June 14, 2011 at 9:39 am
Who can explain what actually happens when you insert a number _without_ quotes (it actually happened by mistake) into an NVARCHAR field? Here is a simple test:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_insert_number]') AND type in (N'U'))
DROP TABLE [dbo].[test_insert_number]
GO
CREATE TABLE [dbo].[test_insert_number]([text] [NVARCHAR](50) NULL) ON [PRIMARY]
GO
TRUNCATE TABLE [dbo].[test_insert_number]
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (123) -- inserts '123' - why?
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (00123) -- inserts '123' - why? where are the zeroes?
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES ('a0123b') -- works as expected
--INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (a0123b) -- fails (as it should)
SELECT * FROM [dbo].[test_insert_number]
--text
--123
--123 -- this means that 00123 is first converted into an INT, which is then converted into a string (!)
--a0123b
Can anyone provide an explanation?
June 14, 2011 at 10:38 am
alexms_2001 (6/14/2011)
Who can explain what actually happens when you insert a number _without_ quotes (it actually happened by mistake) into an NVARCHAR field? Here is a simple test:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_insert_number]') AND type in (N'U'))
DROP TABLE [dbo].[test_insert_number]
GO
CREATE TABLE [dbo].[test_insert_number]([text] [NVARCHAR](50) NULL) ON [PRIMARY]
GO
TRUNCATE TABLE [dbo].[test_insert_number]
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (123) -- inserts '123' - why?
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (00123) -- inserts '123' - why? where are the zeroes?
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES ('a0123b') -- works as expected
--INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (a0123b) -- fails (as it should)
SELECT * FROM [dbo].[test_insert_number]
--text
--123
--123 -- this means that 00123 is first converted into an INT, which is then converted into a string (!)
Yes. Integers are the default data type SQL Server will assume when you provide a literal whole number unless you wrap it in a CAST function or add decimal places (e.g. 1.0 will be treated as NUMERIC(2,1)). You also take a performance hit for the conversion from INT to string, so it's best to quote your strings.
Can anyone provide an explanation?
SQL Server does not maintain leading zeroes on any numeric data types because they do not affect the value.
Edit: clarify defaults as INT and NUMERIC
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 10:51 am
PS For NVARCHAR fields it's best to prefix your string literals with the capital N:
INSERT INTO [dbo].[test_insert_number] ([text]) VALUES (N'a0123b') -- works as expected
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 1:56 pm
To opc.three:
> Integers are the default data type SQL Server
... and we know it how?
This is what the question was all about, but googling for something like "sql server default data type" brought essentially nothing.
However, it turns out that (per http://www.dbforums.com/microsoft-sql-server/1211979-default-data-types-sql-server.html) that "If I import a database into SQL Server it makes every non-numeric field a nvarchar field", which is kind of inverse: not "try int first" but "try nvarchar first".
> SQL Server does not maintain leading zeroes on any numeric data types
Of course. The point was to experimentally prove that an invalid string (no quotes, no N+quotes) is converted to an int first, see comments in the test script.
> it's best to prefix your string literals
I happen to know that, the question was - what happens with invalid "strings"? Where is it documented, if it is?
Again, the reason for asking a question was an error that went unnoticed for years just b/c the string being inserted happened to always contain digits only.
June 14, 2011 at 2:04 pm
SELECT 1 AS [No reason to get testy...],
1.0 AS [unpaid volunteer trying to help you here]
INTO dbo.[Was it something I said?]
EXEC sys.sp_help
@objname = N'dbo.[Was it something I said?]' ;
DROP TABLE dbo.[Was it something I said?]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 4:17 pm
alexms_2001 (6/14/2011)
To opc.three:> Integers are the default data type SQL Server
... and we know it how?
This is what the question was all about, but googling for something like "sql server default data type" brought essentially nothing.
However, it turns out that (per http://www.dbforums.com/microsoft-sql-server/1211979-default-data-types-sql-server.html) that "If I import a database into SQL Server it makes every non-numeric field a nvarchar field", which is kind of inverse: not "try int first" but "try nvarchar first".
That article actually has nothing to do with what's going on; the import process (not the database!) chooses NVarChar as the default data type because it's the universal receiver, you can cram basically any data type in to an NVarChar field.
> SQL Server does not maintain leading zeroes on any numeric data types
Of course. The point was to experimentally prove that an invalid string (no quotes, no N+quotes) is converted to an int first, see comments in the test script.
I think the major problem with you question is, there is no such thing as an invalid string in T-SQL on MSSQL.
select 0000000056 --compiles down to a constant = 56
select a00056 --compiles down to "select column named a00056", which doesn't existEither way, the query is reduced before execution occurs. 0000056 contains no decimal place, so it's assumed to be an int; 9999999999 is too large for an integer, so it's a big int, 23.34 becomes a single float, etc. And this part is documented:
http://msdn.microsoft.com/en-us/library/ms187745.aspx
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
> it's best to prefix your string literals
I happen to know that, the question was - what happens with invalid "strings"? Where is it documented, if it is?
Again, the reason for asking a question was an error that went unnoticed for years just b/c the string being inserted happened to always contain digits only.
If you run the estimated execution plan, you can see the insert statements actually never tries to type the data as int (again, it's reduced prior to the optimizer getting a hold of it).
Scalar Operator(CONVERT_IMPLICIT(nvarchar(50),[@1],0))Once execution begins, everything gets cast directly to the storage type, int is never involved.
As for the prefixing statement, had you actually prefixed with N the missing quotes would have resulted in N0000056 which would have thrown an error and you probably would have caught you error years ago. 😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply