SQL Server Avoid Typing Field Names Tip

After 3 years of using SQL Server I stumbled across this shortcut for writing SQL Statements. How many times have you had to write a SQL statement that contains 80% of the fields of a table, or maybe an insert statement where fields are specified and you need to type all of them out. You get the drift typing field names suck. The easy way to get around this in SSMS (SQL Server Management Studio) is:

1. Expand your Tables of a Database Working on
   
2. Drill down on the tables
   
3. Pull the Columns root (Lack of a better term) over to the Query window
   
4. All the columns will get pulled over in text

to expand your tables within a Database

SQL Server Reseeding Identity Values

Maybe it just me, but when working in ETL processes I find it necessary to reseed the identity seed value. I find gaps appearing Most of the time this is caused by rolling back data that came in bad. Before I import any data I run the script below to perform a reseed. It’s fairly simple but saves head-aches, particularly on large DB’s, where the threat of hitting 2 billion or the max on a int is possible. Here the PKInt represents the identity column I’m reseeding.




Declare @MaxSeed int
Select @MaxSeed = max(Pkint) from ImportTable
DBCC CHECKIDENT ('ImportTable', RESEED, @MaxSeed )

SQL Server Using Charindex and Substring Functions to Peel out Data

The substring function on allows you to pull out a portion of a string. For example the if you want to pull out the domain name out of the url below, you’d run a query similar to that below.

select substring('www.testurl.com/testpage.htm', 1, 15)


So the substring functions starts at the beginning of the string (1), and moves a legnth of 15 characteers to grab all the data between the two. The result of the above query comes to “www.testurl.com”, and pealing off the page of the url. This works well when you know the size of the url, but not so when it’s dynamic. For example the this query won’t work against a different url.

select substring('www.newtesturl.com/testpage.htm', 1, 15)

The key is too make the second (Start) and third (Legnth) parameters dynamic. This is where the charindex comes into play. The charindex looks for a specific characteer and finds its position within the string. For example if you want to pull out of the url everything prior to the first occurance of a forward slash “/” you first need to find it’s characteer position. The statement below returns a result of 19, meaning the “/” is the 19th characteer from the start.

select charindex('/', 'www.newtesturl.com/testpage.htm', 1)


Before putting them together there is a trick. If we run the following query you’ll get a result which includes the forward slash.

select substring('www.newtesturl.com/testpage.htm', 1,
charindex('/', 'www.newtesturl.com/testpage.htm', 1))

In order to eliminate the forward slash we want to subtact 1 from the charindex portion giving you a statement the looks like so.

select substring('www.newtesturl.com/testpage.htm', 1,
charindex('/', 'www.newtesturl.com/testpage.htm', 1)-1)

SQL Server Truncating Multiple Tables With Foriegn Keys

A typical task of a DBA is to truncate multiple tables in a database. Often this is used to shrink large datasets, to peel off irrelevant information for test or development. One of the obstacles to overcome in truncating data is foreign key references. This is because a truncation is just a drop/create table statement. The following script uses a cursor to loop the sys.tables to grab table names, and building a dynamic statement for the truncation. Prior to the truncation it loops through sys.foreign_keys DMV to grab the all references and deletes them first.

--Will hold Table Name
Declare @TName varchar(100)
--Will Hold FKName
Declare @FKName varchar(100)
--Hold TableName of foriegnKey
Declare @PName varchar(100)
--Hold Dynamic Statement
Declare @Stmt varchar(200)


DECLARE truncate_Cursor CURSOR
FOR
SELECT Name from sys.tables where Name in

--Place tables you want to truncate in list
(
't1',
't2',
't3',
't4',
't5',
't6'
)

Open truncate_Cursor
FETCH NEXT FROM truncate_cursor
into @TName

WHILE @@FETCH_STATUS = 0
BEGIN

--Delete Foriegn Keys
Declare FK_Cursor Cursor For
Select name, object_name(parent_object_id) from sys.foreign_keys
where referenced_object_id = object_id(@TName)

Open FK_Cursor
FETCH NEXT FROM FK_cursor
into @FKName, @Pname

WHILE @@FETCH_STATUS = 0
BEGIN
set @stmt = 'Alter table ' + @Pname + ' Drop constraint ' + @FKName
Exec(@Stmt)
FETCH NEXT FROM FK_cursor
into @FKName, @Pname
End

CLOSE FK_Cursor
DEALLOCATE FK_Cursor

