back to t-sql basics (2 of 2)

11 Mar

So you want to make changes to the database? Well that has all sorts of implications. My experience with database change is only with small applications that I have written myself. The other experience I have is troubleshooting a few ISV applications that require me to delete or update erroneous records.

This lack of experience has driven me. It should be a requirement of anyone administering SQL to know SQL. The DMVs are great way for a sysadmin to get started querying. However, you don’t write to these objects. So the first time you need to preform an update or delete there’s a bit of voodoo involved.

What I have started doing is collecting useful information using some of Allen White’s DIY baseline and monitoring powershell scripts. ( This has started giving me at least a cursory knowledge of IUDs by loading this data into other tables that I need to maintain. Also, cleaning up data that is old is a must. But again this small-time development is mostly a refresher for me.

I’ve gotten great experience at the enterprise sysadmin role but I seriously lack the production development DBAs skills. I hope I have a chance to learn about schema changes on busy databases before I have to make any. Also, I would like to be able to offer more assistance for the database design problems. For now, I don’t stay awake at night worrying about a missing where clause on a delete or an improperly nested update query but maybe someday it will.

There are two remaining chapters on concurrency and programming that I might be covering with some notes later. This book has my highest recommendation. Without further ado, I have taken these notes on my reading of: Microsoft SQL Server 2008 T-SQL Fundamentals by Itzik Ben-Gan.

(1, p238)
Insert into values has changed in 2008. You can now include multiple values (1,1,1),(2,2,2),(3,3,3).

Insert into select allows you to insert records from one result set into a table. This is called table value constructor.

Insert exec works like insert select but allows you to insert the results of a sp.
(1, p241)
Select into is non-standard sql. It allows for table creation on the fly.

Bulk insert is a very fast method of loading from a flat file.
(1, p242)

Bulk insert from ‘c:\inputfile.csv’ with
(fieldterminator = ‘,’,
Rowterminator = ‘\n’)

Identity allows for auto incrementing a field in a table. Do not specify this column on the insert and based on the seed and increment the value will increase. IDENTITY(1,1) means start at 1 and increment by 1. This is not the same as a sequence.

SCOPE_IDENTITY() will return the last identity used within the current scope. Identities will increment on failed inserts. Deleted records will create gaps in identities. Ident_current() will ignore session and give you the last identity.

(1, p247)
SQL only has two commands for deleting data, delete and the non-standard truncate. Deleting whole objects such as tables can be done with the DROP command.
Deletes based on a join are non-standard, use deletes with subqueries instead.
2008 allows for += where previous versions don’t allow those compound operators
Sql does all at once operations. Consider col1 = 20 and col2 = 10

Update testable
Set col1 = col2, col2 = col1;

This query would swap col1 and col2 where in most languages the values would end up being the same.
Just like deletes, updates can be based on a join but it is non-standard. Use subqueries

Update testable
set col1 += .05
where exists
(select * from testtable2 as tt2
where tt2.col1 = testable.col1
and id = 1)

(1, p254)
Assignment Update = new to allow creating your own sequences
MERGE; allows for IUDs with logic. Its also new to 2008 and is mostly ansi standard.

(1, p259)
SQL allows the use of CTEs to modify data… whoa…
-can be useful to select data before modifying it
-can use a table expression to select TOP and orderby… then run your update

(1, p266)

Delete from testable
where col1 < ‘20010101’

This will return col1 and col2 for all the records that were deleted.

1. Ben-Gan, I. (2009). Microsoft SQL Server 2008 T-SQL Fundamentals. Redmond, Washington: Microsoft Press.

Leave a comment

Posted by on March 11, 2012 in SQL Dev


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: