Difference between max length and length in DataType Schema

Using OleDb I can retrieve the Database Native DataTypes Schema with LoadSchema("DataTypes")

In the CreateParameters column some variable fields such as varchar have CreateParametera of 'max length' while nchar has a CreateParameters of 'length'.

Can someone differentiate between 'length' and 'max length' for me?

TVM

BOL: "nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying."

so max length for nchar is 4,000.

for varchar(max) max length is 2^31-1 bytes depending on platform.

Thanks.

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

Diagram, sorting and series groups

Say you have two values for each region: current month and previous month. I
have a column in datase called period_offset (0 for current month and -1 for
previous month).
I want to sort the diagram over current month, where previous month is just
for informational purposes (previous month is also colored gray, btw).
For series group, I have =Fields!period_offset.Value, and under sorting:
=Fields!period_offset.Value.
Currently, the diagram seem to sort by the previous month value (where
=Fields!period_offset.Value = -1). How can I sort by current month
(=Fields!period_offset.Value = 0) ?
Thanks
TiborWhere I say "Diagram", I'm referring to the chart control, btw.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uC169yjmEHA.3396@.tk2msftngp13.phx.gbl...
> Say you have two values for each region: current month and previous month.
I
> have a column in datase called period_offset (0 for current month and -1
for
> previous month).
> I want to sort the diagram over current month, where previous month is
just
> for informational purposes (previous month is also colored gray, btw).
> For series group, I have =Fields!period_offset.Value, and under sorting:
> =Fields!period_offset.Value.
> Currently, the diagram seem to sort by the previous month value (where
> =Fields!period_offset.Value = -1). How can I sort by current month
> (=Fields!period_offset.Value = 0) ?
> Thanks
> Tibor
>|||It seems like the order for the rows are determined from the order returned
in the SQL Query. When I add an ORDER BY to the query so that for each
region, that current month is returned before last month, the diagram is
sorted over the current month column.
I can change ASC/DESC in my SQL ORDER BY and the sort in the diagram changes
accordingly...
Is above assumption correct?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23te0gkmEHA.644@.tk2msftngp13.phx.gbl...
> Where I say "Diagram", I'm referring to the chart control, btw.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uC169yjmEHA.3396@.tk2msftngp13.phx.gbl...
> > Say you have two values for each region: current month and previous
month.
> I
> > have a column in datase called period_offset (0 for current month and -1
> for
> > previous month).
> >
> > I want to sort the diagram over current month, where previous month is
> just
> > for informational purposes (previous month is also colored gray, btw).
> >
> > For series group, I have =Fields!period_offset.Value, and under sorting:
> > =Fields!period_offset.Value.
> >
> > Currently, the diagram seem to sort by the previous month value (where
> > =Fields!period_offset.Value = -1). How can I sort by current month
> > (=Fields!period_offset.Value = 0) ?
> >
> > Thanks
> > Tibor
> >
> >
>|||Unless you define an explicit sort expression on the chart series grouping
or the chart or the dataset, the data will be processed and grouped in RS in
the order they come from the data provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uwo5nkkmEHA.1652@.TK2MSFTNGP09.phx.gbl...
> It seems like the order for the rows are determined from the order
returned
> in the SQL Query. When I add an ORDER BY to the query so that for each
> region, that current month is returned before last month, the diagram is
> sorted over the current month column.
> I can change ASC/DESC in my SQL ORDER BY and the sort in the diagram
changes
> accordingly...
> Is above assumption correct?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u%23te0gkmEHA.644@.tk2msftngp13.phx.gbl...
> > Where I say "Diagram", I'm referring to the chart control, btw.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:uC169yjmEHA.3396@.tk2msftngp13.phx.gbl...
> > > Say you have two values for each region: current month and previous
> month.
> > I
> > > have a column in datase called period_offset (0 for current month
and -1
> > for
> > > previous month).
> > >
> > > I want to sort the diagram over current month, where previous month is
> > just
> > > for informational purposes (previous month is also colored gray, btw).
> > >
> > > For series group, I have =Fields!period_offset.Value, and under
sorting:
> > > =Fields!period_offset.Value.
> > >
> > > Currently, the diagram seem to sort by the previous month value (where
> > > =Fields!period_offset.Value = -1). How can I sort by current month
> > > (=Fields!period_offset.Value = 0) ?
> > >
> > > Thanks
> > > Tibor
> > >
> > >
> >
> >
>|||Thanks Robert. This is in line with what I have experienced.
In this particular situation, I just couldn't get RS to sort the way I wanted...
But I did manage when combining sorting on Group/Series and in the SQL query. I'll probably stick
with what I have until I have the time to learn enough about sorting and the relation between
sorting in grouping vs. series.
I found the documentation lacking information rather severely in this area, I should add. And it
just wasn't intuitive enough for me. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23u3BxyzmEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Unless you define an explicit sort expression on the chart series grouping
> or the chart or the dataset, the data will be processed and grouped in RS in
> the order they come from the data provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uwo5nkkmEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> It seems like the order for the rows are determined from the order
> returned
>> in the SQL Query. When I add an ORDER BY to the query so that for each
>> region, that current month is returned before last month, the diagram is
>> sorted over the current month column.
>> I can change ASC/DESC in my SQL ORDER BY and the sort in the diagram
> changes
>> accordingly...
>> Is above assumption correct?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:u%23te0gkmEHA.644@.tk2msftngp13.phx.gbl...
>> > Where I say "Diagram", I'm referring to the chart control, btw.
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> > message news:uC169yjmEHA.3396@.tk2msftngp13.phx.gbl...
>> > > Say you have two values for each region: current month and previous
>> month.
>> > I
>> > > have a column in datase called period_offset (0 for current month
> and -1
>> > for
>> > > previous month).
>> > >
>> > > I want to sort the diagram over current month, where previous month is
>> > just
>> > > for informational purposes (previous month is also colored gray, btw).
>> > >
>> > > For series group, I have =Fields!period_offset.Value, and under
> sorting:
>> > > =Fields!period_offset.Value.
>> > >
>> > > Currently, the diagram seem to sort by the previous month value (where
>> > > =Fields!period_offset.Value = -1). How can I sort by current month
>> > > (=Fields!period_offset.Value = 0) ?
>> > >
>> > > Thanks
>> > > Tibor
>> > >
>> > >
>> >
>> >
>>
>|||Regarding BOL:
Yes, our UE/documentation team is working on improving the entire charting
documentation. Updates should be available on MSDN in the next few months.
Regarding sorting:
Grouping/sorting specified in the commandtext is done on the data source's
database server. Grouping/sorting/filtering defined in the RDL is done in
the RS processing engine internally.
If you specify a sort expression on group expressions in the report, the
processing engine will apply sorting. I.e. if you don't specify any explicit
collation settings on the RDL dataset, we will - in case of a live execution
against a SQL 2000 data source - determine the collation settings from the
data source. Otherwise, we will do sorting based on the culture settings of
the report.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3fKkA1mEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Thanks Robert. This is in line with what I have experienced.
> In this particular situation, I just couldn't get RS to sort the way I
wanted...
> But I did manage when combining sorting on Group/Series and in the SQL
query. I'll probably stick
> with what I have until I have the time to learn enough about sorting and
the relation between
> sorting in grouping vs. series.
> I found the documentation lacking information rather severely in this
area, I should add. And it
> just wasn't intuitive enough for me. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23u3BxyzmEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > Unless you define an explicit sort expression on the chart series
grouping
> > or the chart or the dataset, the data will be processed and grouped in
RS in
> > the order they come from the data provider.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uwo5nkkmEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> It seems like the order for the rows are determined from the order
> > returned
> >> in the SQL Query. When I add an ORDER BY to the query so that for each
> >> region, that current month is returned before last month, the diagram
is
> >> sorted over the current month column.
> >>
> >> I can change ASC/DESC in my SQL ORDER BY and the sort in the diagram
> > changes
> >> accordingly...
> >>
> >> Is above assumption correct?
> >>
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> message news:u%23te0gkmEHA.644@.tk2msftngp13.phx.gbl...
> >> > Where I say "Diagram", I'm referring to the chart control, btw.
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> >> in
> >> > message news:uC169yjmEHA.3396@.tk2msftngp13.phx.gbl...
> >> > > Say you have two values for each region: current month and previous
> >> month.
> >> > I
> >> > > have a column in datase called period_offset (0 for current month
> > and -1
> >> > for
> >> > > previous month).
> >> > >
> >> > > I want to sort the diagram over current month, where previous month
is
> >> > just
> >> > > for informational purposes (previous month is also colored gray,
btw).
> >> > >
> >> > > For series group, I have =Fields!period_offset.Value, and under
> > sorting:
> >> > > =Fields!period_offset.Value.
> >> > >
> >> > > Currently, the diagram seem to sort by the previous month value
(where
> >> > > =Fields!period_offset.Value = -1). How can I sort by current month
> >> > > (=Fields!period_offset.Value = 0) ?
> >> > >
> >> > > Thanks
> >> > > Tibor
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>