--Run Truncation
set @stmt = 'Truncate table ' + @Tname
Exec(@Stmt)



FETCH NEXT FROM Truncate_Cursor
into @Tname
END
CLOSE Truncate_Cursor
DEALLOCATE Truncate_Cursor

SSIS Dynamic Flat File Destinations with SQLCMD

Our group uses SSIS exclusively for both ETL and standard tasks. Without getting into all the specifics of why we’ve chosen this technology, at the heart is the ability for any team member to troubleshoot someone else’s code, and if designed right gives the ability to create generic packages with an OOP approach. It’s this last point that has lead to challenge in design.

We wanted to design a package where we could drop any SQL statement into a file, and allow the package to dynamically connect to SQL Server execute the statement, and export it to a flat file. As we found the only way to accomplish this through the native SSIS components is tweak the metadata of the package (XML Description) prior to the execution. The reason for this is the metadata of the connection (Source/Destination) are baked into the package itself. The workaround we found was the sqlcmd utility. Before getting into the details let me state I understand that there are other utilities such as BCP and that using SSIS in this way may not be the best way to go. My answer is in our case we need to maintain SSIS for conformity with our other processes and the ability to leverage other tools within the SSIS framework. This blog doesn’t go into the extra tools required, but simply focus’s on the nuts and bolts of the package.

Using SQLCMD
Below is the following core statement that will be used. Many of these parameters will equate to variables within your package. By keeping these as variables it allows you to make your package generic.

sqlcmd -Slocalhost -dAdventureWorks -i"C:\testing\inputquery.sql" -o"c:\testing\outfile.txt" -h-1 -s"," -W


-S[Server] – Is the server your running against and maps to package variable ServerName
-d[Database] – Is the database your statement will execute from. This is mapped to the package variable DBName.
-i[Input File] - This the location you drop the sql file. This is mapped to the package variable QueryFile
-o [OutputFile] – Location of the flat file your exporting to. This is mapped to the package variable QueryOutPutFile
-h – Is the header parameter with a value of “-1” that translates to no header.
-s – Is the column separator parameter with a value of “,” which means use a column separator of a comma
-W – Trims any trailing spaces.

Since sqlcmd is an executable you’ll need to use a process task component to execute it. The process task is separated into two main properties. The executable name, and the arguments it accepts. The arguments are represented above, but since most are input variables they need to be dynamic at runtime. For cleanliness I create the arguments inside a package variable in this case called Args. The expression looks like so.

"-S" + @[User::ServerName] + " -d" + @[User::DBName] + " -i \"" + @[User::QueryFile] + "\" -o\"" + @[User::QueryOutPutFile] + "\" -h-1 -s\",\" -W"

Which evaluates to:
-Slocalhost -dAdventureWorks -i "c:\testing\inputquery.sql" -o"c:\testing\outfile.txt" -h-1 -s"," –W




Once this argument is in place assign the variable to the process task’s argument property, and the sqlcmd to the executable property.

For testing attach to your local Adventureworks database, insert a query into the inputfile specified in the QueryFile Variable (Select * from Sales.SalesPerson) and select a proper output file. If all went well you’ll notice a nice comma delimited resultset, except at the bottom where you’ll see the amount of rows spit out, by the query.

“(17 rows affected)”

This will obviously blowup your format. In order to work around this you need to make sure your query has the command of “set nocount on” as the first command in the statement. So you’ll need to make sure your developers write their query in this fashion. Yes that is a joke, for developers to code against the DBA’s standards, we know they’ll forget at least half the time. So in order for this to happen you need to build a component that will automatically insert this as the first statement. Prior to running the process task, create a script task, that does just this. The following code writes this statement out to the beginning of the file prior to execution.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text

Public Class ScriptMain

Public Sub Main()
Dim bt As Byte()
Dim fs As FileStream
Dim fPath As String
Dim b(1024) As Byte
Dim temp As UTF8Encoding
Dim queryHold As String
fPath = Dts.Variables("QueryFile").Value.ToString

queryHold = "Set Nocount on " & vbCrLf & File.ReadAllText(fPath)
fs = File.OpenWrite(fPath)
bt = New UTF8Encoding(True).GetBytes(queryHold)
fs.Write(bt, 0, bt.Length)
fs.Close()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

With this place your all set.

SQL Server Parsing Strings Based on Right Side Characters

I’m currently working on a project that requires extracting data based on URL’s. However the entire url is not needed only the text to the far left hand side of the last forward slash “/”. For example below I want to exclude the junk string.

