Practical Programming

If you have done any signficant amount of programming in SQL you will have learned, with time, that nearly everything you can do in SQL Manager/Studio is 100% scriptable. Taking advantage of this can be a huge help with automating tasks… but you don’t need to bury your head in SQL Syntax to do so. Take the time to check out the wizards in studio, the context menus, etc… and you will almost always see a button or option to “generate script”. Get used to taking advantage of this because it will save you a multitude of headaches!

Here is a quick tip for restoring a table from a backup in script:

You cannot restore a database while it has active connections… to get around this you will need to set the database up in single_user mode (which deactivates all other connections except for your own). You will also need appropriate privelages to execute the commands like drop, restore, sp_adduser, and so on… db_creator roles are a simple solution for setting access priveleges but make sure you adequately protect the account as it opens up your database to lots of malicious activity if it were to fall in the wrong hands.

Here is a example to get you started down the right track:

--disconnect any one else so you can do the restore
ALTER DATABASE {your database}
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE

USE MASTER 

--delete the old database (you could overwrite it instead
---if you wanted, merge, etc.)
DROP DATABASE {your database}

--restore the database from the backup
RESTORE DATABASE [{your database}]
   FROM  DISK = N'{insert full path to ur backup file here}' WITH  FILE = 1,
   MOVE N'{your backupset}' TO N'{insert your path and db name here}.mdf',
   MOVE N'{your backupset}_log' TO N'{insert your path and db name here}.ldf',
   NOUNLOAD,
   RECOVERY,
   REPLACE,
   STATS = 10

--grant privileges as necessary
use {your database}
exec sp_adduser '{user account}', '{user name}', 'db_datareader'

--reset the connection mode back to multi_user (this is important)
ALTER DATABASE {your database}
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE

MSDN Ref:
   1. Restore (Transact-SQL)
   2. sp_addUser (Transact-SQL)
   3. Drop Table (Transact-SQL)
   4. Alter Database (Transact-SQL)

Quick Tips sql

Microsoft’s excellent implementation of XML in SQL 2005 allows you to insert multiple rows with only one insert statement. Here’s how to insert many rows without calling insert many times. In this example we will have three tables in our database. They are: PRODUCTS, PRODUCTS_COLLECTIONS, PRODUCTS_COLLECTIONS_JOIN. PRODUCTS is our main table which contains our products; a product can belong to one or many collections. Therefore, we have PRODUCTS_COLLECTIONS_JOIN to bind a product to a collection via their id’s.

Pros vs. Cons

1. PRO - Only need to connect to your database once, to execute the procedure.
2. PRO - We are using XML support built into SQL 2005.
3. PRO - Keep things neat. There is only one procedure to manage which joins these tables.
4. CON - You are passing more data with the one connection (the XML). Whether this matters to you will depend on many factors dependent on your setup.

How?

SQL 2005 supports xPath strings, go here for a quick tutorial. The first statement in this procedure deletes all joins in PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id and the collection_id is NOT found in the XML passed. Next the procedure inserts into PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id, and where a collection_id is in the XML, and where a join with this product_id and collection_id does not already exist.

create procedure [spProductsCollectionsJoin] @id int, @collections xml
as
declare @deleted bit
set @deleted = 0
delete from [products_collections_join]
where
	[product_id] = @id
	and [collection_id] not in(
            select x.item.value('@id','int') as id
            from @collections.nodes('/collections/item') as x(item))
insert into [products_collections_join]([product_id],[collection_id])
	select
		[products].[id],
		[products_collections].[id]
	from [products]
	inner join [products_collections] on [products_collections].[id] in (
                   select x.item.value('@id','int') as id
                   from @collections.nodes('/collections/item') as x(item))
	left join [products_collections_join] on
                  ([products_collections_join].[collection_id] =
                   [products_collections].[id]
                   and [products_collections_join].[product_id] = @id)
	where
		[products].[id] = @id
		and [products_collections_join].[collection_id] is null
		and [products_collections].[deleted] = @deleted

To execute the join we need to create our XML to pass to the procedure:

declare @product_id int
declare @collections xml
set @product_id = 1
set @collections = '
<collections>
<item id="1"/>
<item id="2"/>
<item id="3"/>
</collections>
'
exec spProductsCollectionsJoin @product_id, @collections

After executing the procedure there will be three new rows in PRODUCTS_COLLECTIONS_JOIN which correspond to the 3 new item nodes in the XML.

Further Reading

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

http://www.w3.org/TR/xpath