diadvantage & advantage of identy column

dear sir/madam
please tell me about diadvantage & advantage of using identy column.

sincerely yoursan identity column allows for all of the records in your table to have a unique identifier. Without this you run the risk of having duplicate records, which means that you won't be able to run a delete statement, and go through a lot of hell to get the duplications out. The only tables that I don't use identifiers in are lookup tables that are used for many to many relationships. All tables should have primary keys, and all tables if not a lookup table should have a unique identifier.

Deterministic - column property

Shows whether the data type of the selected column can be determined with certainty. (Applies only to Microsoft SQL Server 2000 or later.)

This is what Microsoft documentation says for this column property. How I can use his feature for database application development? What is the practical use of this property?

SQL Server 2005.

Thank you,

Smith

it is relevant only to computed columns. Indexes can be built on computed columns only if they are determinists. For mroe details refer to BOL.

Determinism of UDF

I'm having a problem getting SQL to recognize a UDF as deterministic so I can use it in a persisted, computed column. I'm using INFORMATION_SCHEMA.ROUTINES to check the IS_DETERMINISTIC value and it is always NO. Why isn't the following UDF deterministic?

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.part CHAR, @.seconds INT
) RETURNS INT
AS BEGIN
DECLARE @.hours INT, @.minutes INT

SET @.hours = @.seconds / 3600
IF @.part = 'H'
RETURN @.hours

