January 2008 - Posts - Danny's SQL Server and Internals Viewer Blog

Stored Procedure parameters

Here’s some more SQL that writes SQL. One way of debugging a stored procedure is to chop off the CREATE PROCEDURE at the top and replace it with DECLARE and SET statements for the variables, then step through the stored procedure.

The following SQL gives an easy way of extracting the stored procedure parameters and creating variables based on the parameters, including the data type. Just copy-paste the output into a query window.

The variable initialization is output as template parameters so you can press Ctrl+Shift+M and easily populate the variables using the Template Parameter window.

The first version is a simple query, the second is a UDF that you can keep in the master database.

Query version:

DECLARE @StoredProcName VARCHAR(100)

SET @StoredProcName = '(Stored Proc Name)'

SELECT 'DECLARE ' + c.name +

' ' + t.name +

CASE WHEN t.name LIKE '%char%'

THEN '(' + CONVERT(varchar, c.max_length) + ')'

ELSE '' END

FROM sys.parameters c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE c.object_id = OBJECT_ID(@StoredProcName)

UNION ALL

SELECT ' '

UNION ALL

SELECT 'SET ' + c.name +

' = <' + c.name +

',' + t.name +

CASE WHEN t.name LIKE '%char%'

THEN '(' + CONVERT(varchar, c.max_length) + ')'

ELSE '' END +

',>'

FROM sys.parameters c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE c.object_id = OBJECT_ID(@StoredProcName)

Example

Output from SET @StoredProcName = 'HumanResources.uspUpdateEmployeePersonalInfo' in the AdventureWorks db:

DECLARE @EmployeeID int

DECLARE @NationalIDNumber nvarchar(30)

DECLARE @BirthDate datetime

DECLARE @MaritalStatus nchar(2)

DECLARE @Gender nchar(2)

SET @EmployeeID = <@EmployeeID,int,>

SET @NationalIDNumber = <@NationalIDNumber,nvarchar(30),>

SET @BirthDate = <@BirthDate,datetime,>

SET @MaritalStatus = <@MaritalStatus,nchar(2),>

SET @Gender = <@Gender,nchar(2),>

User-defined function version:

CREATE FUNCTION dbo.uFn_StoredProcVariables(@StoredProcName SYSNAME)

RETURNS NVARCHAR(MAX) AS

BEGIN

DECLARE @Declares NVARCHAR(MAX)

DECLARE @Sets NVARCHAR(MAX)

SET @Declares =

(SELECT 'DECLARE ' + c.name +

' ' + t.name +

CASE WHEN t.name LIKE '%char%'

THEN '('+CONVERT(varchar, c.max_length)+')'

ELSE '' END + CHAR(10)

FROM sys.parameters c

INNER JOIN sys.types t

ON c.user_type_id = t.user_type_id

WHERE c.object_id = OBJECT_ID(@StoredProcName)

FOR XML PATH(''))

SET @Sets =

(SELECT 'SET ' + c.name +

' = <' + c.name +

',' + t.name +

CASE WHEN t.name LIKE '%char%'

THEN '(' + CONVERT(varchar, c.max_length) + ')'

ELSE '' END + ',>' + CHAR(10)

FROM sys.parameters c

INNER JOIN sys.types t

ON c.user_type_id = t.user_type_id

WHERE c.object_id = OBJECT_ID(@StoredProcName)

FOR XML PATH(''))

RETURN @Declares

+ CHAR(10)

+ REPLACE(REPLACE(@Sets, '>', '>'), '<', '<')

END

Example

PRINT dbo.uFn_StoredProcVariables('HumanResources.uspUpdateEmployeePersonalInfo')

Posted 22 January 2008 07:23 PM by Danny | no comments

Filed under: General

Scuffling with ‘String or binary data would be truncated’

The error ‘String or binary data would be truncated’ can be annoying. It occurs when you try to insert or update a string or binary column with a value that is too large. Recently I was trying to INSERT from a SELECT from one table to another and I got this error. It can be a pain tracking down the cause, especially if there are a large number of columns or a large dataset involved.