/level1/level2/level3/junk.

Since the levels can vary I need a parser that will work from the right hand side backwards. SQL Server lacks the functionality to perform this. This is not a bad thing since string manipulation should be done outside the DB in some form a managed code, and giving developers this functionality, would only lead to headaches and expenses.

To circumnavigate the lack of functionality I wrote a very straight forward CLR to perform the parsing. Notice I’ve got two separate functions in the code, one that grabs the last value, and one that excludes it. Once registered with SQL, either can be used.

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System;


public partial class CustomStringFunctions
{
[SqlFunction()]
public static SqlString leftValueIncludeIndexChar(string originalString, string charVal)
{
int substringEnd;
string finalData;
int charValLength;

charValLength = charVal.Length;
substringEnd = originalString.LastIndexOf(charVal);
finalData = originalString.Substring(0, substringEnd + charValLength);
return finalData;

}

public static SqlString leftValueExcludeIndexChar(string originalString, string charVal)
{
int substringEnd;
string finalData;

substringEnd = originalString.LastIndexOf(charVal);
finalData = originalString.Substring(0, substringEnd);
return finalData;

}
}

Once the assembly is compiled Register it and insert it into a function. Below I created two functions, one to include the characteers and one to exclude.


Create assembly LastCharIndex
from 'c:\testing\lastindex.dll'
go
Create function LastCharIndexInclude(@InputString nvarchar(500), @Criteria nvarchar(50))
Returns nvarchar(500) External Name LastCharIndex.CustomStringFunctions.leftValueIncludeIndexChar

Create function LastCharIndexExclude(@InputString nvarchar(500), @Criteria nvarchar(50))
Returns nvarchar(500) External Name LastCharIndex.CustomStringFunctions.leftValueExcludeIndexChar



Examples:

select dbo.LastCharIndexInclude('/level1/level2/level3/junk', '/')
select dbo.LastCharIndexExclude('/level1/level2/level3/junk', '/')

SQL Server Memory Usage With Text Fields

In a recent blog I talked about covering indexes for memory optimization within the buffer pool. I used a fictitious comments table to make my point, about how one large field could destroy querying for particular values. After posting I got the thinking about the example I used, and in the real world would I actually design it, in that fashion. The waffling answer is maybe. The reason I say this is that there is another solution that works with the clustered index, without the need to create a second covering index. Rather then using a Char or a Varchar to hold the actual comments field, you could use a text column. Text fields by default reside in separate data pages then the other fields within the row. Rather then these text fields residing in the same b-tree as the clustered index the data row contains a pointer to the text data that lives in a separate page. The effect this has on your memory consumption is, if the text field is not specified within your query it is never brought into memory. Lets go through some code examples similar to my last blog to see the actual impact.


The following code sets up 2 tables, one using a char field for comments and one using a text field for comments.


Create table CommentsText
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText text,
CONSTRAINT Pk_intText PRIMARY KEY CLUSTERED (PkComment))

Create table CommentsChar
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intChar PRIMARY KEY CLUSTERED (PkComment))