SET @.seconds = @.seconds - (@.hours * 3600)
SET @.minutes = @.seconds / 60

IF @.part = 'M'
RETURN @.minutes

SET @.seconds = @.seconds - (@.minutes * 60)
RETURN @.seconds
END

It does not use any DB objects, floating point values, sprocs, SQL functions or non-POD types. It is strictly taking the input and returning a calculated output. I even went so far as to simply it to this with no effect.

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.seconds INT
) RETURNS INT
AS BEGIN
RETURN 0
END

I've tried mucking around with some of the ANSI options like NUMERIC_ROUNDABORT and ARITHABORT and friends but it had no effect. Anybody know how I can make the function deterministic?

Michael Taylor - 2/7/07
http://p3net.mvps.org

I have identified the issue. The function must be schema bound. I added the WITH SCHEMABINDING to the declaration and it is now deterministic. Just in case anybody else runs into this problem.

Michael Taylor - 2/7/07
http://p3net.mvps.org

|||Additionally, if you are using a CLR UDF then you can explicitly specify these properties using custom attributes on the udf

Determinism of UDF

I'm having a problem getting SQL to recognize a UDF as deterministic so I can use it in a persisted, computed column. I'm using INFORMATION_SCHEMA.ROUTINES to check the IS_DETERMINISTIC value and it is always NO. Why isn't the following UDF deterministic?

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.part CHAR, @.seconds INT
) RETURNS INT
AS BEGIN
DECLARE @.hours INT, @.minutes INT

SET @.hours = @.seconds / 3600
IF @.part = 'H'
RETURN @.hours

SET @.seconds = @.seconds - (@.hours * 3600)
SET @.minutes = @.seconds / 60

IF @.part = 'M'
RETURN @.minutes

SET @.seconds = @.seconds - (@.minutes * 60)
RETURN @.seconds
END

It does not use any DB objects, floating point values, sprocs, SQL functions or non-POD types. It is strictly taking the input and returning a calculated output. I even went so far as to simply it to this with no effect.

ALTER FUNCTION [dbo].[SecondsToTimePart2] (
@.seconds INT
) RETURNS INT
AS BEGIN
RETURN 0
END

I've tried mucking around with some of the ANSI options like NUMERIC_ROUNDABORT and ARITHABORT and friends but it had no effect. Anybody know how I can make the function deterministic?

Michael Taylor - 2/7/07
http://p3net.mvps.org

I have identified the issue. The function must be schema bound. I added the WITH SCHEMABINDING to the declaration and it is now deterministic. Just in case anybody else runs into this problem.

Michael Taylor - 2/7/07
http://p3net.mvps.org

|||Additionally, if you are using a CLR UDF then you can explicitly specify these properties using custom attributes on the udf