In the past I’ve written queries to give me the LEN for each column, but again if there are a large number of columns involved this can be very time consuming.

Below is a way of identifying which rows are causing the problem. This doesn’t help if you’ve got a large number of columns, as you still need to work out which field is causing the problem, but it will help if you have a large dataset and the problem rows are very sparse.

For this example I’ll create a couple of tables and generate some data. The source table has a column of VARCHAR(50), whereas the destination has VARCHAR(25):

CREATE TABLE SourceTable

(

RowId INT

,Chars INT

,String VARCHAR(50)

)

GO

CREATE TABLE DestinationTable

(

RowId INT

,Chars INT

,String VARCHAR(25)

)

GO

Next the tables are populated with a random number of ‘X’s, between 0 and 50. In theory you should get about 50% with a length above 25 characters and 50% below.

DECLARE @i INT

DECLARE @RandomNumber INT

SET @i=0

WHILE @i <= 50

BEGIN

SET @RandomNumber = ROUND(50 * RAND(), 0)

INSERT INTO SourceTable

SELECT @i, @RandomNumber, REPLICATE('X', @RandomNumber)

SET @i=@i+1

END

GO

Next try inserting from SourceTable to DestinationTable:

INSERT INTO DestinationTable

SELECT * FROM SourceTable
GO

This results in the error:

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

It’s possible to ignore the 'String or binary data would be truncated' message by setting ANSI_WARNINGS to OFF. This will truncate fields where they don’t fit. ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it.

The following can be used to work out which rows are causing the issue:

1. Take a copy of the destination table:

SELECT * INTO #Destination FROM DestinationTable WHERE 1=2

GO

2. Set ANSI_WARNINGS OFF and perform the insert into the copy of the destination table, then set ANSI_WARNINGS ON again:

SET ANSI_WARNINGS OFF

GO

INSERT INTO #Destination

SELECT * FROM SourceTable

GO

SET ANSI_WARNINGS ON

GO

As ANSI_WARNINGS is off SQL Server truncates the fields rather than produces the warning.

3. Next compare what you would like to insert against what was inserted with the ANSI_WARNINGS OFF truncating. By using EXCEPT you only select the rows that don't match, and have therefore been truncated:

SELECT * FROM SourceTable

EXCEPT

SELECT * FROM #Destination

GO

The rows that have been truncated and are the cause of the ‘String or binary data would be truncated’ error.

(Note - The use of EXCEPT limits this to 2005/2008. The finaly query could be re-written for SQL Server 2000 and below.)

This isn’t the most elegant solution, and as I said if there were a large number of columns you’d still need to hunt through for the offender(s), but at least this gives an idea of where to look. I may have missed some glaringly obvious solution to this problem, so I’d be interested to know if anyone has any other ways of dealing it.

Posted 12 January 2008 07:27 PM by Danny | 3 comment(s)

SQL Internals Viewer on LearnSQLServer.com

Scott Whigham at LearnSQLServer.com has featured SQL Internals Viewer in a new series of video tutorials. The site has a whole range of video tutorials on SQL Server covering the basics right up to advanced topics.

I've seen the videos and they are a good introduction to the app and what you can do with it.

The videos are available here (requires subscription).

Posted 12 January 2008 05:46 PM by Danny | no comments

Filed under: General

SQL Server 2008 TIME data type

The new TIME type stores a time with a specified scale that defines the fractional second precision.

The scale ranges from 0-7 representing 0-7 significant digits for the fractional seconds. The default precision is TIME(7), giving 7 significant digits, a range of .0000000 to .9999999.

TIME is stored as an integer of various sizes, depending on the scale. For a scale of 0-2 it is stored as a 3 byte integer, 3-4 a 4 byte integer, and for scale 5-7 it is stored as a 5 byte integer.

The scale is then used to calculate the time since midnight, with an accuracy ranging from 1 second to 100 nanoseconds.

