Hi,
When I reset the SQL query in Database ->Show Sql Query, all integer and money type values are becoming zeros. When I execute the same query in the SQL Query Analyzer I am getting the values with same no. of records in the report. Can anybody suggest me the solution.
The only difference before resetting, the query doesn't have "WHERE" clause.
Thanks in advance.Post the query you used and the table structure
Showing posts with label integer. Show all posts
Showing posts with label integer. Show all posts
Monday, March 12, 2012
Integer, varchar and IN
In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
(same question is also posted at sql-server-performance.com forums)Originally posted by Frettmaestro
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?there are several ways, all of them, um, how do i put this, less than elegant
(same question is also posted at sql-server-performance.com forums) i'd be interested in hearing about any more clever solutions than mine:select C.categoryname
from user U
inner
join category C
on instr(','+C.catid',', ','+U.categories+',') > 0
where U.userid = 12the concatenation of a comma in front of and behind the fields is so that you will find the first or last category in the list
and of course the search string is delimited by commas, so that you don't accidentally find 12 inside 312|||Thanx alot for the contribution...are you a vb/asp-developer? This syntax is alot like asp/vb but unfortunately there is no such thing as "instr" in sql. I have used InStr alot in my asp-scripting but "IN" and "LIKE" are the only keywords I can think of that does close to the same thing allthough I cannot make them work. It's not a big deal to make the extra table actually...it was really more out of curiosity I was asking...|||vb/asp? me? heh, no way, not even close
my mistake, change INSTR to CHARINDEX|||but yeah, create a normalized table
for one thing, your queries won't do table scans and can start using indexes
:cool:|||This is the final version and it worked like a charm allthough I think I will go with the normalized table as you are recomending.select C.category
from user U
inner
join category C
on CHARINDEX(',' + CAST(C.catid AS varchar(20)) + ',', U.categories) > 0
where U.userid = 12It really doesn't make that big difference because this will probably only be used by 3-4 users maybe a couple of times a week, but still I prefer to create quality stuff instead of shit when I can. But thanx alot buddy for all your help, I might be back to bother you again real soon :D|||you have to put commas around U.categories too, otherwise you won't find the first or last item
;)
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
(same question is also posted at sql-server-performance.com forums)Originally posted by Frettmaestro
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?there are several ways, all of them, um, how do i put this, less than elegant
(same question is also posted at sql-server-performance.com forums) i'd be interested in hearing about any more clever solutions than mine:select C.categoryname
from user U
inner
join category C
on instr(','+C.catid',', ','+U.categories+',') > 0
where U.userid = 12the concatenation of a comma in front of and behind the fields is so that you will find the first or last category in the list
and of course the search string is delimited by commas, so that you don't accidentally find 12 inside 312|||Thanx alot for the contribution...are you a vb/asp-developer? This syntax is alot like asp/vb but unfortunately there is no such thing as "instr" in sql. I have used InStr alot in my asp-scripting but "IN" and "LIKE" are the only keywords I can think of that does close to the same thing allthough I cannot make them work. It's not a big deal to make the extra table actually...it was really more out of curiosity I was asking...|||vb/asp? me? heh, no way, not even close
my mistake, change INSTR to CHARINDEX|||but yeah, create a normalized table
for one thing, your queries won't do table scans and can start using indexes
:cool:|||This is the final version and it worked like a charm allthough I think I will go with the normalized table as you are recomending.select C.category
from user U
inner
join category C
on CHARINDEX(',' + CAST(C.catid AS varchar(20)) + ',', U.categories) > 0
where U.userid = 12It really doesn't make that big difference because this will probably only be used by 3-4 users maybe a couple of times a week, but still I prefer to create quality stuff instead of shit when I can. But thanx alot buddy for all your help, I might be back to bother you again real soon :D|||you have to put commas around U.categories too, otherwise you won't find the first or last item
;)
integer variable usage in sqlStatement expression
I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!
"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:
aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:
tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+(DT_STR, 4, 1252)@.[User::rcSourceFile]+",
"+(DT_STR, 4, 1252)@.[User::rcCleanInserts]+",
"+(DT_STR, 4, 1252)@.[User::rcDataConversionErrors]+",
"+(DT_STR, 4, 1252)@.[User::rcPKViolation]+",
"+(DT_STR, 4, 1252)@.[User::rcExistingInvoiceID]+",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase1] +",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase2] +",
"+(DT_STR, 4, 1252)@.[User::rcConditionalSplitErrors]+"
)"
all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what?
I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!
jpsR1 wrote:
I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!
"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+(DT_STR, 4, 1252)@.[User::rcSourceFile]+",
"+(DT_STR, 4, 1252)@.[User::rcCleanInserts]+",
"+(DT_STR, 4, 1252)@.[User::rcDataConversionErrors]+",
"+(DT_STR, 4, 1252)@.[User::rcPKViolation]+",
"+(DT_STR, 4, 1252)@.[User::rcExistingInvoiceID]+",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase1] +",
"+(DT_STR, 4, 1252)@.[User::rcISNULLCase2] +",
"+(DT_STR, 4, 1252)@.[User::rcConditionalSplitErrors]+"
)"all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what?
I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!
So, what is your problem?
The expression looks right and makes sense. The expression is treated as an string; so yes, you have to cast all non-string variables to string in order to be used in the expression.
|||Right, well, you can't concatenate (a string operator) a string with an integer data type. So what you've done is correct.|||You can save some keystrokes by casting to (DT_WSTR,length) instead, though.|||It's a little confusing i have an Integer stored in a Package Variable set as an Int32 that I have to type cast as a string in order to show as an integer? Is it only me that sees this as whacky?|||This would be more intuitive:
"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @.[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@.[System:tartTime]+ "',
1,
"+@.[User::rcSourceFile]+",
"+@.[User::rcCleanInserts]+",
"+@.[User::rcDataConversionErrors]+",
"+@.[User::rcPKViolation]+",
"+@.[User::rcExistingInvoiceID]+",
"+@.[User::rcISNULLCase1] +",
"+@.[User::rcISNULLCase2] +",
"+@.[User::rcConditionalSplitErrors]+"
)"
It's a little confusing i have an Integer stored in a Package Variable set as an Int32 that I have to type cast as a string in order to show as an integer? Is it only me that sees this as whacky?
But you can't concatenate an integer to a string. It has nothing to do with SSIS. It's an integer. You're trying to concatenate. Integers can't be concatenated. End result: you have to cast your integers to strings.|||
Why do you think I'm trying to concatenate an integer?
I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.
|||
Why do you think I'm trying to concatenate an integer?
I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.
What do you think the "+" symbol means when you are assembling your SQL statement?|||
Why do you think I'm trying to concatenate an integer?
I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.
You are right; the column in the table and the variable are integer; BUT the expression property of an SSIS object expects an string value; hence everything on it has to be an string.
|||
Why do you think I'm trying to concatenate an integer?
I have stored a row count (an integer) in a package variable(an integer) and am trying to INSERT this integer value into an integer column in a table.
Maybe this will help explain it:
You are assembling a string (the INSERT statement). If you go into SSMS, open a query editor, and type
Code Snippet
SELECT 'INSERT INTO TestTable VALUES(' + 1 +')'
you will get an error back because SQL Server can't add an integer to a string. The "+" has two functions. For numbers it is used for addition. For strings, it is used for concatenation. If you include a number and a string in the same expression, SSIS (and SQL Server, and most other programming tools) throws an error because you are doing something ambiguous. Do you want to concatenate, or do you want to add? The tools could guess (like VB 1 - 6) and implicitly convert the numbers to string values, but this is generally accepted as a bad practice, as it leads to bugs and unclear code. Instead, most newer development tools don't do implicit conversions (or at least have it disabled by default).
Integer to HH:MM:SS
Hi,
In SRS 2K, how can I take a field that is listed as an integer (say 60),
and make it look like 01:00:00 in the report? Is there a way to do this in
the Textbox Properties window using the Custom Format section?
Thanks.Found my answer in another thread:
=String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddSeconds(Fields!*Value))
"lucotc" wrote:
> Hi,
> In SRS 2K, how can I take a field that is listed as an integer (say 60),
> and make it look like 01:00:00 in the report? Is there a way to do this in
> the Textbox Properties window using the Custom Format section?
>
> Thanks.
In SRS 2K, how can I take a field that is listed as an integer (say 60),
and make it look like 01:00:00 in the report? Is there a way to do this in
the Textbox Properties window using the Custom Format section?
Thanks.Found my answer in another thread:
=String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddSeconds(Fields!*Value))
"lucotc" wrote:
> Hi,
> In SRS 2K, how can I take a field that is listed as an integer (say 60),
> and make it look like 01:00:00 in the report? Is there a way to do this in
> the Textbox Properties window using the Custom Format section?
>
> Thanks.
Integer Parameter
I have a parameter field with a data type of int. I want the users to be able to select one or 'all' from the dropdown list. Is there a wildcard for a integer datatype?
Dropdown list example:
All Project Ids
1001
1002
1003
1004
In cases like this I will frequently use a NULL parameter to designate 'all'; can that work for you in this case? An example of this would be something like this:
declare @.example table (integerCol integer)
insert into @.example values (1001)
insert into @.example values (1002)
insert into @.example values (1003)
insert into @.example values (1004)declare @.anIntegerParm integer
set @.anIntegerParm = null
set @.anIntegerParm = 1002if @.anIntegerParm is null
select * from @.example
else
select * from @.example
where integerCol = @.anIntegerParm-- When @.anIntegerParm is null:
-- integerCol
-- --
-- 1001
-- 1002
-- 1003
-- 1004-- When @.anIntegerParm = 1002:
-- integerCol
-- --
-- 1002select * from @.example
where @.anIntegerParm is null
or @.anIntegerParm is not null
and integerCol = @.anIntegerParm-- integerCol
-- --
-- 1002
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifier
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as part
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. This
> will have some impact on join performance, but it probably will not be that
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> > can you please give me an idea about the advantages/disadvantages between
> > using numeric identity versus uniquidentifier in sql 2005 and which is a
> > better choice generally?
> > Is there an article on MSDN in a table format that lists the differences
> > plus recommandations on when to use each one of them?
> > thanks
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifier
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as part
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. This
> will have some impact on join performance, but it probably will not be that
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> > can you please give me an idea about the advantages/disadvantages between
> > using numeric identity versus uniquidentifier in sql 2005 and which is a
> > better choice generally?
> > Is there an article on MSDN in a table format that lists the differences
> > plus recommandations on when to use each one of them?
> > thanks
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:[vbcol=seagreen]
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifie
r
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness
,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as pa
rt
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. Thi
s
> will have some impact on join performance, but it probably will not be tha
t
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:[vbcol=seagreen]
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifie
r
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness
,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as pa
rt
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. Thi
s
> will have some impact on join performance, but it probably will not be tha
t
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
Friday, March 9, 2012
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanks
SalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanks
SalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks
integer indices in 'over' clause of row_number() function in SQL20
Hello,
I tried to use the new row_number() function while adjusting my application
to SQL 2005. For example, this query:
--
use AdventureWorks
select CurrencyCode, row_number() over(order by 1) from Sales.Currency
order by 1
--
worked perfectly in the CTP release (9.00.1187.00). Strangely,
when I installed the application on the final release of SQL 2005
(9.00.1399.00), I got the following error message:
Msg 5308, Level 16, State 1, Line 2
Windowed functions do not support integer indices as ORDER BY clause
expressions.
If I use the column name instead of the number (i.e., 'order by
CurrencyCode'), the query works.
The problem is that in my real queries the fields in select clause are much
more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
right to repeat the whole expression in the over clause. I also can't use
aliases for the number of reasons.
So the question is:
Does anybody know why this feature was removed from the final release of
SQL2005? Is there any chance that it'll be fixed?
Is there any idea how can I use the integer numbers to designate the fields
in over(order by) clause?
Thanks in advance,
GrishaGrisha wrote:
> Hello,
> I tried to use the new row_number() function while adjusting my applicatio
n
> to SQL 2005. For example, this query:
> --
> use AdventureWorks
> select CurrencyCode, row_number() over(order by 1) from Sales.Currency
> order by 1
> --
> worked perfectly in the CTP release (9.00.1187.00). Strangely,
> when I installed the application on the final release of SQL 2005
> (9.00.1399.00), I got the following error message:
> Msg 5308, Level 16, State 1, Line 2
> Windowed functions do not support integer indices as ORDER BY clause
> expressions.
> If I use the column name instead of the number (i.e., 'order by
> CurrencyCode'), the query works.
> The problem is that in my real queries the fields in select clause are muc
h
> more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
> right to repeat the whole expression in the over clause. I also can't use
> aliases for the number of reasons.
> So the question is:
> Does anybody know why this feature was removed from the final release of
> SQL2005? Is there any chance that it'll be fixed?
> Is there any idea how can I use the integer numbers to designate the field
s
> in over(order by) clause?
> Thanks in advance,
> Grisha
I'm not certain, but probably MS followed the SQL standard, which I
think no longer supports column index numbers. Someone may correct me
on that point though as I don't have the docs in front of me.
In any case, I think this is a good thing (not allowing column numbers
I mean). Using column numbers should be avoided for the same reasons we
avoid "SELECT *" - to reduce maintenance costs and potential for errors
when the table structure is modified.
What I don't understand is why you can't use an alias. How about
putting your query into a CTE?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I tried to use the new row_number() function while adjusting my application
to SQL 2005. For example, this query:
--
use AdventureWorks
select CurrencyCode, row_number() over(order by 1) from Sales.Currency
order by 1
--
worked perfectly in the CTP release (9.00.1187.00). Strangely,
when I installed the application on the final release of SQL 2005
(9.00.1399.00), I got the following error message:
Msg 5308, Level 16, State 1, Line 2
Windowed functions do not support integer indices as ORDER BY clause
expressions.
If I use the column name instead of the number (i.e., 'order by
CurrencyCode'), the query works.
The problem is that in my real queries the fields in select clause are much
more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
right to repeat the whole expression in the over clause. I also can't use
aliases for the number of reasons.
So the question is:
Does anybody know why this feature was removed from the final release of
SQL2005? Is there any chance that it'll be fixed?
Is there any idea how can I use the integer numbers to designate the fields
in over(order by) clause?
Thanks in advance,
GrishaGrisha wrote:
> Hello,
> I tried to use the new row_number() function while adjusting my applicatio
n
> to SQL 2005. For example, this query:
> --
> use AdventureWorks
> select CurrencyCode, row_number() over(order by 1) from Sales.Currency
> order by 1
> --
> worked perfectly in the CTP release (9.00.1187.00). Strangely,
> when I installed the application on the final release of SQL 2005
> (9.00.1399.00), I got the following error message:
> Msg 5308, Level 16, State 1, Line 2
> Windowed functions do not support integer indices as ORDER BY clause
> expressions.
> If I use the column name instead of the number (i.e., 'order by
> CurrencyCode'), the query works.
> The problem is that in my real queries the fields in select clause are muc
h
> more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
> right to repeat the whole expression in the over clause. I also can't use
> aliases for the number of reasons.
> So the question is:
> Does anybody know why this feature was removed from the final release of
> SQL2005? Is there any chance that it'll be fixed?
> Is there any idea how can I use the integer numbers to designate the field
s
> in over(order by) clause?
> Thanks in advance,
> Grisha
I'm not certain, but probably MS followed the SQL standard, which I
think no longer supports column index numbers. Someone may correct me
on that point though as I don't have the docs in front of me.
In any case, I think this is a good thing (not allowing column numbers
I mean). Using column numbers should be avoided for the same reasons we
avoid "SELECT *" - to reduce maintenance costs and potential for errors
when the table structure is modified.
What I don't understand is why you can't use an alias. How about
putting your query into a CTE?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
integer indices in 'over' clause of row_number() function in S
Grisha wrote:
> Hello David,
> Thank you for the quick answer.
> Well, I don't know about changes in SQL standard, but I still think that t
he
> fact that column numbers are supported in 'regular' order by clause of a
> query but not inside over clause is ...hmmm... strange.
> Also I disagree with you about your comparison of column numbering to
> 'SELECT *'. Indeed, 'SELECT *' could lead to errors when table structure i
s
> changed, but column numbers are something that belongs to specific query,
so
> I think that it's perfectly well to use them.
> I considered to use CTE but since my queries are actually generated
> dynamically from the application it requires a lot of re-coding and
> potentially a lot of new bugs...
> Anyway, actually I wanted to number the records for identifying them later
> on. So I discovered by now that I can 'neutralize' the over clause by
> changing the query
> to:
> select CurrencyCode, row_number() over(order by user) from Sales.Curren
cy
> order by 1
>
That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
the ORDER BY. Probably this is especially dangerous if your query is
dynamic. Here's a repro:
CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
INSERT INTO T1 (x,y)
SELECT 1,9 UNION ALL
SELECT 2,8 UNION ALL
SELECT 3,7 UNION ALL
SELECT 4,6 ;
SELECT x,y,
ROW_NUMBER() OVER(ORDER BY user) AS row_num
FROM T1
ORDER BY 1 ;
I get:
x y row_num
-- -- --
1 9 4
2 8 3
3 7 2
4 6 1
(4 row(s) affected)
YMMV
> Anyway, IMHO this is just bug...
> Thanks again,
> Grisha
>
It is certainly an error in the documentation because the help on the
OVER (ORDER BY ...) clause specifies that column aliases may be used:
http://msdn2.microsoft.com/ms188385.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--Hello David,
thank you for the example. I know that it will not match query's ORDER BY.
But it will still assign unique numbers to the records (although not in any
particular order) and this is enough for me.
Of course, it doesn't reflect the idea for which row_number() function was
designed, but I don't care. Probably MS programmers should care for it... :-
)
"David Portas" wrote:
> Grisha wrote:
> That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
> the ORDER BY. Probably this is especially dangerous if your query is
> dynamic. Here's a repro:
> CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO T1 (x,y)
> SELECT 1,9 UNION ALL
> SELECT 2,8 UNION ALL
> SELECT 3,7 UNION ALL
> SELECT 4,6 ;
> SELECT x,y,
> ROW_NUMBER() OVER(ORDER BY user) AS row_num
> FROM T1
> ORDER BY 1 ;
> I get:
> x y row_num
> -- -- --
> 1 9 4
> 2 8 3
> 3 7 2
> 4 6 1
> (4 row(s) affected)
> YMMV
>
> It is certainly an error in the documentation because the help on the
> OVER (ORDER BY ...) clause specifies that column aliases may be used:
> http://msdn2.microsoft.com/ms188385.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
> Hello David,
> Thank you for the quick answer.
> Well, I don't know about changes in SQL standard, but I still think that t
he
> fact that column numbers are supported in 'regular' order by clause of a
> query but not inside over clause is ...hmmm... strange.
> Also I disagree with you about your comparison of column numbering to
> 'SELECT *'. Indeed, 'SELECT *' could lead to errors when table structure i
s
> changed, but column numbers are something that belongs to specific query,
so
> I think that it's perfectly well to use them.
> I considered to use CTE but since my queries are actually generated
> dynamically from the application it requires a lot of re-coding and
> potentially a lot of new bugs...
> Anyway, actually I wanted to number the records for identifying them later
> on. So I discovered by now that I can 'neutralize' the over clause by
> changing the query
> to:
> select CurrencyCode, row_number() over(order by user) from Sales.Curren
cy
> order by 1
>
That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
the ORDER BY. Probably this is especially dangerous if your query is
dynamic. Here's a repro:
CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
INSERT INTO T1 (x,y)
SELECT 1,9 UNION ALL
SELECT 2,8 UNION ALL
SELECT 3,7 UNION ALL
SELECT 4,6 ;
SELECT x,y,
ROW_NUMBER() OVER(ORDER BY user) AS row_num
FROM T1
ORDER BY 1 ;
I get:
x y row_num
-- -- --
1 9 4
2 8 3
3 7 2
4 6 1
(4 row(s) affected)
YMMV
> Anyway, IMHO this is just bug...
> Thanks again,
> Grisha
>
It is certainly an error in the documentation because the help on the
OVER (ORDER BY ...) clause specifies that column aliases may be used:
http://msdn2.microsoft.com/ms188385.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--Hello David,
thank you for the example. I know that it will not match query's ORDER BY.
But it will still assign unique numbers to the records (although not in any
particular order) and this is enough for me.
Of course, it doesn't reflect the idea for which row_number() function was
designed, but I don't care. Probably MS programmers should care for it... :-
)
"David Portas" wrote:
> Grisha wrote:
> That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
> the ORDER BY. Probably this is especially dangerous if your query is
> dynamic. Here's a repro:
> CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO T1 (x,y)
> SELECT 1,9 UNION ALL
> SELECT 2,8 UNION ALL
> SELECT 3,7 UNION ALL
> SELECT 4,6 ;
> SELECT x,y,
> ROW_NUMBER() OVER(ORDER BY user) AS row_num
> FROM T1
> ORDER BY 1 ;
> I get:
> x y row_num
> -- -- --
> 1 9 4
> 2 8 3
> 3 7 2
> 4 6 1
> (4 row(s) affected)
> YMMV
>
> It is certainly an error in the documentation because the help on the
> OVER (ORDER BY ...) clause specifies that column aliases may be used:
> http://msdn2.microsoft.com/ms188385.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
integer indices in 'over' clause of row_number() function in S
>> Well, I don't know about changes in SQL standard, but I still think that
the fact that column numbers are supported in 'regular' order by clause of a
query but not inside over clause is ...hmmm... strange. <<
Sloppy is probably a better word :) They are not supposed to be
supported in any ORDER BY clause. The goal of X3 was to get rid of all
position number column references after SQL-92, so that you do not
confuse an attribute with an expression, or depend on the current state
of a table at runtime .
The only places that you get implicit column names is in the expansion
of SELECT * and missing column lists in INSERT INTO. There was even
talk of gettign rid of those things.On 18 Jan 2006 06:20:11 -0800, --CELKO-- wrote:
>Sloppy is probably a better word :) They are not supposed to be
>supported in any ORDER BY clause. The goal of X3 was to get rid of all
>position number column references after SQL-92, so that you do not
>confuse an attribute with an expression, or depend on the current state
>of a table at runtime .
Hi Joe,
But unlike a standards committe, MS SQL Server has an established user
base with billions of line of deployed code. They can't just remove a
syntax element that was valid in the previous release.
BTW, given that SELECT * should never be used in a query, how does ORDER
BY ordinal_position rely on the current state of a table at runtime?
>The only places that you get implicit column names is in the expansion
>of SELECT * and missing column lists in INSERT INTO. There was even
>talk of gettign rid of those things.
Too bad that it was only talk.
Hugo Kornelis, SQL Server MVP
the fact that column numbers are supported in 'regular' order by clause of a
query but not inside over clause is ...hmmm... strange. <<
Sloppy is probably a better word :) They are not supposed to be
supported in any ORDER BY clause. The goal of X3 was to get rid of all
position number column references after SQL-92, so that you do not
confuse an attribute with an expression, or depend on the current state
of a table at runtime .
The only places that you get implicit column names is in the expansion
of SELECT * and missing column lists in INSERT INTO. There was even
talk of gettign rid of those things.On 18 Jan 2006 06:20:11 -0800, --CELKO-- wrote:
>Sloppy is probably a better word :) They are not supposed to be
>supported in any ORDER BY clause. The goal of X3 was to get rid of all
>position number column references after SQL-92, so that you do not
>confuse an attribute with an expression, or depend on the current state
>of a table at runtime .
Hi Joe,
But unlike a standards committe, MS SQL Server has an established user
base with billions of line of deployed code. They can't just remove a
syntax element that was valid in the previous release.
BTW, given that SELECT * should never be used in a query, how does ORDER
BY ordinal_position rely on the current state of a table at runtime?
>The only places that you get implicit column names is in the expansion
>of SELECT * and missing column lists in INSERT INTO. There was even
>talk of gettign rid of those things.
Too bad that it was only talk.
Hugo Kornelis, SQL Server MVP
Integer Index -vs- nVarChar(50) index....
We have a legacy database that was ported over from LDAP where the user
tables are indexed on the username (which is an nVarChar(50) field). We have
sever (ok, 92) related tables all using the nVarChar(50) field as the
primary key.
Does anyone have hard numbers as to whether SQL2000 would be much more
efficient if we were to convert all the tables to use an integer as the
primary key (and relate the tables on that key) instead of the nVarChar(50)?
TIA,
OwenYou can calculate the avg length of that column and comprare to 8 bytes (for
bigint) or 4 bytes (for int). The amount of I/O operations over an index wit
h
a key of, let us say, 20 bytes (10 characters for nvarchar) is for sure
higher compare with 4 or 8 bytes. Not to mention if the username is using a
clustered index, in this case that amount (for example 35 bytes) is
replicated to the nonclustered indexes also.
AMB
"Owen Mortensen" wrote:
> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We ha
ve
> sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the nVarChar(50
)?
> TIA,
> Owen
>
>|||int will be much more efficient.
hard to give you "Hard Data" but I would think the performance improvements
would be fairly significant especially if the system has a lot of data.
If your Database is only a few Gig and if you only have minor load in any
given day, then it may not be worth the efforts in changing.
cheers
Greg Jackson
PDX, Oregon|||Define hard numbers. Ints are better because they are smaller. They are
also better because they are fixed length. So for each row in your table,
your index pages would hold ~ 2000 rows (8000bytes per page/ 4 bytes per
row) If you have an average of 10 characters per entry, plus the 2 byte
overhead for variable length, then you will only get 8000/12 = 666 rows per
page. Ignoring the dubious nature of the number of rows, it will take 3
times as many pages in the index to handle the need for this index.
Carrying this over to the leaf nodes, if this is the clustered index, you
have to add the 12 bytes for every row rather than 4.
Now, this having been said, pure performance of the index is not the only
issue here. I like integers keys as much as the next guy, and possibly
more, but is this THAT great of a savings. Truly it depends on what your
needs are, and how often you will be doing a full index scan. The cost
might be very small if you are just doing single row retrievals, since you
may not have more than a single level added to the index, as the b-tree
index structure is pretty good.
So the cost to change all of these tables versus ten-twenty milliseconds
might not be worth it. The question is (finally) what is the impetus to
change this. Is something slow? Have you come to the conclusion that for
PK based retrieval it is costing too much? Or other types of retrieval?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:up0n6hgYFHA.2768@.tk2msftngp13.phx.gbl...
> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We
> have sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the
> nVarChar(50)?
> TIA,
> Owen
>
tables are indexed on the username (which is an nVarChar(50) field). We have
sever (ok, 92) related tables all using the nVarChar(50) field as the
primary key.
Does anyone have hard numbers as to whether SQL2000 would be much more
efficient if we were to convert all the tables to use an integer as the
primary key (and relate the tables on that key) instead of the nVarChar(50)?
TIA,
OwenYou can calculate the avg length of that column and comprare to 8 bytes (for
bigint) or 4 bytes (for int). The amount of I/O operations over an index wit
h
a key of, let us say, 20 bytes (10 characters for nvarchar) is for sure
higher compare with 4 or 8 bytes. Not to mention if the username is using a
clustered index, in this case that amount (for example 35 bytes) is
replicated to the nonclustered indexes also.
AMB
"Owen Mortensen" wrote:
> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We ha
ve
> sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the nVarChar(50
)?
> TIA,
> Owen
>
>|||int will be much more efficient.
hard to give you "Hard Data" but I would think the performance improvements
would be fairly significant especially if the system has a lot of data.
If your Database is only a few Gig and if you only have minor load in any
given day, then it may not be worth the efforts in changing.
cheers
Greg Jackson
PDX, Oregon|||Define hard numbers. Ints are better because they are smaller. They are
also better because they are fixed length. So for each row in your table,
your index pages would hold ~ 2000 rows (8000bytes per page/ 4 bytes per
row) If you have an average of 10 characters per entry, plus the 2 byte
overhead for variable length, then you will only get 8000/12 = 666 rows per
page. Ignoring the dubious nature of the number of rows, it will take 3
times as many pages in the index to handle the need for this index.
Carrying this over to the leaf nodes, if this is the clustered index, you
have to add the 12 bytes for every row rather than 4.
Now, this having been said, pure performance of the index is not the only
issue here. I like integers keys as much as the next guy, and possibly
more, but is this THAT great of a savings. Truly it depends on what your
needs are, and how often you will be doing a full index scan. The cost
might be very small if you are just doing single row retrievals, since you
may not have more than a single level added to the index, as the b-tree
index structure is pretty good.
So the cost to change all of these tables versus ten-twenty milliseconds
might not be worth it. The question is (finally) what is the impetus to
change this. Is something slow? Have you come to the conclusion that for
PK based retrieval it is costing too much? Or other types of retrieval?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:up0n6hgYFHA.2768@.tk2msftngp13.phx.gbl...
> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We
> have sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the
> nVarChar(50)?
> TIA,
> Owen
>
Integer identity value handling in export / import
Hi,
I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?
CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||
I am Hitesh.
I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.
Thanks.
Integer identity value handling in export / import
Hi,
I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?
CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||
I am Hitesh.
I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.
Thanks.
Integer field question
Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
Neil
I don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
Neil
I don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
Integer field question
Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input t
o
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input t
o
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
Integer field question
Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.
Integer datatype question
I have a column setup using the Int datatype and a length of 4. First: does this mean that the allowable range is from -9999 to 9999? Second: I can't seem to change the 4 to anything else, how can I modify the length :confused:That's the internaly stored length...
Look up datatypes in books online...
int is actually
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).|||no, the 4 indicates the amount of bytes used. It's a fixed size, you can't change it. As specified by BOL, integer values '(...)from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).'. If this won't do use a bigint instead.
EDIT: Brett, I'll quote you on this: Damn...sniped again
Look up datatypes in books online...
int is actually
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).|||no, the 4 indicates the amount of bytes used. It's a fixed size, you can't change it. As specified by BOL, integer values '(...)from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).'. If this won't do use a bigint instead.
EDIT: Brett, I'll quote you on this: Damn...sniped again
integer datatype confusion, signed vs unsigned
Hi Group
Transact SQL defines that int is:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). Storage size is 4 bytes.
This implies that only SIGNED integer values are possible with
SQL-Server. I'm aware that from a data conversion point of view this
is no problem in that a singed integer can be interpreted as unsigned
or signed.
Then, there is a (c) datatype definition SQLUINTEGER as well as
SQLINTEGER, so unsigned integers seem to be suported conversion wise.
However, when it comes to sorting or using select, things are IMHO
different in that -1 is smaller than '0' if interpreted as signed, but
obviousely the biggest possible value interpreted as unsigned etc. I
therefore somehow miss the possibility to declare an integer collumn
to be "unsigned" so as sorting etc. is made the right way.
The same problem obviousely exists with small integers except that
their range is limitted to what can be expressed with 16 bits.
Could someone sheed some light on this?
TIA
MarkusThe INT datatype is signed, as are BIGINT and SMALLINT. TINYINT is
the only one not signed. This is can not be changed.
If negative numbers are not valid for a column, enforce that with a
CHECK constraint:
CHECK (IntCol >= 0)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 11:26:57 +0200, Markus Zingg <m.zingg@.nct.ch>
wrote:
>Hi Group
>Transact SQL defines that int is:
>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
>1 (2,147,483,647). Storage size is 4 bytes.
>This implies that only SIGNED integer values are possible with
>SQL-Server. I'm aware that from a data conversion point of view this
>is no problem in that a singed integer can be interpreted as unsigned
>or signed.
>Then, there is a (c) datatype definition SQLUINTEGER as well as
>SQLINTEGER, so unsigned integers seem to be suported conversion wise.
>However, when it comes to sorting or using select, things are IMHO
>different in that -1 is smaller than '0' if interpreted as signed, but
>obviousely the biggest possible value interpreted as unsigned etc. I
>therefore somehow miss the possibility to declare an integer collumn
>to be "unsigned" so as sorting etc. is made the right way.
>The same problem obviousely exists with small integers except that
>their range is limitted to what can be expressed with 16 bits.
>Could someone sheed some light on this?
>TIA
>Markus|||Markus Zingg (m.zingg@.nct.ch) writes:
> Transact SQL defines that int is:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
> 1 (2,147,483,647). Storage size is 4 bytes.
> This implies that only SIGNED integer values are possible with
> SQL-Server. I'm aware that from a data conversion point of view this
> is no problem in that a singed integer can be interpreted as unsigned
> or signed.
> Then, there is a (c) datatype definition SQLUINTEGER as well as
> SQLINTEGER, so unsigned integers seem to be suported conversion wise.
I'm not really sure where you find this SQLUINTEGER type, but if the
type is in C, I presume that the type collection has been defined for
more engines than SQL Server in mind, and some of those engines may
support an unsigned integer type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Transact SQL defines that int is:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). Storage size is 4 bytes.
This implies that only SIGNED integer values are possible with
SQL-Server. I'm aware that from a data conversion point of view this
is no problem in that a singed integer can be interpreted as unsigned
or signed.
Then, there is a (c) datatype definition SQLUINTEGER as well as
SQLINTEGER, so unsigned integers seem to be suported conversion wise.
However, when it comes to sorting or using select, things are IMHO
different in that -1 is smaller than '0' if interpreted as signed, but
obviousely the biggest possible value interpreted as unsigned etc. I
therefore somehow miss the possibility to declare an integer collumn
to be "unsigned" so as sorting etc. is made the right way.
The same problem obviousely exists with small integers except that
their range is limitted to what can be expressed with 16 bits.
Could someone sheed some light on this?
TIA
MarkusThe INT datatype is signed, as are BIGINT and SMALLINT. TINYINT is
the only one not signed. This is can not be changed.
If negative numbers are not valid for a column, enforce that with a
CHECK constraint:
CHECK (IntCol >= 0)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 11:26:57 +0200, Markus Zingg <m.zingg@.nct.ch>
wrote:
>Hi Group
>Transact SQL defines that int is:
>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
>1 (2,147,483,647). Storage size is 4 bytes.
>This implies that only SIGNED integer values are possible with
>SQL-Server. I'm aware that from a data conversion point of view this
>is no problem in that a singed integer can be interpreted as unsigned
>or signed.
>Then, there is a (c) datatype definition SQLUINTEGER as well as
>SQLINTEGER, so unsigned integers seem to be suported conversion wise.
>However, when it comes to sorting or using select, things are IMHO
>different in that -1 is smaller than '0' if interpreted as signed, but
>obviousely the biggest possible value interpreted as unsigned etc. I
>therefore somehow miss the possibility to declare an integer collumn
>to be "unsigned" so as sorting etc. is made the right way.
>The same problem obviousely exists with small integers except that
>their range is limitted to what can be expressed with 16 bits.
>Could someone sheed some light on this?
>TIA
>Markus|||Markus Zingg (m.zingg@.nct.ch) writes:
> Transact SQL defines that int is:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
> 1 (2,147,483,647). Storage size is 4 bytes.
> This implies that only SIGNED integer values are possible with
> SQL-Server. I'm aware that from a data conversion point of view this
> is no problem in that a singed integer can be interpreted as unsigned
> or signed.
> Then, there is a (c) datatype definition SQLUINTEGER as well as
> SQLINTEGER, so unsigned integers seem to be suported conversion wise.
I'm not really sure where you find this SQLUINTEGER type, but if the
type is in C, I presume that the type collection has been defined for
more engines than SQL Server in mind, and some of those engines may
support an unsigned integer type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
integer datatype and null value error
I'm getting a datatype error: "Application uses a value of the wrong type for the current operation" when executing the following stored procedure:
CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
@.sessionid varchar(50),
@.FirstName varchar(50) = NULL,
@.LastName varchar(50) = NULL,
@.SchoolName varchar(50) = NULL,
@.address varchar(50) = NULL,
@.City varchar(50) = NULL,
@.State int = NULL,
@.Zip varchar(5) = NULL,
@.Phone varchar(10) = NULL,
@.Email varchar(50) = NULL,
@.CurrentCustomer varchar(3) = NULL,
@.ImplementationType int = NULL,
@.ProductType int = NULL,
@.Comment varchar(500) = NULL
AS
--check if a current record exists
SET NOCOUNT ON
begin
UPDATE dbo.Temp_ContactInfo
SET
FirstName = @.FirstName,
LastName = @.LastName,
SchoolName = @.SchoolName,
Address = @.address,
City = @.City,
State = @.State,
Zip = @.Zip,
Phone = @.Phone,
Email = @.Email,
CurrentCustomer = @.CurrentCustomer,
ImplementationType = @.ImplementationType,
ProductType = @.ProductType,
Comment = @.Comment
WHERE
sessionid = @.sessionid
If @.@.Rowcount = 0
INSERT INTO dbo.Temp_ContactInfo
(sessionid,
FirstName,
LastName,
SchoolName,
address,
City,
State,
Zip,
Phone,
Email,
CurrentCustomer,
ImplementationType,
ProductType,
Comment)
VALUES
(@.sessionid,
@.FirstName,
@.LastName,
@.SchoolName,
@.address,
@.City,
@.State,
@.Zip,
@.Phone,
@.Email,
@.CurrentCustomer,
@.ImplementationType,
@.ProductType,
@.Comment)
end
GO
This is code I'm using to call the procedure:
set InsertTempInfo = Server.CreateObject("ADODB.Command")
With InsertTempInfo
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.Insert_Temp_ContactInfo"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@.sessionid", 200, 1,50, usrid)
.Parameters.Append .CreateParameter("@.FirstName", 200, 1,50,fname)
.Parameters.Append .CreateParameter("@.LastName", 200, 1,50,lname)
.Parameters.Append .CreateParameter("@.SchoolName", 200, 1,50,schoolname)
.Parameters.Append .CreateParameter("@.address", 200, 1,50,address)
.Parameters.Append .CreateParameter("@.City", 200, 1,50,city)
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
.Parameters.Append .CreateParameter("@.Zip", 200, 1,5,zip)
.Parameters.Append .CreateParameter("@.Phone", 200, 1,10,phone)
.Parameters.Append .CreateParameter("@.Email", 200, 1,50,email)
.Parameters.Append .CreateParameter("@.CurrentCustomer", 200, 1,3,currentcustomer)
.Parameters.Append .CreateParameter("@.ImplementationType", 3, 1,4,implementationtype)
.Parameters.Append .CreateParameter("@.ProductType", 3, 1,4,producttype)
.Parameters.Append .CreateParameter("@.Comment", 200, 1,500,comment)
.Execute()
End With
Set InsertTempInfo = Nothing
the error is thrown on the following line:
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.
So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @.state parameter is failing and throwing the error.
I've double checked that the table has the state column set to integer datatype
The column is set as follows:
Name datatype length Allow Nulls
--------------
State int 4 checked
I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?
Not sure what else to look at?
It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?
Thanks for any help.Basic thing to check: Make sure the data type you are passing to the stored procedure is of the same type as the stored procedure is expecting
CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
@.sessionid varchar(50),
@.FirstName varchar(50) = NULL,
@.LastName varchar(50) = NULL,
@.SchoolName varchar(50) = NULL,
@.address varchar(50) = NULL,
@.City varchar(50) = NULL,
@.State int = NULL,
@.Zip varchar(5) = NULL,
@.Phone varchar(10) = NULL,
@.Email varchar(50) = NULL,
@.CurrentCustomer varchar(3) = NULL,
@.ImplementationType int = NULL,
@.ProductType int = NULL,
@.Comment varchar(500) = NULL
AS
--check if a current record exists
SET NOCOUNT ON
begin
UPDATE dbo.Temp_ContactInfo
SET
FirstName = @.FirstName,
LastName = @.LastName,
SchoolName = @.SchoolName,
Address = @.address,
City = @.City,
State = @.State,
Zip = @.Zip,
Phone = @.Phone,
Email = @.Email,
CurrentCustomer = @.CurrentCustomer,
ImplementationType = @.ImplementationType,
ProductType = @.ProductType,
Comment = @.Comment
WHERE
sessionid = @.sessionid
If @.@.Rowcount = 0
INSERT INTO dbo.Temp_ContactInfo
(sessionid,
FirstName,
LastName,
SchoolName,
address,
City,
State,
Zip,
Phone,
Email,
CurrentCustomer,
ImplementationType,
ProductType,
Comment)
VALUES
(@.sessionid,
@.FirstName,
@.LastName,
@.SchoolName,
@.address,
@.City,
@.State,
@.Zip,
@.Phone,
@.Email,
@.CurrentCustomer,
@.ImplementationType,
@.ProductType,
@.Comment)
end
GO
This is code I'm using to call the procedure:
set InsertTempInfo = Server.CreateObject("ADODB.Command")
With InsertTempInfo
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.Insert_Temp_ContactInfo"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@.sessionid", 200, 1,50, usrid)
.Parameters.Append .CreateParameter("@.FirstName", 200, 1,50,fname)
.Parameters.Append .CreateParameter("@.LastName", 200, 1,50,lname)
.Parameters.Append .CreateParameter("@.SchoolName", 200, 1,50,schoolname)
.Parameters.Append .CreateParameter("@.address", 200, 1,50,address)
.Parameters.Append .CreateParameter("@.City", 200, 1,50,city)
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
.Parameters.Append .CreateParameter("@.Zip", 200, 1,5,zip)
.Parameters.Append .CreateParameter("@.Phone", 200, 1,10,phone)
.Parameters.Append .CreateParameter("@.Email", 200, 1,50,email)
.Parameters.Append .CreateParameter("@.CurrentCustomer", 200, 1,3,currentcustomer)
.Parameters.Append .CreateParameter("@.ImplementationType", 3, 1,4,implementationtype)
.Parameters.Append .CreateParameter("@.ProductType", 3, 1,4,producttype)
.Parameters.Append .CreateParameter("@.Comment", 200, 1,500,comment)
.Execute()
End With
Set InsertTempInfo = Nothing
the error is thrown on the following line:
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.
So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @.state parameter is failing and throwing the error.
I've double checked that the table has the state column set to integer datatype
The column is set as follows:
Name datatype length Allow Nulls
--------------
State int 4 checked
I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?
Not sure what else to look at?
It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?
Thanks for any help.Basic thing to check: Make sure the data type you are passing to the stored procedure is of the same type as the stored procedure is expecting
Subscribe to:
Posts (Atom)