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. (http://sqlblog.com/blogs/allen_white/archive/2012/03/03/load-perfmon-log-data-into-sql-server-with-powershell.aspx) 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.
UPDATING
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)
OUTPUT
Delete from testable OUTPUT deleted.col1, deleted.col2 where col1 < ‘20010101’
This will return col1 and col2 for all the records that were deleted.
Source
1. Ben-Gan, I. (2009). Microsoft SQL Server 2008 T-SQL Fundamentals. Redmond, Washington: Microsoft Press.