If t is the value stored in the time column and n is the scale the time from midnight in seconds can be calculated by t / 10ⁿ.

Here’s a summary of the storage and scaling (seconds, milliseconds, and nanoseconds are the respective duration t is multiplied by):

Scale	Storage (bytes)	Seconds	Milliseconds	Nanoseconds
TIME(0)	3	1	1000	1000000000
TIME(1)	3	0.1	100	100000000
TIME(2)	3	0.01	10	10000000
TIME(3)	4	0.001	1	1000000
TIME(4)	4	0.0001	0.1	100000
TIME(5)	5	0.00001	0.01	10000
TIME(6)	5	0.000001	0.001	1000
TIME(7)	5	0.0000001	0.0001	100

It’s possible to extract the unscaled value from a TIME value, although it requires a few steps.

DECLARE @Time TIME(7) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]

DECLARE @BinaryTime VARBINARY(8)

SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),

1,

DATALENGTH(@Time))

SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value

-- Result: 600000000

The above example gives a result of 600000000, which, looking at the scale makes sense. The scale is 7, so a time of 1 minute past midnight is 60 seconds = 600000000 / 10⁷.

DECLARE @Time TIME(3) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]

DECLARE @BinaryTime VARBINARY(8)

SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),

1,

DATALENGTH(@Time))

SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value

-- Result: 60000

A scale of 3 gives a result of 6000 as 60 seconds = 6000 / 10³

Books Online has more information about the new DATE type here.

Posted 10 January 2008 07:34 PM by Danny | 1 comment(s)

Filed under: SQL Server 2008, Internals, Data types

SQL Server 2008 DATE data type

SQL Server 2008 has several new data types, including new date and time types. In a series of short posts I’ll go into how these data types are structured. All of these new types are supported in SQL Internals Viewer, and a new data type viewer is coming up in a future version of the app.

The new date and time data types are:

DATE – Stores a date value
TIME – Stores a time value with an accuracy of up to 100 nanoseconds
DATETIME2 – Stores a date and time value with the higher TIME accuracy
DATETIMEOFFSET – Stores a date and time value with a time zone offset

DATE type internals

The date type simply stores a date, ranging from January 1st 0001 (1 AD) to December 31st 9999. Internally the type is stored as a 3 byte (24-bit) integer. The integer value is the number of days since the base date of 01/01/0001.

It isn’t possible to convert from an INT to DATE directly. Running SELECT CONVERT(DATE, 1) will result in the following error:

Msg 529, Level 16, State 2, Line 1

Explicit conversion from data type int to date is not allowed.

However it is possible to convert to from INT to DATE by converting first to BINARY(3), reversing the bytes, and converting to DATE. (I’ll explain why you need the REVERSE and CONVERT in a subsequent post.)

This shows each increment of the 24-bit integer represents a day from the base date:

DECLARE @IntValue INT

SET @IntValue = 0

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

-- Result: 0001-01-01

SET @IntValue = 1

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

-- Result: 0001-01-02

SET @IntValue = 2

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

-- Result: 0001-01-03

SET @IntValue = 3

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

-- Result: 0001-01-04

Unlike DATE and SMALLDATETIME it doesn’t seem possible (with the November CTP) to add and subtract days from a date:

DECLARE @Date DATE = '2008-02-01'

SELECT @Date + 1

This results in the error:

Msg 206, Level 16, State 2, Line 2

Operand type clash: date is incompatible with int

Adding two dates together also results in the following error:

Msg 8117, Level 16, State 1, Line 3

Operand data type date is invalid for add operator.

Books Online has more information about the new DATE type here.

Next up, the new TIME type.

Posted 08 January 2008 06:57 PM by Danny | no comments

Filed under: SQL Server 2008, Internals, Data types

FOR XML and back again

I’ve used the 2005 XML features for a few things now and I’m getting to quite like it. One thing I’ve found is that it is a lot easier to get data into XML than it is to get it back out again.

