Q. I want to use a Primary key constraint to uniquely identify all employees, but I also want to check that the values entered are in a particular range, and are all non-negative. How can I set up my constraint to do this?

A.  It’s a simple matter of setting up two constraints on the employee id column: the primary key constraint to guarantee unique values, and a check constraint to set up the rule for a valid, non-negative range; for example:

Create Table Employee ( Emp_Id Integer Not Null Constraint PkClustered Primary Key
Constraint ChkEmployeeId Check ( Emp_Id Between 1 And 1000) )

Or, if you want to create table-level constraints (where more than one column defines the primary key):

Create Table Employee ( Emp_Id Int Not Null , Emp_Name VarChar(30) Not Null,
Constraint PkClustered Primary Key (Emp_Id),
Constraint ChkEmployeeId Check ( Emp_Id Between 0 And 1000) )

Q. What’s a quick way of outputting a script to delete all triggers in a database (I don’t want to automatically delete, just review the scripts, and selectively delete based on the trigger name)?

A. There are probably a number of ways, but this may be a job for a cursor. Try the following Transact SQL script:

Declare @Name VarChar (50)
Declare Cursor_ScriptTriggerDrop Cursor
For Select name from sysObjects where type = 'Tr'

Open Cursor_ScriptTriggerDrop
Fetch Next From Cursor_ScriptTriggerDrop Into @Name
Print 'If Exists (Select name From sysObjects Where name = ' + Char(39) + @Name +
Char(39)+ ' and type = ' + Char(39) + 'Tr' + Char(39) + ')'
Print 'Drop Trigger ' + @Name
Print 'Go'
While @@Fetch_Status = 0
    Fetch Next From Cursor_ScriptTriggerDrop Into @Name
        Begin       
                Print 'If Exists (Select name From sysObjects Where name = ' + Char(39)
+ @Name + Char(39) + ' and type = ' + Char(39) + 'Tr' + Char(39) + ')'
                Print 'Drop Trigger ' + @Name
                Print 'Go'
        End
Close Cursor_ScriptTriggerDrop
DeAllocate Cursor_ScriptTriggerDrop

Q. Is there a function I can use to format dates on the fly as they’re added to a resultset? I need to support a large range of formatted outputs.

A. Take a deep breath and build it yourself. Here’s a useful function to give you sophisticated formatting abilities:

Use Pubs
Go
If Object_Id ( 'fn_FormatDate' , 'Fn' ) Is Not Null Drop Function fn_FormatDate
Go
Create Function fn_FormatDate
     (
      @Date   DateTime               --Date value to be formatted
     ,@Format VarChar(40)     --Format to apply
     )
Returns VarChar(40)
As
Begin

     -- Insert Day:
     Set @Format = Replace (@Format , 'DDDD' , DateName(Weekday , @Date))
     Set @Format = Replace (@Format , 'DDD' ,
    Convert(Char(3),DateName(Weekday , @Date)))
     Set @Format = Replace (@Format , 'DD' , Right(Convert(Char(6) , @Date,12) ,
    2))
     Set @Format = Replace (@Format , 'D1' , Convert(VarChar(2) , Convert(Integer
    , Right(Convert(Char(6) , @Date , 12) , 2))))

     -- Insert Month:
     Set @Format = Replace (@Format , 'MMMM', DateName(Month , @Date))
     Set @Format = Replace (@Format , 'MMM', Convert(Char(3) , DateName(Month
    , @Date)))
     Set @Format = Replace (@Format , 'MM',Right(Convert(Char(4) , @Date,12),2))
     Set @Format = Replace (@Format , 'M1',Convert(VarChar(2) , Convert(Integer ,
    Right(Convert(Char(4) , @Date,12),2))))

     -- Insert the Year:
     Set @Format = Replace (@Format,'YYYY' , Convert(Char(4) , @Date , 112))
     Set @Format = Replace (@Format,'YY' , Convert(Char(2) , @Date , 12))

     -- Return function value:
     Return @Format
End
Go

        -- Examples:

Set NoCount On

     Select dbo.FormatDate(Ord_Date,'dddd, mmmm d1, yyyy') From Pubs..Sales
    Where stor_id = 6380 AND ord_num = '6871'
     Select dbo.FormatDate(Ord_Date,'mm/dd/yyyy') From Pubs..Sales Where stor_id
    = 6380 AND ord_num = '6871'
     Select dbo.FormatDate(Ord_Date,'mm-dd-yyyy') From Pubs..Sales Where stor_id
    = 6380 AND ord_num = '6871'
     Select dbo.FormatDate(Ord_Date,'yyyymmdd')  From Pubs..Sales Where stor_id
    = 6380 AND ord_num = '6871'
     Select dbo.FormatDate(Ord_Date,'mmm-yyyy')  From Pubs..Sales Where stor_id
    = 6380 AND ord_num = '6871'

Set NoCount Off

Q. If SQL Server does on-the-fly caching and parameterisation of ordinary SQL statements do I need to build libraries of stored procedures with input parameters that simply wrap the SQL statement itself?