Declare @int int
set @int = 1
While @int <>
Begin
insert into CommentsText(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end

Insert into CommentsChar
Select CommentType, CommentDate, CommentText from CommentsText



Once setup I’ll free the memory, and run the same aggregation query of my last blog, where we aggregate based on types of comments for each day.


checkpoint
dbcc Dropcleanbuffers

select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate


select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate


Once complete, we’re going to run a memory allocation script very similar to the one found in BOL under the sys.dm_os_buffer_descriptors section

--Memory Consumption Script

SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC

The results show a difference of a 10 to 1 difference in memory usage favoring the table using a text field. As explained in the last blog this is due to fact that the comments field does live with the rest of the row data. However when we add a covered index, this changes for the char table by eliminating the comments field from the queried data structure.



Create index commentschartest on commentschar(CommentType, CommentDate)

checkpoint
dbcc Dropcleanbuffers

select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate


select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate




--Memory Consumption Script

SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC

The results now show both tables consuming reasonable, with slightly better performance going to the table using the covered index. The difference in size can be attributed to the overhead of maintaining pointers to the text fields in the root data, which is 24 bytes of data per row. The question as a DBA you need to make within your envirornment is; Is it more efficient to use a covered index or simply put the data in a text field. In most situations the covered index, allows for more flexibility, and better overall performance.

Before leaving the subject I’d like to touch on the idea of using Varchar(max) as in comparison to a Text field. Varchar(max) tries to provide the best of both worlds. The rule of thumb on varchar(max) is data that will fit on a single page with the other data in the row, will live side by side with the data. However if the whole field exceeds 8KB then the field will be placed in overflow pages. From a memory consumption point of view, with a similar query above, the text field will be more efficient, for the queries we’ve been looking at.

SQL Server Memory Usage With Covered Indexes

In my last blog I talked about clearing memory caches to free up memory. Most of the time this is a bit of a hack. The true solution lies in shrinking how much data actually goes into memory, which falls upon the size of your data structures. In particular how much data can you fit into a SQL Server data page. SQL Server maintains a one to one relation between it’s data pages on disk, and the data pages brought into memory. It’s also an all or nothing deal, meaning if you need one column of 4 bytes from one row, you need to bring the entire 8KB data page up into memory. In this blog I’ll talk about how to use covered indexes to maximize these data pages, so you’re not using memory on unneeded datum.

Before getting going I want to provide a data setup script as well as memory consumption query that will be used through out this post. The memory consumption query is a knock off of the one found in BOL under sys.dm_os_buffer_descriptors, with a couple of tweaks.

--Data Setup

Create table Comments
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intT1 PRIMARY KEY CLUSTERED (PkComment))