The following is a stored procedure that given an XML variable and a table name will dynamically construct and execute the SQL needed to shred XML back into a table. I knocked it together quite quickly and at the moment it only works on XML that has been created by FOR XML AUTO, although it could quite easily be modified for the other XML options.

CREATE PROC dbo.uSpShredTable @Xml XML, @TableName SYSNAME AS

DECLARE @Sql NVARCHAR(MAX)

SELECT @Sql = 'SELECT ' +

STUFF((SELECT ' ,T.Data.value(''@' +

c.name + ''', ''' +

t.name +

CASE WHEN c.user_type_id IN (165,167,173,175,231,239)

THEN '(' + CONVERT(VARCHAR, c.max_length) + ')'

WHEN c.user_type_id IN (106, 108)

THEN '(' + CONVERT(VARCHAR, c.precision)

+ ', ' + CONVERT(VARCHAR, c.scale) + ')'

ELSE '' END +

''') AS ' + c.name + CHAR(10)

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE object_id = OBJECT_ID(@TableName)

AND t.name !='xml'

FOR XML PATH('')), 1, 7, '') +

'FROM @Xml.nodes(''/' + @TableName + ''') T(Data)'

EXEC sp_executesql @Sql, N'@Xml XML', @Xml = @Xml

GO

Example usage:

-- Get some XML

DECLARE @Xml XML

SET @Xml = (SELECT * FROM Person.Contact FOR XML AUTO)

-- Get a table back

EXEC uSpShredTable @Xml, 'Person.Contact'

GO

How it works

The stored procedure constructs and executes the following SQL:

SELECT T.Data.value('@ContactID', 'int') AS ContactID

,T.Data.value('@NameStyle', 'NameStyle') AS NameStyle

,T.Data.value('@Title', 'nvarchar(16)') AS Title

,T.Data.value('@FirstName', 'Name') AS FirstName

,T.Data.value('@MiddleName', 'Name') AS MiddleName

,T.Data.value('@LastName', 'Name') AS LastName

,T.Data.value('@Suffix', 'nvarchar(20)') AS Suffix

,T.Data.value('@EmailAddress', 'nvarchar(100)') AS EmailAddress

,T.Data.value('@EmailPromotion', 'int') AS EmailPromotion

,T.Data.value('@Phone', 'Phone') AS Phone

,T.Data.value('@PasswordHash', 'varchar(128)') AS PasswordHash

,T.Data.value('@PasswordSalt', 'varchar(10)') AS PasswordSalt

,T.Data.value('@rowguid', 'uniqueidentifier') AS rowguid

,T.Data.value('@ModifiedDate', 'datetime') AS ModifiedDate

FROM @Xml.nodes('/Person.Contact') T(Data)

This is done using information from sys.columns and sys.types to get the columns and data types for the table. The only type it can’t handle is the XML type, so this is excluded. FOR XML AUTO adds columns as attributes, where each element represents a row. The SQL simply reads the specified attribute values.

Posted 06 January 2008 10:17 PM by Danny | 2 comment(s)

Filed under: XML, General

Hello!

Hi,

My name is Danny and I’m the author of a tool called SQL Internals Viewer. If you haven’t already seen the application it can be downloaded here. Essentially the tool is a window on the SQL Server Storage Engine, displaying how SQL Server structures and stores data internally.

My plan for this blog is to cover a few things. Firstly I’d like to explain the functionality of the application. Second I’m going to cover some fundamentals of the Storage Engine so you can put what the viewer displays into context. Finally I’ll cover some general information that I’ve found out through the development of the app, and occasionally I’ll veer into more general SQL stuff.

I’ll try to keep the posts fairly short and concise so this blog isn’t a chore to read! I’ll also try to include a lot of examples. If there is anything you are interested in or if you would like me to go into a particular aspect of the app, including how it works, please let me know. I can be contacted at danny@sqlinternalsviewer.com.

Posted 03 January 2008 11:57 PM by Danny | 2 comment(s)

Danny's SQL Server and Internals Viewer Blog

January 2008 - Posts

Search

This Blog