A. I’m sure this question has come up before, relating to SQL Server 7, but it remains a good question. The answer’s ‘yes and no’ (though more yes than no). It’s true that both SQL Server 7 and 2000 do ad hoc caching/ parameterization of SQL statements, but there are a couple of ‘gotchas’ attached. First of all the following two statements will generate an executable plan in SQL Server’s cache with a usage count of two (i.e. the plan is put in cache and reused by the second statement):

Select Title From Pubs.Dbo.Titles Where Price = £20
Select Title From Pubs.Dbo.Titles Where Price = £7.99

However if the currency indicator is dropped, one plan is marked with an Integer parameter, the other with a Numeric parameter (as each is auto-parameterized). Worse still, if the case of any part of the column name(s), database, owner, or object name(s) changes, in a following SQL statement, SQL Server fails to recognize the structural identity of the next statement and generates another plan in cache.

To check this out, run both statements (with currency marker and identical case) along with the following call:

Select CacheObjType , UseCounts , Sql From Master..sysCacheObjects

(If possible run Dbcc FreeProcCache before the three SQL statements, but beware: it drops all objects in cache).

You should find that the executable plan has been found and reused (the UseCounts value will be ‘2’). Now, either drop the currency indicators or change the case in one of the ‘Select Title … ‘ statements and note that a fresh executable (and compiled) plan  is generated for the second statement.

Now create a stored procedure with Price as an input parameter, run it with or without currency markers, changing the case of the call at will, and the procedure will be found and reused – check the UseCounts column value.

Q. How can I get a quick list of all the options set for a particular session?

A. Here’s a stored procedure which will give you a list of all options set in a session. If you create it in Master, with the ‘sp_’ prefix it’ll be available within any database:

Create Procedure sp_GetDBOptions
As
Set NoCount On
/* Create temporary table to hold values   */
Create Table #Options
(OptId Integer Not Null , Options_Set VarChar(25) Not Null )

If @@Error <> 0
Begin
            Raiserror('Failed to create temporary table #Options',16,1)
            Return(@@Error)
End

/* Insert values into the Temporary table   */
Insert Into #Options Values (0,'NO OPTIONS SET')
Insert Into #Options Values (1,'DISABLE_DEF_CNST_CHK')
Insert Into #Options Values (2,'IMPLICIT_TRANSACTIONS')
Insert Into #Options Values (4,'CURSOR_CLOSE_ON_COMMIT')
Insert Into #Options Values (8,'ANSI_WARNINGS')
Insert Into #Options Values (16,'ANSI_PADDING')
Insert Into #Options Values (32,'ANSI_NULLS')
Insert Into #Options Values (64,'ARITHABORT')
Insert Into #Options Values (128,'ARITHIGNORE')
Insert Into #Options Values (256,'QUOTED_IDENTIFIER')
Insert Into #Options Values (512,'NOCOUNT')
Insert Into #Options Values (1024,'ANSI_NULL_DFLT_ON')
Insert Into #Options Values (2048,'ANSI_NULL_DFLT_OFF')

If @@Options <> 0
      Select Options_Set
      From #Options
      Where (OptId & @@Options) > 0
Else
      Select Options_Set
      From #Options
      Where Optid = 0

Set NoCount Off

Q. Is it possible to get a list of all of the system tables and views that are in Master only?

A. Perfectly easy, I’ll even order the output by type and name:

Select type, name From Master..sysObjects Where Type In ('S', 'V')
AND name Not In (Select name From Model..sysObjects) order by type, name

Q. Is there an easy way to get a list of all databases a particular login can access?

A. Yes, there’s an undocumented procedure called sp_MsHasDbAccess which gives precisely the information you want. Use the SetUser command to impersonate a user (SetUser loginname) and run sp_MsHasDbAccess with no parameters. To revert to your default status, run SetUser with no parameters.

Other recommended sources for Microsoft SQL FAQs:

http://support.microsoft.com/support/default.asp?PR=sql&FR=0&SD=MSDN&LN=EN-US (for FAQ and Highlights for SQL)

http://support.microsoft.com/view/dev.asp?id=hl&pg=sql.asp for Microsoft technical advice)

http://support.microsoft.com/support/sql/70faq.asp (UK based)

http://www.mssqlserver.com/faq/ (an independent support company)

http://www.sqlteam.com/ (a good source for answers to SQL questions)

http://www.sql-server-performance.com/default.asp (for SQL performance issues)

http://www.sql-server.co.uk/frmMain.asp (UK SQL user group - need to do free registration)

http://www.advisor.com/wHome.nsf/w/MMB (a useful US based VB/SQL Magazine)

http://www.advisor.com/wHome.nsf/w/JMSS (a useful US based SQL Magazine)

http://secure.duke.com/nt/sqlmag/newsub.cfm?LSV=NunLjpaxugA-hA9hauyikjkarnlkTKgEAQ&Program=9 (another useful SQL magazine)

The public newsgroups starting microsoft.public.sqlserver are also useful sources for help with individual problems.