Blogger Jeff = new Blogger

Programming and stuff in Western PA

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:

Advertisements

January 14, 2008 - Posted by | .Net, SQL Server

3 Comments »

  1. Thank you so much. This is what I have been looking for.

    Comment by Andrew Lackman | January 26, 2008 | Reply

  2. Jeff, How do you get your “Jeff’s Templates” folder sorted at the top of the folder list?

    Comment by Mike Christopher | December 5, 2008 | Reply

  3. Getting your template folder to the top of the list: http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5529e8e4-8d7b-40f5-b77a-e1a11f583aea

    Comment by Russell Fields | January 13, 2012 | Reply


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: