Blogger Jeff = new Blogger

Programming and stuff in Western PA

Sql Server 2008 CTP

You can download it from here.

I’ll have to download this and take a spin.

Two things I’ve read about that sound neat off the bat :

(1) Automatic auditing : one of my recent posts detailed how to do this. As I said, I end up doing this on practically every project, so it’s nice Microsoft is baking this right in.

(2) Date only and Time only datatypes : HALLELUJAH! I can’t tell you how many times this has screwed me when doing date comparison, and all I’m concerned with is a date, but the time element throws it off.

Advertisements

March 13, 2008 Posted by | SQL Server, Technology | Leave a comment

SQL Server Audit tables the easy way

One of the more common tasks I perform from project to project is the creation of audit tables. The easiest way I know how to do this is to create a copy of the table I want to audit, and then add a trigger to the parent table. For my example I’ll use my ever-so-popular MLB database and my TEAMS table. The schema for TEAMS looks like:

CREATE TABLE [dbo].[TEAMS](
    [TEAM_ID] [int] IDENTITY(1,1) NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL
) ON [PRIMARY]

GO

The audit table for TEAMS will be an exact copy, with a couple of modifications. First, TEAM_ID needs to be a copy, so it’s no longer an identity column, The second change I made was to add an AUDIT_DT column to capture when the change occurred :

CREATE TABLE [dbo].[TEAMS_AUDIT](
    [TEAM_ID] [int] NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL,
    [AUDIT_DT] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

I then add a trigger to the TEAMS table to capture updates and deletes (I only want to capture when the original column changes, not when it’s added) :

CREATE TRIGGER TRG_TEAMS 
   ON  TEAMS 
   AFTER UPDATE,DELETE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [MLB].[dbo].[TEAMS_AUDIT]
           ([TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE])
     SELECT [TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE]
    FROM DELETED
END
GO

That’s it! A couple of things you may want to consider when creating an audit table is :

  • Remember that the audit table gets an insert for each update/delete to the parent table. This can have some performance ramifications. One of the things I do is not put any indexes on the audit table.
  • You’ll want to purge the data from this table from time to time (or back it up).



Digg!

Tags:

March 2, 2008 Posted by | SQL Server, Technology | Leave a comment

Cascading deletes in SQL Server

I get asked from time to time if SQL Server supports cascading deletes, and the answer is Yes. SQL Server does so via foreign key constraints with the DELETE CASCADE flag set. In the following example, after creating the objects and inserting some data, we delete a USR_ID from the parent data. After querying the child table (USER_PHONE) a second time, we can see that the cascading delete worked :

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)

CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD  
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USER_PHONE
DELETE USERS WHERE USR_ID=2
SELECT * FROM USER_PHONE

DROP TABLE USER_PHONE
DROP TABLE USERS

Tags:

January 28, 2008 Posted by | SQL Server, Technology | 1 Comment

Sql Server 2005 ROW_NUMBER function

The ROW_NUMBER function is a neat little enhancement that makes a fairly common task in Sql pretty easy : generating a row number for a result set. You’ve always been able to do this Sql, but had to do something like the following :

CREATE TABLE #temp
(
	RowNumber int identity
	,LastName varchar(100)
	,HireDate datetime
)

INSERT INTO #temp(LastName
	,HireDate)
SELECT LastName
	,HireDate
FROM dbo.Employees
ORDER BY HireDate DESC,LastName DESC

SELECT * FROM #temp

Results :

Result Set 1

Why would you want to return a unique row number in a result set? A lot of times it’s for more complex comparisons, or maybe for a paging function.

The ROW_NUMBER function makes this step easier :

SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate
FROM dbo.Employees

Results :

Result Set 2

All we’re basically doing here is telling the result set how the RowNumber will be assigned. This is doing the same thing as our first example.

However, the ROW_NUMBER function has a PARTITION attribute we can set. What this does is first group our data by one or more columns, and then assign the RowNumber column. For instance in our example above, we could partition the data on TitleOfCourtesy(Mr.,Mrs.,etc). What this means is for each unique instance of TitleOfCourtesy, we assign a RowNumber. So our Sql looks like this :