Declare @int int
set @int = 1
While @int <>
Begin
insert into Comments(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end

--Memory Consumption Script

SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC


Now that we’re ready to go, let me explain, what we have. The newly created comments table is a fairly common structure for holding user comments. The bulk of the table is the comment itself with some metadata to describe it. CommentType might map to; Question, Comment, Authors reply, Etc, while commentDate is the Date of the comment. In this current setup the root pages of the clustered index is capable of holding only 3 rows per data page, because of the large size of the comments field. Before starting let’s flush the memory.

checkpoint
dbcc Dropcleanbuffers


Assume that in the application one of the calls aggregates the count of this table, by type and date. Notice in this query that text is never referenced.


select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate

Once complete run the memory consumption query. You’ll notice 23279 data pages or 181MB’s of memory are being consumed here. The ironic part is most of the data allocated is not needed. This is where covering your index comes in. By covering your index you’re creating a b-tree data structure with data only required to fullfill the data requirements of one or more specific queries. Let me preference the last statement in that your index will contain the values of your primary key for reference. Below is the statement to create the index.

create index coveredtest on Comments(CommentType, CommentDate)

Once Complete rerun the following Queries, by clearing the memory, running the aggregate statement, then running the memory consumption script. The consumption script is left out below to save space.


checkpoint
dbcc Dropcleanbuffers

select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate

Not only did the query return considerably faster but the data page usage is down to 211 or 2 MB. Over the entire server this will become a significant savings. Before ending this, I need to add fair warning, as know your application before implementing. If modifications are occuring regularly, or your comments are being queried in such a way that the commentText column is being read regularly, then it does belong in memory, and your savings will not be that significant, and might actually hurt your memory pool. On that my last word in the real world you’d use a varchar over a char, but this was easiest to make a point.

SQL Server Finding Last Day of the Month using DateDiff and DateAdd

Getting the last day of the month is a critical aspect of retrieving data in many circumstances the following scripts are designed to do just that.

This first script figures out the last day of the month for the previous month.

select dateadd(month,datediff(month, 0, getdate()), 0) - 1

To truly understand what’s going on we need to break it down. On the inner function datediff. The syntax for datediff is

DATEDIFF (datepart , startdate , enddate )

However you can as I do below and above is subsititue either the startdate or endate with an integer. This number represents the amount of days since the beginning of time, as SQL Server sees it, or quite simply from 1/1/1900. For example the “one” in the startdate below translates to ‘1/2/1900’, or one day after 1/1/1900. The result function is how many full months have passed between 1/2/1900 and 1/1/2000

select datediff(month, 1, '1/1/2000')

As of writing this on 2/16/09 the following script yeilds a value of 1309. Meaning 1309 months have passed between 2/16/09 and 1/1/1900.

select datediff(month, 0, getdate())

This number is then used with the dateadd function, that again leverages sql servers concept of the beginning of time. Here below using the 1309 result from above I get the first day of the month, by adding 1309 months (retrieved in the datediff) to get the first day of the month.

select dateadd(month, 1309, 0)

Now by subtracting 1 day from the results, I’m able to get the last day of the month.

select dateadd(month, 1309, 0) – 1

Put this all together dynamically and it looks like the first statement.

select dateadd(month,datediff(month, 0, getdate()), 0) – 1


There are other times you might need to retrieve the last date of the month for the previous ten years. The script below does just that.


create table #LastDayHold
(dte datetime)
Declare @Interval int
set @interval = 0
while @interval <>
Begin
Insert into #LastDayHold select dateadd(month,datediff(month, 0, getdate()) - @interval, 0) - 1
Set @interval = @interval + 1
end
select * from #LastDayHold

SQL Server Performance Impact of Modifying Columns through Alter Table Part II

Introduction
In part one I talked about the effects of a DDL or Alter Table statement has on your live system. However all Alter Tables statements are not created equal. When a DDL statement is run there are two primary ways SQL Server handles these changes. For a lack of better terminology I refer to these two as “Immediate DDL Update” and “Delayed DDL Update”. Understanding each type is critical for a DBA or Developer in making decisions on how and when to run an Alter Table Statement. The primary purpose of this article is to be used as a cheat sheet, to help you decide on timing, coding and design.


Immediate DDL Update
This is the most straight forward type of modification. An alter table statement is performed and the underlying data structure is changed immediately to accommodate this change. This operation is significantly disruptive to the concurrency of an OLTP system, because it’s physically changing and reorganizing data on every row in your table. As you remember from part I a schema lock is issued, during these DDL statements and therefore the entire table and all its dependencies are inaccessible for the duration of the update. In line with that logic, the longer the update the lower your concurrency.

If you are running this type of command it’s best to ensure there is free space in your data page to accommodate the additional data or reorganization. This might mean reindexing with the appropriate fill factor during off hours, then running the statement. Doing so will alleviate much of the physical page IO processes, which might entail page splits and allocations, resulting in IO latching, and fragmentation.

Delayed DDL Update
This type of update works with meta-data only, and avoids changing underlying data until needed. Because it doesn’t change the data it allows for the DDL command to execute instantaneously, and improves concurrency within your system. I use the term delayed because eventually the underlying data will change with data reorganization such as a reindex. As I go through the different examples below, you’ll see that there are two flavors here one that simply makes the meta-data change, and one that first needs to validate the underlying data.

Modification Types

Adding Null Columns Without Defaults
This is a “Delayed DDL Update”. Here a new column is added but since it allows Null values, and the data going in is null, only metadata changes are made. If this change is made on a highly active OLTP system users will not notice any effects. The real work comes when an update or insert statement is executed against this column. When an update occurs data is added to the row, and the data structure is modified to ensure this new data can fit. It’s here that you’ll see IO issues mentioned above. As long as there are no immediate bulk updates that follow, this can be performed anytime. If you use a default value then refer to the next example, for guidance.


Adding Not Null Columns
When adding a Column that’s “Not Null” it requires you use a default value, as part of the Alter Table statement. If an int column is added, then four bytes of data is added to row. Since the data is being changed underneath it is an “Immediate DDL Update”, which translates into Schema locking and IO processing. Perform this statement during off hours, if the table is particularly large.

Increasing Size on a Varchar Data Type
This is a “Delayed DDL Update”, making changes only in meta-data. This is based on the fact that variable data by nature is meta-data specific using offsets at the end of the data row. When moving from a Varchar(10) to a Varchar(12), no data is changed. This is a perfectly safe statement to run during high oltp times.

Decreasing Size on Varchar Data Types
This is also a “Delayed DDL Update”, but unlike the previous example there is overhead. This is because SQL must first check to ensure that existing values will fit in the new smaller cap. For example when going from Varchar(12) to Varchar(10) SQL must make sure there’s no strings with more then 10 characters. This check is performed using a table scan. Whether this can be done during high transactional times is dependent on two factors. The first is the size of the table, how long does it take to run a table scan on the modified table. The second factor is indexes. Unlike increasing the size where indexes are not affected, this modification can not be performed when the altered column has an index on it. It must first be dropped, perform the statement, and then recreate.

Changing Size on Char Data
Whether you’re increasing or decreasing the size of a Char datatype it’s an “Immediate DDL Update”. Data is copied within the data row to a new location at the end of the fixed length portion of the row. The old data is only soft deleting meaning it still exists within the row, but is not accessible to the user. If you go from char(10) to Char(20) you will increase the data row 20 bytes, while going from Char(20) to Char(10) increases the row 10 bytes. As with the above example all indexes referencing this column must be dropped prior to the action. If you plan on doing this on a hot database, I’d recommend doing during off hours, as there is heavy IO here.

Changing a Not Null Column to Null
This is a “Delayed DDL Update” that is a pure meta-data change. Within a data row null values are tracked in a bitmap, that resides in every row whether there is a null value or not (This is new with SQL Server 2005). Changing to a Null datatype simply alerts SQL to make appropriate changes to this bitmap if a null value is inserted or updated in the modified field. In addition you need not worry about referencing indexes, the procedure can run without needing to drop them. It’s perfectly acceptable to run this during hot times.

Changing Null to Not Null
Like the example above this too is a “Delayed DDL Update”. However unlike it’s sibling it’s not instantaneous, because a check must first be issued to ensure none of the current values are null. SQL performs this using a table scan. It also differs from “Not Null to Null” in that any indexes referencing the column must be dropped. Running this statement during peak hours shouldn’t cause much disruption to your users, if there are no referencing indexes.

Increasing Integer Base Values
Increasing size requires an “Immediate DDL Update”. As with changing the size of the Char value a soft delete occurs and a new value is inserted into the end of the fixed portion of the data row. Going from an Int to a BigInt for example adds an additional 8 bytes of data to each row. When determining whether to execute this on a hot system, take into account the size of the row, and how full the page is. If your row is 80 bytes long and you make the change above, it increases the size 10%. If you have a fill factor of 80% and a reindex has recently occurred then you’re probably alright to run on a hot system. If your row size is 12 bytes, adding an additional 8 bytes increase the data size 40% and will result in page splitting and allocation.

Decreasing Integer Values
Of all the DDL changes this is the most interesting. This is a “Delayed DDL Update”, that occurs in meta-data. When the statement is executed SQL Server first checks through a table scan that the existing data will fit within the new datatype. After the validation then the old data is used, that is there is no moving of data, as seen in other statements. During the next reindex the extra space will be recovered. As with many of these changes any indexes referenceing this column must be dropped, prior to running the command. If you don’t have any referencing indexes here, then it’s a safe command to run.



Converting Datatypes with a different Base Type
This is an “Immediate DDL Update”, and follows the IO intensive process of many of the above examples, where a soft delete occurs on the original field and a new one is created. If you change your data for example on an int to a decimal(10,5), additional 10 bytes is allocated to each row. This type of command should be executed during off hours.

Conclusion
This is a simple list of possible changes you might come across, and should be used as a cheat sheet. There are several more scenarios which can run many more pages. The key with any change is to test prior to execution.

SQL Server Performance Impact of Modifying Columns through Alter Table

Introduction
Recently we had an issue where a value of null was being handled incorrectly by our front end code. After populating the null values, the question was, should we make this column “not null”, to avoid this issue in the future. That would’ve been most appropriate during design, but if we made the change now, it would affect the live database during the DDL execution. Running an “Alter Table” statement to change this field to “Not Null” would’ve taken three minutes. Before moving ahead with this change we needed to fully understand what the effect on our users would be during this modification. The following is the first in a two part article which discusses user effects of DDL changes to data structures. All examples use the stock AdventureWorks database in 2005.

Schema Locks VS Table Locks
When a DDL statement is executed against a table, a schema lock is issued against the entire table. There are two types of schema locks; Modification (Sch-M) and stability (Sch-S). Stability locks are more relevant to referencing objects such as Stored Procedures, Views, Indexes, etc, while modification locks are for data structure changes. Being that we’re talking about “Alter Table” statements the latter is of concern. Most people including myself will often compare a schema modification lock to an exclusive table lock. In most conversations this description is fairly accurate in that data modifications to any part of the table wait until the lock is release, as do select statements in read committed isolation mode. However one key difference is table locks allow for read uncommitted selects, which are denied under a schema modification lock. Although scolded by many DBA’s dirty reads are highly beneficial in high transactional systems. It allows users the ability to retrieve data during peak modification times, without waiting, for DML statements to complete. This is not the case when a schema lock is held, a dirty read needs to wait until the lock is released. If you try to sidestep the concurrency issue using snapshot isolation, try again this doesn’t work either.

If want to look at how this works, open two tabs in SSMS. In the first session (tab) execute the below DDL statement against the Title column in the Employees table of the Adventureworks database.

Begin Tran alter table HumanResources.Employee Alter column Title Char(50)

In your other session ( 2nd tab) attempt to read with a dirty read. Notice no data is returned instead your session is waiting on a schema lock.

select top 10 * from HumanResources.Employee with (nolock)

Had the first session simply been a DML statement using a table lock such as the one below, you would have been able to access the data with a dirty read. To prove this out rollback the first statement and run a table lock dml statement.

rollback tran
Begin Tran Update HumanResources.Employee with (tablock) Set Title = 'Test'

Now rerun your select. Notice you can retrieve data now.

select top 10 * from HumanResources.Employee with (nolock)

Schema Locks and Child Relationships
A significant point with schema modification locks is that they cascade on relationships. Any child table will experience a schema lock if its parent is having a DDL statement run against it. For example the Employee table is the parent to the JobCandidate table on the Employeeid field. If a schema modification is made against the Employee table the JobCanidate is also locked. In fact all the child tables of Employee will not allow access even if the query doesn’t reference the employee table. One would expect that this is limited to changes against the field in which the two entities are related such as the Employeeid field however it’s any change against the parent table.

To demonstrate reuse the previous example. In the first session rollback your transaction if it was open, and issue the first command. Then look at your locks by quering the DMV dm_tran_locks in the same session.

Begin Tran alter table HumanResources.Employee Alter column Title Char(50)
go
select o.name, l.* from sys.dm_tran_locks l with (nolock) join sys.sysobjects o with (nolock) on l.resource_associated_entity_id = o.id where resource_database_id = db_id() and request_mode = 'Sch-M'

Notice all child tables and the relation objects are locked. If you try running any selects against these objects while the DDL transaction is active you won’t be able to access the data. The query below is one that a customer service might use. It has nothing to do with the employee table but the alter statement above would block it. Make sure this is executing in a different session.

select PH.status,ph.ShipDate, ph.TotalDue,pd.Productid, Orderqty from Purchasing.PurchaseOrderHeader ph
inner join Purchasing.PurchaseOrderDetail pd on pd.purchaseorderid = ph.purchaseorderid
where ph.purchaseorderid = 5

Plan Recompilation
Locking is an easy target to attack since it has significant impact on a system. However we need to also consider the cost of recompiling. Any SP or SQL statement in cache that references the object where the DDL statement is performed will be recompiled, with statement completion. Anytime a recompilation occurs there is a wait to rebuild the plan, and the chance you might get bit with “parameter sniffing”. However unlike the schema lock that cascades from parent to child, plans that depend on child tables are not recompiled. So the above customer service query would be free from recompilation. In the large picture recompilations are more of an annoyance then a credible concern to running a DDL statement.

Conclusion
The best solution is to design your table correct the first time, but we should save that verbiage for consultants who don’t have to hear businesses ever changing requirements. The reality is these situations will always come up, and you need to understand the effects of your change so that you can plan out the proper execution and timing. One key to remember is all DDL statements are not created equally some can take quite a bit longer then others. In Part II I’ll discuss different types of DDL statements against tables and the affects they have on your system.

SSIS error using mysql and ado

I just ran into an interesting problem in SSIS while using the mysql 5.1.6 ADO.net connector. I had a package that connected to mysql using ado.net ran a command, then a couple of other sql tasks, and finally came back and ran another mysql ado.net command. When working in Visual studio I had no problems all green, but when I went to schedule it out through a job it failed, with the following message.

Executed as user: fakeuser. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:23:26 AM Error: 2008-10-23 11:23:27.12 Code: 0xC001000E Source: MYSQL SSIS Description: The connection "Mail Connection" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 11:23:26 AM Finished: 11:23:27 AM Elapsed: 0.937 seconds. The return value was unknown. The process exit code was -532459699. The step failed.

The mail connection in the message is a red herring. The issue seems to be a memory allocation error by SSIS. The problem was each task opened a second instance of the mysql ado.net frame work and blew it up. Why this only happened when called externally and not within visual studio I don’t have the answer.

The work around was in the properties of the mysql ado.net connection I set RetainSameConnection to true, so that a second connection would not be spawned and step on the first

Bulk Load Permisions in SQL Server

Managing permissions is alway a tight wire act. Recently after cleaning up some permissions on an investigation box I had a developer emailed me this error.


Msg 4834, Level 16, State 4, Line 7
You do not have permission to use the bulk load statement


This is a pretty easy fix just add the user to the bulkadmin server role