http://www.zvon.org/xxl/XPathTutorial/Output/examples.html

Source

SQL Script to create products table.
SQL Script to create products_collections table.
SQL Script to create products_collections_join table.
SQL Script to create spProductsCollectionsJoin procedure.
SQL Script to test everything.

Until next time, dream in digital.

Advanced Stuff, sql sql, xml

Users of SQL Server 2005 already know DTS Packages have been set aside for the newer, more functional, SSIS (SQL Server Integration Services). But if you are anything like myself, you have dozens of old DTS Packages and no desire to upgrade them. Send to the rescue The Microsoft SQL Server 2005 Feature Pack; you will want to Download the Microsoft SQL Server 2000 DTS Designer Components add-on. 

X86 Package (SQLServer2005_DTS.msi) - 5088 KB

Note:
The complete package is available here.

Quick Tips sql

Why?

When a query is passed to Microsoft SQL Server, a number of things have to happen in order for you to get a result set back. First, before anything can be done your SQL Statement is compiled by the Query Optimizer which creates an Execution Plan based on statistical data and things like indexes. Essentially it is making its best attempt to create a procedure that is as highly optimized as possible and can return the results your’re looking for.

Once this Execution Plan is created it is then cached on the server so that the next time a query with the same parameters and structure is called it can simply run that existing plan instead of having to build a new one which can be, and is often, processor and i/o intensive; creating undo overhead.

Using cached execution plans is considered a best-practice because it allows your queries to execute much faster (since the plan doesnt have to be constantly rebuilt/compiled) and as a side effect because you are passing data to typed values (int, varchar, etc.) you avoid any possibilities of SQL Injection Attacks.

How?

• used stored procedures
• use cfqueryparam for all values in your queries (even constants)
   

Note:

This is applicable to all languages and application types: web, desktop, cli… wether it be in cfml, c#, or c++, SQL essentially functions the same way with the exception of the driver being utilized. If you aren’t sure your execution plans are being cached take the time to look up the correct way to do it. You will be glad you did.

Quick Tips sql

From comment: 12/22/09

I discovered later on, this is actually a very BAD implementation. The reason is when using indexes, executing a function on each item requires an entire index scan to be done instead of an index seek. This completely screws up doing a range search for dates on a clustered index. Yikes.

The methods above work, but boy or they ever inefficient. Avoid them if at all possible. Consider using things like DateDiff(), etc.

And Remember, when your writing high performance queries always study the execution plan and reduce bottlenecks!

Why?

Sooner or later you will need to do date-only range searches (where you are not concerned with times). Many programmers will actually specify a start time of 12:00 AM and ending time of 11:59:59 PM to get around this. But if you write a lot of queries you will quickly find this to be an annoyance and without an appropriate solution it can be easy to forget about times all together; creating a hard to find bug that will crop up and have your users up in arms.

How?

Since datetimes are stored as binary values in SQL we can utilize this to our advantage by casting the datetime object to a decimal and passing that result to the floor() function to isolate the date and effectively chop off the time.

declare @startdate datetime
declare @enddate datetime

set @startdate = '01/01/2008'
set @enddate = '03/01/2008'

select
	fname + ' ' + lname as emp_name,
	convert(varchar(10), hire_date, 101) as hire_date,
	hire_date as og_hire_date,
	floor(cast(hire_date as decimal(9,4))) as og_hire_date_cast

from employees

where
     floor(cast(hire_date as decimal(9,4))) between @startdate and @enddate

Note:

Conversely if we only wanted the time, we are concerned with what is on the right side of the decimal. Using the function makes this a cinch. For more information on how SQL stores datetimes internally, check out Date and Time (Transact-SQL) on MSDN

SQL Function, MSDN Ref.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
CAST ( expression AS data_type [ (length ) ])
FLOOR ( numeric_expression )

Quick Tips sql

Why?

Formatting datetimes directly in your query is significantly faster than using dateFormat() and timeFormat() in ColdFusion (and most other languages) and simplifies your query output by having formatting already completed.

How?

Formatting datetimes is incredibly simple. All you need do is call Convert() and specify the format parameter you wish to use. 

SELECT top 1
     convert(varchar(10), birthdate, 101) AS birthdate
FROM employees

Notice the style parameter of 101. This returns the date in US Standard format mm/dd/yyyy. A plethora of style parameters are available including options for times and standard formats of countries outside the US. Check MSDN for a complete listing and more information on the Convert method.

SQL Function, MSDN Ref.
     CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Quick Tips sql