SELECT ROW_NUMBER() OVER(PARTITION BY TitleOfCourtesy 
			ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate,TitleOfCourtesy
FROM dbo.Employees
ORDER BY ROWNUMBER

Which gives us this result set:

Result Set 3

Tags:

January 25, 2008 Posted by | SQL Server, Technology | 4 Comments

SQL Server 2005 Productivity tips

(1)  Synonyms
Tired of typing "SELECT ID FROM TBL_THELONGESTTABLENAMEINHISTORY" ? Then create a synonym and make life easier for yourself.

CREATE SYNONYM [dbo].[mininame]
FOR [NorthWind].[dbo].[TBL_THELONGESTTABLENAMEINHISTORY]

Now to access that mama-jamma, your query is whittled down to :

SELECT ID FROM mini


(2) Use those Templates young man!
SQL Management Studio supplies you with a wealth of pre-defined templates. Want the basic syntax for a cursor, or need the full create syntax for a table constraint?  Just open up the Templates window and drag it onto your SQL query window. 

But if you’re like me, you may have to run the same code a number of times, and templates are a wonderful place to store stuff like that.  All you have to do is right click on the SQL Server Template folder in the Template Explorer and choose to add a new template.  For my personal templates, I even created my own folder to store them in:

You can even add your own parameters to templates, and be prompted for the values at design time.  Parameters are enclosed in less than/greater than signs, take the form of <Parameter Name,Type,Value> :

IF EXISTS (

 SELECT * 

 FROM INFORMATION_SCHEMA.ROUTINES 

 WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'

 AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>' 

)

 DROP PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>

GO



CREATE PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>

 <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 

 <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>

AS

 SELECT @p1, @p2

GO

After saving your template, you can then add it to an existing script in a query window, and then click Ctrl-Shift-M to bring up a dialogue box for you to fill in your parameter values :

(3) Metadata views

I recently had to dive into a huge (50 gig) database.  I had to generate some rather large stored procedures that would delete a common key from a lot of tables. To learn the database, I used the information schema views a lot.  I was able to determine if a column existed in all the tables by querying the COLUMNS and TABLES views. I even modified the Sql to generate the delete statements for me, like this:

SELECT 'DELETE ' + a.TABLE_NAME + ' WHERE ProductID = @ProductID'
FROM information_schema.columns a
INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.table_name=b.table_name
WHERE column_name LIKE '%ProductID%'
AND table_type='BASE TABLE'
ORDER BY b.table_name

-------------------
RESULTS
-------------------
>>DELETE Order Details WHERE ProductID = @ProductID
>>DELETE Products WHERE ProductID = @ProductID

What I had to do when I was generating this for 50 tables was run the sql and see if any constraint errors were thrown.  Most likely, I just had to change the order of the delete statements.

(4) Use Sql Profiler to see the Sql your app runs

SQL profiler is best known for troubleshooting performance issues in SQL Server.  But the thing I usually use it for is to see exactly what sql gets run by an application.  Curious to see what happen when you use the neat little WYSIWYG editor in Crystal Reports to query the database?  Run a quick profiler trace.  Want to see what tables are getting accessed by your application, run a profiler trace. The nice thing about getting to the trace data is that you can also copy and paste it directly into SQL Management Studio, or into a separate stored procedure.  Which brings us to our next tip….

(5) Debugging stored procedures

In SQL Server 2005, the only way you can step into a stored procedure is if you create a Visual Studio.Net 2005 Database Project. If you right-click on the stored procedure, and then select step into, you’ll also be prompted to fill in any needed parameter values.  This can be a real pain in the hind quarters if there are a lot of parameters. What I end up doing is saving the sql that gets executed into a separate, temporary stored procedure, and then stepping into that procedure.

Tags:

January 14, 2008 Posted by | .Net, SQL Server | 3 Comments

SQL Server 2005 : Updating multiple tables involved in a two column foreign key constraint

I ran in to a fun problem the other day where the database I was working on had a table using two columns for a primary key constraint. This primary key was then involved in a number of foreign key constraints. It looked something like this :

My client then came to me and requested a stored proc that would update one of those columns with a new value. Their Database Administrator insisted that this was no big deal, as simple update statements would do the trick. I of course told them otherwise, because updating one value would blow the constraint in the other tables (and there were like 20 child tables to update). I came up with three different ways to do this :

(1) Disable the constraints. This can be done by altering the constraint to not check the values being inserted :

After running the DBCC command, one row would get returned showing the bad data in the table. Needless to say, I decided to avoid this route in my stored proc.

(2) The next thing I came up with was to do the following :

  • insert data from child tables into a temp table
  • delete data from child tables
  • update parent data with new column value
  • update temp tables with new column value
  • insert temp data back into child tables

Certain problems arise with this solution. If our table schema was more complicated, we might have to do stuff like allow for the insertion of identity values. When this occurs, the insert list has to include all column names, which means any subsequent updates to the table schema would also involve an update to the procedure I’d be writing to include the new columns.

I actually started down this path, but then a simpler option came to me …..

(3) Insert a new row for the Parent, update the Child to the new value of the Parent, and then delete the original Parent. It would go something like this :

This ended up being the solution I went with, as it was the simplest to implement.

Tags:

November 27, 2007 Posted by | SQL Server, Technology | Leave a comment