MS SQL Survival Guide - Misc
(SQL 2000)

MS SQL Survival Guide - Home Page

Topic Note
SQL Versions SQL Server Versions
  • select @@version
  • Versions
    • MS SQL Server 2000
      • v8.00.2055 - Dec 16, 2008
    • MS SQL Server 2005
    • MS SQL Server 2008
Help & Debugging MS SQL Help
  • Open up Query Analyzer, click on "Help", then "Transact-SQL Help".
    Help on SQL commands - excellent reference with example SQL code.
    Make this your friend for sure.

Debugging SQL Code

  • Example of Debugging SQL to see how long it takes something to run.  Remember, SQL will cache so you may need to stop/start the "MSSQLService" service to clear out the cache.

    declare @strDebug varchar(255)
    declare @dtDebug_start datetime
    set @dtDebug_start = GetDate()
    set @strDebug = ''
    set @strDebug = @strDebug + convert( varchar(255), GetDate() ) + ' ' + convert( char(2),Datepart(ss, GetDate())) + '.' + convert( char(3),Datepart(ms, GetDate())) + ' - ' + convert(varchar(16), datediff(ss,@dtDebug_start,GetDate()) ) + char(13) + chr(10)
    -- Your SQL debugging code here!
    set @strDebug = @strDebug + convert( varchar(255), GetDate() ) + ' ' + convert( char(2),Datepart(ss, GetDate())) + '.' + convert( char(3),Datepart(ms, GetDate())) + ' - ' + convert(varchar(16), datediff(ss,@dtDebug_start,GetDate()) ) + char(13) + char(10)

    Results:
    Sep 23 2008 10:38AM 3 .413 - 0
    Sep 23 2008 10:38AM 3 .413 - 0
     
SQL Debugger - stored procedures SQL Debugger - stored procedures
  • Open "Query Analyzer".
  • In the Object Browser (F8) to the left find the DB and then expand the section "Stored Procedures".
  • Right click on the stored procedure and choose "Debug".
  • Notes
    • If you step through the SQL you will also notice any Triggers that are firing off!!!

Note:  If you have SQL that you want debugged in the same manner you can create a "Stored Procedure" with the SQL and then debug it.

CREATE PROCEDURE AAADebug
AS
begin
print 'hello'
<place SQL here....>
end

Miscellaneous Topics Miscellaneous Topics
  • Column (field) identifiers - Use the brackets [ ] to create a column (field) name.
  • Comments
    • Multiline - /*   */
      Ex: 
      /*
      This is a example of a
      Mulit-line comment
      */
    • Single Line:  --  (2 dashes)
      ex:  -- This is a comment.
SQL - Query Analyzer EXE. Under Construction
Windows Explorer - setting up a program to run your files with the extension of .sql

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe

Accessing Data Fields - Fully Qualified Names See "SQL Query Analyzer", "Help", "Transact-SQL Help", "Transact-SQL Reference", "Transact-SQL Syntax Conventions".

There are 4 parts in a object name:  server name, database name, owner name, object name.

Examples:
Fully qualified:  server_name.database_name.owner_name.object
(Note: "object" is required.  The other values are needed based on your default values.)

Other options: (Note: brackets is optional.)

  • server_name.[database_name.][owner_name.]object
  • database_name.[owner_name.]object
  • owner_name.object
  • object

Examples

SQL Query Options
SQL Query Options (under construction)

QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL
 

SQL Server Enterprise Manager - Registration SQL Server Enterprise Manager (MS SQL 2000 Dev Ed)
  • New SQL Server Registration - needed with a fresh install of SQL2000.
    Click "Action", "New SQL Server Registration"
    • Choose the SQL Server server.  (If you are not prompted with a list of servers, then you can enter the SQL Server install name manually.)
    • Select "The SQL Server login information that was assigned to me by the system administrator [SQL Server Authentication]
    • Select "Prompt for the SQL Server account information when connecting.
    • Select "Add the SQL Server(s) to an existing SQL Server group".
    • Finish.
  • Note: You may also need to re-register if you install SQL Server 2005 Express etc...
SQL Server - Stop & Start (or Restart) the services. SQL Server - Stop & Start (or Restart) the services.
  • MS SQL server will drastically slow down after some period of process/runtime.  I've seen processes take 3x as long.
    The solution is to do one of the following: reboot the server weekly or stop/start the MS SQL services.
  • OS Scheduled Task
    • Restart SQL Server (SQL Server 2000)
      • Create the following batch file: c:\batch\mssql_server_restart.bat
        rem Stop MS SQL Server & the Job Scheduling Agent
        NET STOP SQLSERVERAGENT
        NET STOP MSSQLSERVER
        rem Start MS SQL Server & the Job Scheduling Agent
        NET START MSSQLSERVER
        NET START SQLSERVERAGENT
      • Create a Scheduled Task to run the batch file. (Win 2000 or XP Pro)
        Control Panel, Administrative Tools, Scheduled Tasks
        Click "Add Scheduled Task"
        Click "Browse", find the batch file, then click "OK"
        Task Name: mssql_server_restart
        Click "Weekly", "Next"
        Choose: your Time, Every 1 Week, your Day, "Next"
        Enter User Name: ibmapp
        Enter Pswd: <pswd>
        "Next"
        Finish
         
SQL Server Enterprise Manager - Create a DB Create a new DB (MSSQL2000)
  • Warning:  Read the warnings at the bottom before doing these steps.
  • Launch "SQL Server Enterprise Manager"
  • Locate the DB Server.
  • Right click "Database", "New Database"
  • On the "General Tab" enter the DB name.
  • You can leave all the other defaults
  • Click "OK".
SQL Server Enterprise Manager - Restore a DB Create a new DB from a .BAK file (MSSQL2000)
 
  • Warnings:  Read the warnings before you start.
    • The DB Access for the users under "Security, Logins" get reset after a DB restore.  So, you need to look at the info before Restoring and then recreate after the restore.  In Enterprise Manager click on the folder "Security", then "Logins".  Make a note of all the users that access the DB.  Then right click on each, select properties, then click on "Database Access".  Click box by the DB and note all of the Roles that are permitted.
    • I've had problems with Restoring a .BAK over a DB and the views from the .BAK don't get updated/added.  So I then delete the DB, create a new one and then do a Restore.  (SP4 corrects this problem!)
  • Launch "SQL Server Enterprise Manager"
  • Locate the DB Server.
  • Click "Database"
  • Right click on the DB you want to restore to, "All Tasks", "Restore Database"
  • Click on the "General Tab"
    • Change/Select the database name (if needed)
    • Restore: select "From Device"
    • Restore backup set: select "Database - complete"
    • Click "Select Devices", then "Add"
    • Click on the "..." icon to locate the .BAK file.
    • Click "OK" until you are back at the screen "Restore Database"
    • Click "Options"
    • "Move to physical file name"
      • The default value comes from when the Backup was executed.
      • If you are restoring to a different DB than the one the .BAK file is for, change the "Move to physical file name" value to match your file environment (Drive letter, directory, and filename).  Make sure you change the Data & Log file name.
        Ex:
        ...\abc_data.mdf change to ...\mydb_data.mdf
        ...\abc_log.ldf change to ...\mydb_log.ldf
    • "Logical file name"
      • Note: Don't change the "Logical file name" value (or you'll get an error).  It needs to match the DB you are restoring from - not the DB you are restoring to if that is different.)
    • Click the box: "Force restore over existing database"
    • Click "OK" and SQL Server will begin the restore.
  • Errors:
    • Error 21002: [SQL-DMO]User 'xxx' already exists.
      To fix this error run the following SQL:
      use <DBName>
      EXEC sp_change_users_login 'Auto_Fix', '<your user name>'
SQL Server Enterprise Manager - Create a backup file (.BAK)

(You can use this file as a Backup or Restore it to another DB to create a copy)
Make a Copy of a DB (MSSQL2000) - This process will also work across servers if you copy the .BAK file to the other server.
  • Launch "SQL Server Enterprise Manager"
  • Locate the DB Server.
  • Click "Database".
  • Right click on the DB you want to copy then "All Tasks", "Backup Database"
  • Use the defaults for the screen "SQL Server Backup ..." except:
    • You may want to check "Overwrite existing media" in case there was already a backup copy.
    • Click "Add" in the Destination section.  Select the backup file name.
      (Common directory:  c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP - then you add the file name.)

 

SQL Server Enterprise Manager - Make a Copy of a DB Make a Copy of a DB (MSSQL2000) - This process will also work across servers if you copy the .BAK file to the other server.
Concept:  Make a Backup file (.BAK) of the DB you want to copy, create the new DB followed by a Restore.
  • Make a Backup (.BAK) file. 
    (See the section: SQL Server Enterprise Manager - Create a backup file)
  • Create a DB via SQL Server Enterprise Manager
    (See the section: SQL Server Enterprise Manager - Create a DB )
  • Restore the Backup (.BAK) to the DB you created.
    (See the section: SQL Server Enterprise Manager - Restore a DB)
  • Don't forget to create the neccessary DB Logins (Security, Logins).
  • Don't forget any ODBC connections if needed to interface with an Application.
Scheduling a "Backup". Schedule an automatic creation of a Backup (.BAK) file (MSSQL2000)
  • Start the Backup procedure
    (See the section: SQL Server Enterprise Manager - Create a backup file)
  • In the Backup steps there is a box you can check called "Schedule", then click the "..." Icon.
Creating a Text File.

Create a text file
Create a comma delimited file
Create a tab delimited file.
Export to Excel

Create a Text file from a Table

Using SQL Query Analyzer - Create a Text file (comma delimited):

  • select * from <table name>
  • Select the rows you want to export
    • Select all rows - click on the blank square (in the Column headings) above the first record.  All rows will be highlighted.
    • Select specific rows - Click on the row #'s - click & shift click works here.
    • Filename - enter your file name
    • Save as Type - choose "Export Files (*.CSV)
    • File Format - ANSI
    • Column Delimiter
      • Tab Delimited
        • To export to Excel, choose "Tab Delimited".  See the steps below on how to import data into Excel.
      • Comma Separated (CSV)
        • Be careful with this option.  In SQL Query analyzer you can not Text Qualify with Quotes so any commas used in fields will end up creating a new column in Excel.
    • To export to a file - right click on the same blank square and choose "Save As".

Import to Excel 2003 a Tab Delimited file from Query Analyzer.

  • Create a "Tab Delimited" file using the steps above for "Query Analyzer"
    Why Tab Delimited?
    1) Commas in a field (ie: ABC, Inc.) create additional columns in Excel.
    Why Data Import in Excel?
    1) You can tell Excel to import a column as text vs number etc...
  • Make sure the file extension is: .csv or .txt
  • Excel Import (Display Leading Zeros & large numbers as text)
    • Launch Excel and open up a worksheet.
      Put your cursor in starting cell for the imported data.
      Click "Data", "Import External Data", "Import Data"
      File Type:  Text Files
      Now navigate to the file.
    • Text Import Wizard - Step 1 of 3
      Select - Delimited, then click "Next"
    • Text Import Wizard - Step 2 of 3
      Only Check box checked should be: Tab (should be the default).
      Text Qualifier: {none}
      Click Next
    • Text Import Wizard - Step 3 of 3
      In the Data Preview section click on any column that you know you want a column data format other than "General" and then click the format you desire.
      (For instance, you have a column you know you want selected as "Text".  Text is handy for text cells that contain large #'s or numbers with leading zeros.)
      Click "Finish".

DTS (Data Transformation Services) - Create a Text file (comma delimited):

  • Launch "Enterprise Manager"
  • Locate the server, DB, then table.
  • Right click on the table and choose "All Tasks", "Export Data".
  • The first screen is the Data Source screen.  Make sure that you choose the DB you desire (should be defaulted).  Complete and click Next.
  • The second screen is the Destination.  Click on the Destination screen and choose "Text" file.  (fill in the data and then click Next)
  • You can choose all the info or a query.  Then Next.
  • Source:  Choose the table you want to export.  Then Next.
  • Then Next.

Import to Excel 2003 using a DTS package

  • Use the DTS package steps outlined above.
    Screen:  Select destination File Format (notes)
    File Type: Ansi
    Row Delimeter: {CR}{LF}
    Column Delimiter: Comma
    Text qualifier: Double Quote {"}
  • Make sure the file extension is: .csv
    You can rename the .txt file to a .csv file extension.
  • Excel Import (Display Leading Zeros & large numbers as text)
    • Launch Excel and open up a worksheet.
      Put your cursor in starting cell for the imported data.
      Click "Data", "Import External Data", "Import Data"
      File Type:  Text Files
      Now navigate to the file.
    • Text Import Wizard - Step 1 of 3
      Select - Delimited, then click "Next"
    • Text Import Wizard - Step 2 of 3
      Only Check box checked should be: Comma
      Text Qualifier: "    (The quote symbol)
      Click Next
    • Text Import Wizard - Step 3 of 3
      In the Data Preview section click on any column that you know you want a column data format other than "General".  For instance, you have a column you know you want selected as "Text"
      Click "Finish".
Export to Excel - using Query Analyzer
(WARNING: I suggest using the DTS package to Export Data)
Export to Excel (using Query Analyzer)
If you want to export data from Query Analyzer to Excel I suggest the following steps.  Follow these steps exact, even though you may want to use a different file extension, the first time through.  If it works, the you can try other steps and see if those work.

First let me make a few comments

  • Warning:
    If you have text that has #'s with leading zeros (maybe more than 1) will loose the leading zeros.
    (Note: I suggest using a DTS package to export the data.)
  • I'm testing with the following environment:
    Excel 2002 SP2
    MS SQL Server:
    SQL Query Analyzer version: SQL 8.00.2039
    MS SQL (select @@version): Microsoft SQL Server 2000 - 8.00.2039 etc...
  • I export as "Tab Delimited" because fields that have data with commas (ex: ABC Company, Inc.) do not export as comma delimited into excel in one cell (the end up in a cell per comma).
  • Also, I do not use the file extension of ".csv", which you would think you should use, because Excel will not read the tab character as a delimiter (all the data ends up in one cell).  So I use another extension like ".txt".

Steps:

  • Launch Query Analyzer.
  • Create your select SQL statement.  Run the Statement.
  • In the results section, click on the the small blank cell above the row #1 which is to the left of the tile of your first column of the select results.  Make sure you click the cell so that all of the rows and columns are highlighted (if not, you may get a file with 0 bytes of data).
  • Now, right click that same cell and choose "Save As"
  • For the filename DO NOT use the extension ".csv" but use the extension ".txt".  (Note: You could use .csv here and then rename the file to .txt later if you want.)  The point here is, you can't use .csv when you open with Excel.
    File name:  <your name>.txt
    Save as Type: Export Files (*.CSV)
    File Format: ANSI
    Column Identifier: Tab Delimited
    Then click "Save"
  • Now, right click on the saved file and open with Excel (Open With, Choose Program, Excel).
  • Immediately save the file from Excel as "Microsoft Excel Workbook (*.xls)"
  • Have fun.... 
SQL Query Analyzer SQL Query Analyzer (MS SQL 2000 Dev Ed.)
  • Show the Object Browser (DB, Tables, etc...) - Tools, Object Browser, Show/Hide
  • see "Print" to see an example of printing messages to the Message tab.
Create a Database Create a Database
  • CREATE DATABASE mydb;
  • 123 - Max char for db name

Using "Enterprise Manager"

  • Create the database.
    • Right click on "Databases" and choose "New Database".
    • Enter name of Database and click on "OK".
  • Create a new login for the Database.
    • In the "Security Folder" right click on "Logins".
    • Under the "General" tab enter the login name and password.
    • Under the "Database Access" tab choose the DB that the use will access.
    • Grant the permissions here also.  "public" & "db_owner" will give you plenty of permissions.
Renaming
(Rename a table, column, db, trigger,etc)
Rename a DB

sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'
 

Rename an Object (

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]

Object Types:
COLUMN
DATABASE
INDEX
OBJECT - An item of a type tracked in sysobjects. (constraints, user tables, views, stored procedures, triggers,  rules etc. )
USERDATATYPE - A user-defined data type added by executing sp_addtype.

This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'

This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
 

Java & MS SQL Types Java & MS SQL Types: 

Try at your own risk.  I've tested the ones with "Y" in the Tested column.  I suggest looking at Sun's Resources listed below the table!!!  If you can't find a match, try using the CAST or CONVERT (see doc's "Transact SQL Help" under "Help" in the "Query Analyzer" - search for "CAST and CONVERT") to CAST to a data type that you can retrieve using java.sql.ResultSet's methods.

If you get the error "Invalid Descriptor Index" you may be using the wrong ResultSet method to retrieve the data.

Tested with: [Microsoft][ODBC SQL Server Driver] - "sun.jdbc.odbc.JdbcOdbcDriver"

Warning:  It has been my experience that you must retrieve the fields ( ie: ResultSet.getString(), etc...) in the same order as the Sql statement or you will get the error: Invalid Descriptor Index.  Therefore you must get field 1 before filed 2, and so on.  Also, once you have retrieved the value, you can't retrieve the value again.  I've tested this with the following driver: "sun.jdbc.odbc.JdbcOdbcDriver".   Sun's API says, "For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once."  Remove the words "For maximum portability" for this driver!!!
Example:  (Test #2 errors with: Invalid Descriptor Index).
System.out.println( "Testing #1 = " + objResultSet.getString( 1 ) ); 
System.out.println( "Testing #2 = " + objResultSet.getString( 2 ) ); 

Tested Java Type java.sql.ResultSet MS SQL Type
      binary
      varbinary
      char
Y java.lang.String getString() varchar
      nchar
      nvarchar
Y java.sql.Date getDate() datetime
      smalldatetime
      decimal
      numeric
      float
      real
Y int getInt() int  (int 4)
      smallint  (int 2)
      tinyint  (int 1)
      money
      smallmoney
      bit
  java.sql.TimeStamp getTimestamp() timestamp
      uniqueidentifier
      image
      ntext
      text
Y = I've tested.

Sun's Resources:

Copy a Table Make a copy of a table (Structure and Data)
  • select * into CopyToTableName from CopyFromTableName

Make a copy of a table and change the column names

  • select systemID as 'UserName' into CopyToTableName from CopyfromTableName
  • The above command will change the name of the column name to 'UserName'.

Create SQL Insert commands from an existing table.

Concept: Write a SQL select statement that creates the insert command from all of the records that you desire.  Then cut-n-paste results to a text file. (Note: to insert a single quote into the results you escape the quote with another single quote.  (EX:  select ''' Hello ''' - this will show: 'Hello' )

  • select 'insert into myTable (myUser, myPswd, myInt) values (''' + myUser + ''',''' + myPswd + ''',' + convert(nvarchar, myInt) + ''')'
  • Then cut-n-paste the results to a text file.

 

Create a View Examples of creating a view:
  • Example of a Standard View
    if exists (select * from dbo.sysobjects where id = object_id(N'myView') ) Begin
      drop view myView_v
    end
    go
    create view myView_v as
      select
      cust.firstname as 'custfirstname',
      cust.lastname as 'custlastname'
      from myCustomers cust
        where cust.ID = 12345
    go
  • Example of creating a View from select / union statement.
    create view myview_view as
    select 1 as 'id', 'Hello 1' as description union
    select 2 as 'id', 'Hello 2' as description union
    select 3 as 'id', 'Hello 3' as description union
    select 4 as 'id', 'Hello 4' as description union
    select 5 as 'id', 'Hello 5' as description
    --See the data.
    select * from myview_view
    drop view myview_view
  • Example of creating a view from 2 tables:
    --Create a data table.
    create table mytable ( myInt int, myvarchar varchar(10) )
    insert into mytable (myInt, myvarchar) values (1, 'Code100')
    insert into mytable (myInt, myvarchar) values (2, 'Code101')
    insert into mytable (myInt, myvarchar) values (3, 'Code100')
    insert into mytable (myInt, myvarchar) values (4, 'Code101')
    insert into mytable (myInt, myvarchar) values (5, 'Code102')
    --Create a lookup table.
    create table myCode ( id int, code varchar(10), description varchar(20) )
    insert into myCode values (1, 'Code100', 'Desc for Code100')
    insert into myCode values (2, 'Code101', 'Desc for Code101')
    insert into myCode values (3, 'Code102', 'Desc for Code102')
    --Create a view of the 2 tables.
    create view myview_view as
    select a.myInt, a.myvarchar, b.description from myTable a
    left join myCode b on b.code = a.myvarchar
    --View the data
    select * from myview_view
    --Drop the tables and views
    drop view myview_view
    drop table mytable
    drop table mycode
     
If exists If exists
  • Drop a table if it exists:
    --Create the table
    create table mytable ( myInt int, myvarchar varchar(10) )
    select count(*) from dbo.sysobjects where name = 'mytable'
    --If the table exists, drop it.
    if exists (select * from dbo.sysobjects where id = object_id(N'mytable') ) Begin
    drop table mytable
    End
    --See if it still exists.
    select count(*) from dbo.sysobjects where name = 'mytable'
     
exec(@strSqlCommand)
Dynamic SQL using SQL variables
Reason for Use: You may want to create SQL code on the fly including references to DB name, table name & column names.

--Example of how to use a variable to create SQL for dynamic DB, Table & Columns.
declare @strSqlCommand varchar(255)
declare @strDBName varchar(255)
declare @strTblName varchar(255)
declare @strColName varchar(255)

set @strDBName = 'tempdb'
set @strTblName = 'tbltest'
set @strColName = 'coltest'

-- drop table tbltest
set @strSqlCommand =
'use ' + @strDBName + ' ' + char(10) +
'create table ' + @strTblName + ' ( ' + @strColName + ' varchar(10) ) ' + char(10) +
'insert into ' + @strTblName + ' (' + @strColName + ') values (''Code100'') ' + char(10) +
'select ' + @strColName + ' from ' + @strTblName + char(10) +
''

print @strSqlCommand

exec(@strSqlCommand)

 

String Options String Options
  • Convert from another type to a string.
    Print 'Hello ' + convert(nvarchar, 123)
    Displays: Hello 123
Case Examples of using case:
  • Command syntax:
    case
      when <condition (true/false) statement> then <true statement> else <false statement>
    end
  • Select example:
    • select ( case when '1'='1' then 'Yes' else 'No' end )
  • Using case in a select statement:
    --Create a table for an example.
    drop table mytable
    create table mytable ( myInt int, myvarchar varchar(10) )
    insert into mytable (myInt, myvarchar) values (1, 'Code100')
    insert into mytable (myInt, myvarchar) values (2, 'Code101')
    insert into mytable (myInt, myvarchar) values (3, 'Code102')
    insert into mytable (myInt, myvarchar) values (4, 'Code100')
    --Example of the case statement in a select.
    select myInt, myvarchar, case when myvarchar = 'Code100' then 'Great' else 'Sad' end from mytable
    --Example of the case in a sum().
    select sum(case myvarchar when 'Code100' then 1 else 0 end) from mytable
    select sum(case myvarchar when 'Code100' then 1 when 'Code102' then 100 else 0 end) from mytable
If Else  
Print This will print information to the Message tab in SQL Query Analyzer.
  • SET NOCOUNT ON - this command will remove the results count from showing up in the messages.
  • Print 'Hello'
  • Print 'Hello ' + convert(nvarchar, 123)
  •  
Triggers Trigger Example: Update, Insert, Delete.

Note:

  • Updates createhave a table called "DELETE" that has all the old values and a table called "INSERT" that has all of the new values.
  • Create Trigger must be the first statement in a batch so use the command "go" prior to the Create Trigger statement.  If not, you will get the error: "'CREATE TRIGGER' must be the first statement in a query batch."

sql_ex_trigger_ins_del_upd.txt - Example of using a trigger that will update the contents of one table based on Insert, Delete, and Updates on another table.  This example also tests the trigger.  When you run the SQL make sure you look at the "Messages" tab.

Disable/Enable a trigger:

alter table <tablename> disable trigger <triggername>
alter table <tablename> enable trigger <triggername>

 

UnionCombines the results of two or more SELECT statement into a single result set.

Ex:
SELECT 'Hello'
UNION
SELECT 'World'

One result set created:
Hello
World

 

Sort Order, Case Sensitive, CollationSort Order, Case Sensitive, Collation

/*
create database tempdb
create table myTable ( myInt int, myText nvarchar(255) )
insert into myTable ( myInt, myText ) values (1, 'hello world')
insert into myTable ( myInt, myText ) values (2, 'Hello World')
insert into myTable ( myInt, myText ) values (3, 'HELLO WORLD')
*/
-- SQL Server 2000 collations can be specified at any level.
-- Levels: DB instance, database, character column, variable, or parameter.
-- The defaults are based on the parent object.
-- The DB Server instance is determined by how SQL was installed. (non-case sensitive is the default)
-- Transact-SQL Help - Search for "case-sensitive instance of SQL Server", "collation", and "case".
-- Binary sort orders are always case-sensitive.
-- SQL Server 2000 supports two categories of character data types for collations:
-- The Unicode data types nchar, nvarchar, and ntext.
-- The non-Unicode character data types char, varchar, and text.

select * from myTable
select * from myTable where myText = 'hello world'
--select myTable.CaseSensitive

//List of valid collation names.
SELECT * FROM ::fn_helpcollations()

/*
delete myTable
drop table myTable
*/
 

Functions--Functions
SELECT * FROM ::fn_helpcollations()
select * from ::fn_servershareddrives()
select * from ::fn_virtualservernodes()
 
Stored Procedure Stored Procedures
  • Stored Procedures are stored in the DB.

Create a Stored Procedure - simple

Example #1:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testprocedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) begin
  drop procedure [dbo].[testprocedure]
end
GO
CREATE PROCEDURE testprocedure
AS
BEGIN
  -- <Place your SQL statements here.>
  -- Test example select:
  select getDate()
END
-- ************************
-- Run (execute) a Stored Procedure
exec testprocedure

Create a Stored Procedure - with input parms

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testprocedureparms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) begin
  drop procedure [dbo].[testprocedureparms]
end
GO
CREATE PROCEDURE testprocedureparms
  @Parm1 varchar(255),
  @Parm2 varchar(255),
  @Parm3 int
AS
BEGIN
  -- <Place your SQL statements here.>
  -- Test example select:
  select 'You passed: ' + @Parm1 + ', ' + @Parm2 + ', ' + convert(varchar(255), @Parm3)
END
GO
-- ************************
-- Run (execute) a Stored Procedure
exec testprocedureparms 'Hello', 'World', 123

Create a Stored Procedure - with input & output parms

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testprocedureparmsout]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) begin
  drop procedure [dbo].[testprocedureparmsout]
end
GO
CREATE PROCEDURE testprocedureparmsout
  @Out1 varchar(255) output,
  @Out2 int output,
  @Parm1 varchar(255),
  @Parm2 varchar(255),
  @Parm3 int

AS
BEGIN
-- <Place your SQL statements here.>
-- Test example select:
  select 'You passed: ' + @Parm1 + ', ' + @Parm2 + ', ' + convert(varchar(255), @Parm3)
  set @Out1 = 'Hi from the program'
  set @Out2 = 123
END
GO
-- ************************
-- Run (execute) a Stored Procedure
declare @strOut1 varchar(255)
declare @intOut2 int

exec testprocedureparmsout @strOut1 output, @intOut2 output, 'Hello', 'World', 123
select @strOut1, @intOut2
GO

Backup & Restore to a Network DriveMicrosoft Info:
http://support.microsoft.com/kb/207187
 

 

Command Info
Comments Comments
  • "-- ": 2 dashes and a space.  Beginning of line or at the end.
  • /* */ - multi-line comment.
Database Commands (General) Database Commands (General)

Examples

  • sql_ex_datbases.txt - Ex: Copy from data from one table in Database A to another table in Database B.

Database - notes

  • 123 - Max char for db name
  • See information about a Database
    SELECT name, crdate, filename, '|' as '|', * FROM master.dbo.sysdatabases WHERE name = N'MyTempDB_Databases_A'
     
Database - Create

Database - CREATE

  • Syntax: create database <database name>
    ex: create database MyTempDBCreate
Database - Drop

Database - DROP

  • Syntax: drop database <db_name>
    ex: drop database MyTempDBCreate
  • IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTempDB_Databases_A') begin
      DROP DATABASE [MyTempDB_Databases_A]
    end
    go
Database - Use

Database - USE

  • Syntax: use <db_name>
    ex: use MyTempDBCreate
Table Commands (General) Table Commands (General)

Notes:

  • System databases (msdb, master, model, tempdb) cannot be dropped.
  • Information about objects (ex: Tables, etc...)
    -- Select information about an object via the name field.
    select * from dbo.sysobjects where name = N'MyTable' -- via name field.
    -- Select information about an object via the object id.
    select * from dbo.sysobjects where id = object_id(N'MyTable') -- Using the object id.
    -- List all objects in the table.
    select type, name, id, crdate, refdate, '|' as '|', * from dbo.sysobjects
    -- List all objects created by Users (not system)
    select type, name, id, crdate, refdate, '|' as '|', * from dbo.sysobjects where type = 'U'

Examples

 

Table - Create

Create a Table

  • Syntax: create table <tablename>
    Ex:
    If exists (select * from dbo.sysobjects where id = object_id(N'mytable') ) Begin
      drop table mytable
    End
    create table mytable ( myidentity int IDENTITY(1,1), myInt int, myvarchar varchar(5) )
    insert into mytable (myint, myvarchar) values (100,'Help')
    select * from mytable

    Results:
    1 100 Help1
    2 200 Help2
  • Create a Table with an ID that is created automatically.  When you insert a row the field "id" is populated with a unique #.
    create table MyTable_Inserts(
      id int IDENTITY(1,1) not null,
      myInt int,
      myChar char(1),
      myNVarchar nvarchar(255)
    )

 

Table - Create Temp Table

Create a Temporary Table.

  • Temporary Tables - Create, Insert, Select, Drop
    if exists (select 1 from tempdb..sysobjects where name like '#tmptable%') begin
      drop table #tmptable
    end

    create table #tmptable ( id int identity(1,1), myMsg varchar(50) )
    insert into #tmptable (myMsg) values ('Hello World 1')
    select * from #tmptable
    drop table #tmptable
     
  • Temporary tables are not in the dbo.sysobjects table! Info on Temp Tables are located in the tempdb..sysobjects table!
    Ex: select * from tempdb..sysobjects
  • WARNINGS
    • You can't access temporary tables from within a user defined function.

 

Table - Drop

Drop a Table

  • Syntax: drop table <tablename>
    Ex:  drop table MyTable
  • Drop, if the table exists!:
    if exists (select * from dbo.sysobjects where id = object_id(N'mytable') ) begin
      drop table mytable
    end
 
Table - Alter

Alter a Table

  • Add a Column
    Syntax:
    alter table <tablename> add
      [columnname] [columntype],
      etc...

    Ex:
    exec sp_help <YourTableName> --To list the current structure.

    if not exists ( 
       select * from syscolumns
          where
          name = '<your column name>'
          and id in ( select id from dbo.sysobjects
                        where
                          id = object_id(N'[dbo].[<YourTableName>]')
                          and OBJECTPROPERTY(id, N'IsUserTable') = 1
                     )
    )
    begin
      ALTER TABLE dbo.<YourTableName> ADD
      [<your column name>] [datetime] default CURRENT_TIMESTAMP NOT NULL
    end
    exec sp_help <YourTableName> --To list the new structure.
  • Drop a Column
    Syntax:
    alter table <tablename> drop column
      [columnname],
      etc...

    Ex:
    exec sp_help <YourTableName> --To list the current structure.

    if exists ( 
       select * from syscolumns
          where
          name = '<your column name>'
          and id in ( select id from dbo.sysobjects
                        where
                          id = object_id(N'[dbo].[<YourTableName>]')
                          and OBJECTPROPERTY(id, N'IsUserTable') = 1
                     )
    )
    begin
      ALTER TABLE dbo.<YourTableName> DROP COLUMN
      [<your column name>]
    end
    exec sp_help <YourTableName> --To list the new structure.

     
  • Add a Foreign Key

    Good practice is to name the foreign key:
    FK_<TableName>_<ForeignTableName>

    Ex:
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_<TableName>_<ForeignTableName>]')) begin

      ALTER TABLE dbo.<TableName> ADD CONSTRAINT
      FK_<TableName>_<ForeignTableName>
      FOREIGN KEY ( <ColumnNameThatPointsToForeignTable> )
      REFERENCES <RefTableName>

    end
    (Note: A foreign key can only reference the primary key of the reference table.)
  • Drop a Foreign Key

    Good practice is to name the foreign key:
    FK_<TableName>_<ForeignTableName>

    Ex:
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_<TableName>_<ForeignTableName>]')) begin

      ALTER TABLE dbo.<TableName> DROP CONSTRAINT
      FK_<TableName>_<ForeignTableName>

    end
     
Table - Insert

Insert

  • sql_ex_insert.txt - example code.
  • Syntax: Insert 1 record
    insert into <table> (<field #1>, <field #2>, etc...) values (<value #1>,<value #2>, etc...)
  • Syntax: Insert 1 record using values from sub-queries:
    insert into <table>
      (<field#1>, <field#2>, <field#3>)
      select
        (select <fieldx> from <tablex> where etc....),
        'Y',
        100
  • Insert records from another table - if the structure is identical.
    insert into <tablename-copyto> select * from <tablename-copy from>
  • Insert records from another table - structure can be different.
    insert into <new table>
      (<field#1>, <field#2>, <field#3>)
      select <field#1-copytable>, <field#2-copytable>, <field#3-copytable>
        from <other copy table>
        where <your condition if you want>.
  • Insert records via select & union.
    insert into MyTable_Inserts ( myInt, myChar, myNVarchar )
      select 4, 'D', 'Hello World 4'
      union all
      select 5, 'E', 'Hello World 5'
      union all
      select 6, 'F', 'Hello World 6'
  • Insert records if a value doesn't exist
    insert into <yourtablename> ( codename )
      select 'None'
      where
        not exists ( select codename from <yourtablename>    
                       where codename = 'None' )
    (Note: Use this to build SQL in flattened Excel speadsheet to populate a table.)
  • Inserting & Updating a field type 'ntext'.

 

Table - Update

Updates

  • Update syntax:
    update <table> set <field> = 'New info' where <field> = 'Old Info'
  • Update syntax with aliases:
    update <**alias> set
      <field> = 'values...'
      ...
      from <table> <alias1>
        inner join <table> <alias2>...
        left join <table> <alias3> ...
      where
        ....
    (** Note: The update alias can be any of the aliases listed)
  • Batch Update - update one table from the values of another table.
    update mytableapp2 set stateint = (case ins.statechar when 'A' then 1 else 0 end)
      from INSERTED ins
      where ins.logonid = mytableapp2.logonid
  • Subqueries in the Update Statement.
    • Example File: sql_ex_update.txt
    • Syntax:
      update A1
        set A1.<myfieldA1> = A2.<myfieldA2>
        from <mytable1> A1
          left join <mytable2> A2 on A2.id = A1.id
        where
          A2.code = 'hello'
       
    • Example #1:
      (See example file above)
      Description:Update 1 record (in MyTable2) from the value of another record in another table (in MyTable).

      update t2
      set t2.mytest = t1.mytest
      from MyTable2 t2
      left join MyTable1 t1 on t1.code = t2.code
      where
      t2.code='A'
       
    • Example #2: 
      Ex file:
      in the same file as Example 1.
      Description: Update 1 record (in MyTable2) from the value of another record in the same table (in MyTable2). I found I had to create a Temp table!
      --Create a temporary table to hold the value.
      create table #tmptable ( code char(1), mytest nvarchar(255) )
      delete #tmptable
      go
      insert into #tmptable 
      select code, mytest from MyTable2 where code = 'A'
      
      select * from #tmptable
      
      update MyTable2
      set mytest = tmp1.mytest 
      from #tmptable tmp1
      where 
      MyTable2.code='C'
      and 
      tmp1.code = 'A'
      
      drop table #tmptable
    • Example #3:
      Ex File:  (Not available yet).
      Description: Update a field in a table if a value exists in another table.
      
      update m1
        set m1.description = 'OK'
        from MyTable1 m1
        where
          exists (select id from MyTable2 m2 where m2.code = m1.code)
      
Table - Delete

Deletes

  • Delete syntax:
    delete from <table> where <search condition>
  • Delete Subqueries syntax:
    delete <TableAlias_A>
      from <table> <TableAliasA>
      <optional joins here>
      where <search condition>
Table - Select Select - <under construction>
Select examples
  • Select records
    • select * from <table name> - selects all the records.
    • select distinct <fieldname>, <fieldname....> invoice from <table name>
  • select * from <table name> where exists ( <your select statement> )
  • Count the # or Records.
    • select count(*) from <table name>
  • Select Top set of records.
    • select top 25 * from MyTable - selects the first X number of rows from a table.
    • select top 10 percent from MyTable - selects the first 10% of the rows from a table.
    • NOTE: To my knowledge there is no "bottom" type option (opposite of top).
  • Formula created column
    • select myInt, Amount, Multipler = (1+(Rate/100)), Formula = ( (1+(Rate/100)) * Amount )
      from MyTableMath
      order by Formula
  • Selects & Locking - selects will lock the table by default.  Long running selects may cause locking issues. Here is options for select that don't lock records if you don't mind the data possibly changing.
    • Changing the default to "READ UNCOMMITTED"
      • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
        <place your select here>
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    • Specifying which table is "READ UNCOMMITTED"
      • select * from <tablename> with (READUNCOMMITTED) where <where clause...>
      • select *
          from <tablename> <alias> with (READUNCOMMITTED)
          left join <tablename> <alias> with (READUNCOMMITTED) on <join info...>
          etc.... 

       

Table (created from selects)

In this example I'm creating a table called "R1" from some selects.

select max(R1.test)
from
  (select 'hello 1' as test
   union all
   select 'hello 2' as test
   ) as R1

-- Results: hello 2

Table - Select - Order By Order by
  • Ascending Sort
    select * from mytable order by code
  • Descending Sort
    select * from mytable order by code DESC

Numeric to Text and Order By

  • Note: (I need to finish this example.) - this will cause a number converted to a char to sort like a number.
    ( right( space(3) + convert(nvarchar(255),C.seatsLeft), 3 ) + '/' + right( space(3) + convert(nvarchar(255),C.maxCapacity), 3 ) ) as 'seatssort',

 

Table - Select - Group By Group By

(need to create example...)

Wildcards, Special Characters, & setting the ESCAPE char Wildcards
   
% Any string of zero or more characters.
_ (underscore) Any single character.
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

-- Wildcard '%' - means any chars.
select 'Yes' where 'Hello World' like '%ell%' -- Yes (Must have 'ell' in the word some where)
select 'Yes' where 'Hello World' like 'ell%' -- <blank> (Must start with 'ell'.)
select 'Yes' where 'Hello World' like 'Hel%' -- Yes (Must start with 'Hel'.)
select 'Yes' where 'Hello World' like '%rld' -- Yes (Must end with 'rld'.)

-- Wildcard '_' (underscore) - means any one char.
select 'Yes' where 'Hello World' like '%e_lo%' -- Yes (Must have 'e' then any char then 'lo' then any chars.)

-- Wildcard '[a-e]' - means an one char in the range.
select 'Yes' where 'Hello World' like 'H[a-e]%' -- Yes (Must start with 'H' then any char from 'a' to 'e' then any chars.)
select 'Yes' where 'Hello World' like 'Ha-e%' -- <blank> (Must start with 'Ha-e' then any chars.)
select 'Yes' where 'HEllo World' like 'H[a-e]%' -- Yes, not case sensitive
select 'Yes' where 'Hfllo World' like 'H[a-e]%' -- <blank>, 'f' is not in the range.

-- Wildcard '[^a-e] - means NOT in the range.
select 'Yes' where 'Hello World' like 'H[^a-e]%' -- <blank> (Must start with 'H' then NO char from 'a' to 'e' then any chars.)
select 'Yes' where 'Hfllo World' like 'H[^a-e]%' -- Yes, 'f' is not in the range of a-e.

-- Wildcard '[ace]' - means that the char must be one of the chars in the brackets.
select 'Yes' where 'Hello World' like 'H[ace]%' -- Yes (Must start with 'H' then have a 'a','c', or 'e' then any chars.)
select 'Yes' where 'Hbllo World' like 'H[ace]%' -- <blank> 'b' is not one of the following chars: 'a','c', or 'e'. (Must start with 'H' then have a 'a' or 'c' or 'e' then any chars.)
select 'Yes' where 'HEllo World' like 'H[ace]%' -- Yes, NOT case sensitive.

-- Wildcard '[^ace]' - means that the char must NOT be one of the chars in the brackets.
select 'Yes' where 'Hello World' like 'H[^ace]%' -- <blank> (Must start with 'H' then NOT 'a','c', or 'e' then any chars.)
select 'Yes' where 'Hbllo World' like 'H[^ace]%' -- Yes, 'b' is NOT 'a','c', or 'e')

--Searching for the Wildcard: '%'
select 'Yes' where 'ab%cd' like 'ab[%]cd' -- Yes, the literal char '%' must be in designated spot.
select 'Yes' where 'abXcd' like 'ab[%]cd' -- <blank>, the literal char '%' is not in the 3rd slot.

--Searching for the Wildcard: '['
select 'Yes' where 'a[b-e]c' like '%[[]%' -- Yes, escape the open bracket: '[[]'
select 'Yes' where 'a[b-e]c' like 'a[b-e]c' -- <blank>, looking for 'a', then any char from b-e, then 'c'.

--Searching for the Wildcard: '%'
select 'Yes' where 'ab%cd' like 'ab[%]cd' -- Yes, the literal char '%' must be in designated spot.
select 'Yes' where 'abXcd' like 'ab[%]cd' -- <blank>, the literal char '%' is not in the 3rd slot.

--Searching for the Wildcard: '[' (Open Brackets)
select 'Yes' where '[Hello World]' like '[[]Hello World]' -- Yes, the '[' must be between brackets. (ie: '[]]')
select 'Yes' where '[Hello World]' like '[Hello World]' -- <blank>, Looking for one char of the following: Hello World
select 'Yes' where 'Hello[World' like 'Hello[[]World' -- Yes, the '[' must be between brackets. (ie: '[]]')
select 'Yes' where 'Hello[World' like 'Hello[World' -- <blank>
select 'Yes' where 'a[b-e]c' like '%[[]%' -- Yes, escape the open bracket: '[[]'
select 'Yes' where 'a[b-e]c' like 'a[b-e]c' -- <blank>, looking for 'a', then any char from b-e, then 'c'.
select 'Yes' where 'a[^b-e]c' like 'a[[]^b-e]c' -- Yes, looking for 'a', then NOT any char from b-e, then 'c'.
select 'Yes' where 'a[^b-e]c' like 'a[^b-e]c' -- <blank>, looking for 'a', then NOT any char from b-e, then 'c'.

-- Searching for the Wildcard: '_' (underscore)
select 'Yes' where 'Hello_World' like 'Hello[_]World' -- Yes, the '_' needs to be between brackets.
select 'Yes' where 'HelloXWorld' like 'Hello[_]World' -- <blank>, looking for the literal char '_' not an 'X'
select 'Yes' where 'Hello_World' like 'Hello_World' -- Yes, but '_' means any one character.
select 'Yes' where 'HelloXWorld' like 'Hello_World' -- Yes, but '_' means any one character.
select 'Yes' where 'Hello_World' like '%o_W%' -- Yes, but '_' means any one character.

-- Other chars alone are OK.
select 'Yes' where 'Hello-World' like 'Hello-World' -- Yes
select 'Yes' where 'Hello^World' like 'Hello^World' -- Yes

--Setting the ESCAPE character. (Note: escape_character has no default and must consist of only one character.)
select 'Yes' where 'ab%cd' like 'ab%%cd' escape '%'-- Yes, Escape now '%', the literal char '%' must be in designated spot.
select 'Yes' where 'ab_cd' like 'ab%_cd' escape '%'-- Yes, Escape now '%', the literal char '_' must be in designated spot.
select 'Yes' where 'ab%cd' like 'ab\%cd' escape '\'-- Yes, Escape now '\', the literal char '%' must be in designated spot.
select 'Yes' where 'ab_cd' like 'ab\_cd' escape '\'-- Yes, Escape now '\', the literal char '_' must be in designated spot.

Additional Example: Search Patterns - Open Bracket - '['

  • Enclose the open bracket ('[') within brackets (ex: '%[]]Hello World]%').  (See examples below)
  • Ex #1 - Plan search returns 9
    select patindex('%my_host_name%','http://[my_host_name]/test/index.html') -- 9
    Ex #2 - The open bracket is not enclosed within brackets and the results is not what you expect.
    --Note:  Was expecting a result of 8 but it returned 1.  Open Bracket as a text value needs to be enclosed with brackets.
    select patindex('%[my_host_name]%','http://[my_host_name]/test/index.html') -- 1 - was expecting 8.
    Ex #3 - This code is correct with the open bracket enclosed within brackets.
    select patindex('%[[]my_host_name]%','http://[my_host_name]/test/index.html') -- 8
MS SQL Global Variables MS SQL Global Variables - all begin with '@@'
 

use MyTempDB -- See my SQL script (at the top of the web page) that will create the DB with examples.

-- *************************
-- Cursor - Global Variables
-- *************************
-- int = @@CURSOR_ROWS - Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft® SQL Server™ can populate large keyset and static cursors asynchronously. @@CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @@CURSOR_ROWS is called.

select @@CURSOR_ROWS -- (Note:  I've tried using this without any success - sorry)

-- int = @@FETCH_STATUS - Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
(I have a link to an example of using cursors in this web page.)

-- *************************
-- MS SQL - Global Variables
-- *************************

-- nvarchar = @@VERSION - Returns the date, version, and processor type for the current installation of Microsoft® SQL Server™.
select @@VERSION -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

-- nvarchar = - Returns the name of the local server running Microsoft® SQL Server™.
select @@SERVERNAME -- THOMASMA9300

-- smallint = @@LANGID - Returns the local language identifier (ID) of the language currently in use.
select @@LANGID -- 0

-- nvarchar = @@LANGUAGE - Returns the name of the language currently in use.
select @@LANGUAGE -- us_english

-- nvarhcar = @@SERVICENAME - Returns the name of the registry key under which Microsoft® SQL Server™ is running. @@SERVICENAME returns MSSQLServer if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.
select @@SERVICENAME -- MSSQLSERVER

-- nvarchar(256) = @@REMSERVER - Returns the name of the remote Microsoft® SQL Server™ database server as it appears in the login record.
select @@REMSERVER -- NULL

-- smallint = @@SPID - Returns the server process identifier (ID) of the current user process.
select @@SPID -- 51

-- int = @@CPU_BUSY - Returns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since Microsoft® SQL Server™ was last started.
select convert(nvarchar, @@CPU_BUSY) + ' millisec - CPU Busy since started as of ' + convert(nvarchar, getdate())
--Returns: 8 millisec - CPU Busy since started as of May 5 2006 8:25AM

-- int = @@CONNECTIONS - Returns the number of connections, or attempted connections, since Microsoft® SQL Server™ was last started.
select convert(nvarchar, @@CONNECTIONS) + ' - connections as of ' + convert(nvarchar, getdate())
--Returns: 19 connections as of May 4 2006 9:10AM

-- int = @@IDLE - Returns the time in milliseconds (based on the resolution of the system timer) that Microsoft® SQL Server™ has been idle since last started.
select convert(nvarchar, @@IDLE) + ' ms - idle time as of ' + convert(nvarchar, getdate())
-- Results: 24937 idle time in millisec as of May 5 2006 8:18AM

-- int = @@IO_BUSY - Returns the time in milliseconds (based on the resolution of the system timer) that Microsoft® SQL Server™ has spent performing input and output operations since it was last started.
select convert(nvarchar, @@IO_BUSY) + ' ms - IO Busy time as of ' + convert(nvarchar, getdate())
-- Results: 28 ms - IO Busy time as of May 5 2006 8:52AM

-- int = @@LOCK_TIMEOUT - Returns the current lock time-out setting, in milliseconds, for the current session.
select convert(nvarchar, @@LOCK_TIMEOUT) + ' ms - Lock time-out for this session.'
-- Results: -1 ms - Lock time-out for this session.

-- int = @@MAX_CONNECTIONS - Returns the maximum number of simultaneous user connections allowed on a Microsoft® SQL Server™. The number returned is not necessarily the number currently configured.
select @@MAX_CONNECTIONS -- 32767

-- tinyint = @@MAX_PRECISION - Returns the precision level used by decimal and numeric data types as currently set in the server.
select @@MAX_PRECISION - 38

-- int = @@PACK_RECEIVED - Returns the number of input packets read from the network by Microsoft® SQL Server™ since last started.
select convert(nvarchar, @@PACK_RECEIVED) + ' - input packets read since last started as of ' + convert(nvarchar, getdate())
-- Results: 72 - input packets read since last started as of May 5 2006 8:59AM

-- int = @@PACK_SENT - Returns the number of output packets written to the network by Microsoft® SQL Server™ since last started.
select convert(nvarchar, @@PACK_SENT) + ' - output packets written since last started as of ' + convert(nvarchar, getdate())
-- Results: 75 - output packets written since last started as of May 5 2006 9:00AM

-- int = @@PACKET_ERRORS - Returns the number of network packet errors that have occurred on Microsoft® SQL Server™ connections since SQL Server was last started.
select convert(nvarchar, @@PACKET_ERRORS) + ' - network packets errors since last started as of ' + convert(nvarchar, getdate())
-- Results: 0 - network packets errors since last started as of May 5 2006 9:01AM

-- int = @@TOTAL_ERRORS - Returns the number of disk read/write errors encountered by Microsoft® SQL Server™ since last started.
select convert(nvarchar, @@TOTAL_ERRORS) + ' - disk read/write errors since last started as of ' + convert(nvarchar, getdate())
-- Results: 0 - disk read/write errors since last started as of May 5 2006 9:04AM

-- int = @@TOTAL_READ - Returns the number of disk reads (not cache reads) by Microsoft® SQL Server™ since last started.
select convert(nvarchar, @@TOTAL_READ) + ' - disk reads (not cache reads) since last started as of ' + convert(nvarchar, getdate())
-- Results: 585 - disk reads (not cache reads) since last started as of May 6 2006 5:37PM

-- int = @@TOTAL_WRITE - Returns the number of disk writes by Microsoft® SQL Server™ since last started.
select convert(nvarchar, @@TOTAL_WRITE) + ' - disk writes since last started as of ' + convert(nvarchar, getdate())
-- Results: 61 - disk writes since last started as of May 5 2006 9:06AM

-- int = @@TRANCOUNT - Returns the number of active transactions for the current connection.
select @@TRANCOUNT -- 0

-- tinyint = @@DATEFIRST - Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
select @@DATEFIRST -- 7 - 1=Monday ... 7=Sunday

-- varbinary = @@DBTS - Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.
select @@DBTS -- 0x0000000000000064


-- ***************************************
-- Stored Procedures - Global Variables
-- ***************************************
-- int = @@NESTLEVEL - Returns the nesting level of the current stored procedure execution (initially 0).

-- int = @@PROCID - Returns the stored procedure identifier (ID) of the current procedure.

-- ******************************
-- Other Global Variables
-- ******************************

-- int = @@ERROR - Returns the error number for the last Transact-SQL statement executed.
select * from master.dbo.sysmessages -- List of all system messages including error messages.

use MyTempDB
declare @intError int
declare @strErrorMsg nvarchar(255)
-- NOTE: This table doesn't allow NULL in the column 'myIntNotNull'.
insert into MyTableGlobalVarError ( myIntNotNull, myNVarchar ) values (null, 'Hello World 1')
set @intError = @@ERROR -- Store the error code on the next line after the command or you may loose the value !!!
set @strErrorMsg = (select description from master.dbo.sysmessages where error = @intError and msglangid = 1033)

if ( @intError > 0 ) begin
  Print 'Error: ' + convert(nvarchar, @intError) + ' - ' + @strErrorMsg
end else begin
  Print 'Insert was successful'
end
go
-- Results: Error: 515 - Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

-- numeric = @@IDENTITY - Returns the last-inserted identity value.
If exists (select * from dbo.sysobjects where id = object_id(N'mytable') ) Begin
  drop table mytable
End
create table mytable ( myidentity int IDENTITY(1,1), myMessage varchar(255) )
insert into mytable (myMessage) values ('Help 1')
select @@IDENTITY as 'Identity' -- Results: 1
insert into mytable (myMessage) values ('Help 2')
select @@IDENTITY as 'Identity' - -- Results: 2
select * from mytable

-- int = @@OPTIONS - Returns information about current SET options.
-- SET options can be modified as a whole by using the sp_configure user options configuration option.
--zzz I need to research this to find out the bit positions for the different options.
SET NOCOUNT ON
IF ( @@OPTIONS & 512 > 0 ) begin
  RAISERROR ('Current user has SET NOCOUNT turned on.',1,1)
end
select @@OPTIONS -- 6008
-- Messages Tab: Current user has SET NOCOUNT turned on.

-- int = @@ROWCOUNT - Returns the number of rows affected by the last statement.
use MyTempDB
select * from MyTableRowCount
select @@ROWCOUNT -- 3 - because there are 3 rows returned from the select statement.

-- int = @@TEXTSIZE - Returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns.

-- int = @@TIMETICKS - Returns the number of microseconds per tick.

-- EOF

Operators Operators

Query Analyzer Help - Click Help, SQL Transact Help, click the "Content" tab, click Transact SQL Reference, then click "Operators" to find MSSQL's Help info (much of the info below comes from there).

Operator Precedence - Follow he precedence levels below. If the same, then left to right.

() - Any thing in brackets is grouped together.
+ (Positive), - (Negative), ~ (Bitwise NOT) - Note: this is signs!
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

Example - Precedence:
select 3+2*2 -- Result is 7 because '*' has a higher precedence. ex: 2*2=4 + 3 = 7.
select (3+2)*2 -- Result is 10 because () groups items together.

Arithmetic operators

  • + (Add) Addition.
    - (Subtract) Subtraction.
    * (Multiply) Multiplication.
    / (Divide) Division.
    % (Modulo) Returns the integer remainder of a division. For example, 12 % 5 = 2 because the remainder of 12 divided by 5 is 2.
  • Examples:
    • Numeric examples:
      select 5+3 -- 8
      select 5-3 -- 2
      select 5*3 -- 15
      select 10/5 -- 2
      select 10 / 5 -- 2
      select (12%5)-- 2 (Note: The remainder is 2.)
    • Datetime examples: Subtracting days from a date:
      select convert(datetime, '05/05/2006') + 2 -- 2006-05-07 00:00:00.000
      select convert(datetime, '05/05/2006') - 2 -- 2006-05-03 00:00:00.000
      select convert(datetime, '01/01/2006') - 1 -- 2005-12-31 00:00:00.000

Assignment operator

  • = (equal) - assigns the value to the object.
  • Examples:
    Declare @myInt int
    set @myInt = 1

Bitwise operators

  • & (Bitwise AND) Bitwise AND (two operands).
    | (Bitwise OR) Bitwise OR (two operands).
    ^ (Bitwise Exclusive OR) Bitwise exclusive OR (two operands).
  • Examples:  Each position from left to right is evaluated with the other position.
    select (111 & 110) -- 110 - AND
    select (111 | 110) -- 111 - OR
    select (110 ^ 010) -- 100 - Exclusive OR - only 1,0 is true for Exclusive OR.  False for 0,0 or 1,1.

Comparison operators

  • = (Equals) Equal to
    > (Greater Than) Greater than
    < (Less Than) Less than
    >= (Greater Than or Equal To) Greater than or equal to
    <= (Less Than or Equal To) Less than or equal to
    <> (Not Equal To) Not equal to
    != (Not Equal To) Not equal to (not SQL-92 standard)
    !< (Not Less Than) Not less than (not SQL-92 standard)
    !> (Not Greater Than) Not greater than (not SQL-92 standard)
    is null - equality if the value is null. (Warning: = null - doesn't work)
  • Examples:
    select (case when 1=1 then 'Yes' else 'No' end) -- Yes
    select (case when 2>1 then 'Yes' else 'No' end) -- Yes
    select (case when 2<1 then 'Yes' else 'No' end) -- No
    select (case when 1>=1 then 'Yes' else 'No' end) -- Yes
    select (case when 1<=1 then 'Yes' else 'No' end) -- Yes
    select (case when -1>=1 then 'Yes' else 'No' end) -- No
    select (case when -1<=1 then 'Yes' else 'No' end) -- Yes
    select (case when 1<>1 then 'Yes' else 'No' end) -- No
    select (case when 0<>1 then 'Yes' else 'No' end) -- Yes
    select (case when 0!=1 then 'Yes' else 'No' end) -- Yes
    select (case when 0!<1 then 'Yes' else 'No' end) -- No
    select (case when 0!>1 then 'Yes' else 'No' end) -- Yes

Logical operators

  • ALL - TRUE if all of a set of comparisons are TRUE.
    AND - TRUE if both Boolean expressions are TRUE.
    ANY - TRUE if any one of a set of comparisons are TRUE.
    BETWEEN - TRUE if the operand is within a range.
    EXISTS - TRUE if a subquery contains any rows.
    IN TRUE - if the operand is equal to one of a list of expressions.
    LIKE TRUE - if the operand matches a pattern.
    NOT - Reverses the value of any other Boolean operator.
    OR TRUE - if either Boolean expression is TRUE.
    SOME TRUE - if some of a set of comparisons are TRUE.
  • Examples - under construction.

String concatenation operator

  • +
  • Examples:
    select 'Hello' + ' ' + 'World' -- 'Hello World'
    select '5' + '5' -- '55'
    select '5' + convert(nvarchar, 5) -- '55'
    select 'Michael''s world' -- Michael's world

Unary operators

  • + (Positive) Numeric value is positive.
    - (Negative) Numeric value is negative.
    ~ (Bitwise NOT) Returns the ones complement of the number.
  • Examples:
    select (-(1+4)) -- -5

     
SQL command Variables SQL Command Variables

Using Variables in your SQL

  • DECLARE @x int, @strMsg nvarchar(15)

  • DECLARE @la_id int
    set @la_id = 10
    select * from play.MyTable where ID = @la_id

Examples

  • DECLARE @strTest nvarchar(255)
    select @strTest = (select 'Hello World')
    print @strTest

  • DECLARE @strTest nvarchar(255)
    set @strTest = (select 'Hello World')
    print @strTest
     

Labels Labels allow you to jump within the code to a specific position.
goto <lablename> - how you jump to a label.
<lablename>: - use the colon after a word to create the label.
 

*************************
SQL Example:
*************************
declare @error char(1)

Print 'Start'
set @error = 'Y'

if ( @error = 'Y' ) begin
  goto ErrorMsg
end

Print 'Processing goes here ...'

goto Finished

ErrorMsg:
Print 'An error occurred.'

Finished:
Print 'Finished.'

********************
Messages returned:
********************
Start
An error occurred.
Finished.

 

SQL Looping - WHILE command Looping with SQL - WHILE command

-- ****************************************************
-- SQL Looping - WHILE Command
-- ****************************************************
declare @strValue varchar(255)
declare @intCount int
declare @intCountMax int

set @intCount = 0
set @intCountMax = 255

while @intCount <= @intCountMax begin

  set @strValue = (select (convert(varchar(15),@intCount) + '-' + char( @intCount ) ))
  print @strValue

  set @intCount = @intCount + 1

end
SQL Looping & Labels

 


-- ****************************************************
-- Looping with a Label ( Use while loop instead!!! )
-- ****************************************************

declare @strValue varchar(255)
declare @intCount int
declare @intCountMax int

set @intCount = 0
set @intCountMax = 255

forloop_begin:

  set @strValue = (select (convert(varchar(15),@intCount) + '-' + char( @intCount ) ))
  print @strValue

  set @intCount = @intCount + 1

  if ( @intCount <= @intCountMax ) begin
    goto forloop_begin
  end

forloop_end:

Example: Looping with Labels
 

Transactions Transactions - commit or rollback:

begin transaction Trans_Test
commit transaction Trans_Test
rollback transaction Trans_Test

Example: #1 - Basic

begin transaction Trans_Insert

  --<place your SQL command here. This example would be an Insert command.>
  --Note: If SQL fails a rollback will automatically be executed.

commit transaction Trans_Insert

Example: #2 - Temp Table Insert

if exists (select 1 from tempdb..sysobjects where name like '#tmptable%') begin
  drop table #tmptable
end
create table #tmptable ( id int identity(1,1), myMsg varchar(50) )

begin transaction Trans_Insert1
  insert into #tmptable (myMsg) values ('Hello World')
  insert into #tmptable (myMsg) values ('Hello World')
commit transaction Trans_Insert1

begin transaction Trans_Insert2
  insert into #tmptable (myMsg) values ('Hello World')
  insert into #tmptable (myMsg) values ('Hello World')
rollback transaction Trans_Insert

select * from #tmptable

/*
Because of the 2nd Transactions Rollback the results will be:
1 Hello World
2 Hello World
*/
 

While SET NOCOUNT ON

DECLARE @x int, @strEx nvarchar(30)
SET @x = 1
SET @strEx = 'Hello World'

PRINT 'Start'
WHILE @x <= len(@strEx)  BEGIN
  PRINT (SUBSTRING(@strEx, @x, 1))
  SET @x = @x + 1
END
PRINT 'Stop'
SET NOCOUNT OFF
GO

Results:
Start
H
e
l
l
o

W
o
r
l
d
Stop
 
SQL Query Analyzer Options SQL Query Analyzer Options:
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL
 
sp_help sp_help <Database Object Name> - will display the structure of the table.  I have seen values be incorrect though.

sp_help <table> - will display the structure of the table and other info.

 

sp_addumpdevice Ex: exec sp_addumpdevice 'disk', '01_Sunday' , '\\10.31.77.111\DB_BACKUPS\<yourdir>\01_Sunday.BAK' 

Fields

   
ntext Under Construction

Functions
DATALENGTH
PATINDEX
SUBSTRING
TEXTPTR
TEXTVALID

Statements
READTEXT
SET TEXTSIZE
UPDATETEXT
WRITETEXT

--The default setting for TEXTSIZE is 4096 (4 KB).
--MSSQL Server: Max is 2 GB
select @@TEXTSIZE -- current textsize setting
SET TEXTSIZE 64512 -- sets the textsize setting
select DATALENGTH(myntext) from MyTable
--The default setting for TEXTSIZE is 4096 (4 KB). This statement resets TEXTSIZE to its default value:
SET TEXTSIZE 0
-- Displays ntext after converting to a varchar.
select convert(varchar(8000), myntext) from <table>

SQL Query Analyzer's Display Properties for Max Char per column
Tools -> Options -> Results
Set Max Characters per column:  8000 (max) (Default is: 254)
 

   
   
   
   
   
   
   
   

Advanced Topics

Topic Info
DTS Packages Overview:
Data Transformation Services (DTS) is a tool in SQL 7.0 designed to help transform data in and out of SQL or execute a set of SQL commands within the DTS package.  At DTS file is referred to as a package. DTS packages can be saved in three different formats, as a file (Structured Storage File - .dts), into the local server (SQL Server), Visual Basic File, or into a repository.

DTS - Package Properties

  • To access the Package Properties, edit the DTS package, then right click on any white space and choose "Package Properties".
  • Tab:  Logging
    • Error Handeling
      To write to the System Application event log the completion status click the tab "Logging" then check the box "Write completion status to event log".  A successful completion shows and "Information" icon.  A failure shows a "Red X Error" icon.
      There is also an option to "Fail package on first error".
      • The step name written in the System Application event log comes from:
        Click Package, Disconnect Edit, Steps, Step Name.
    • Logging - Option to "Log package execution to SQL Server".
  • Transactions - there is an option to have the DTS package control the Transactions - commit & rollback.
  • The function APP_NAME() gets it's value from the following property: Click Package, Disconnected Edit, Connections, Microsoft ...., OLE DB Properties, Application Name, Value

DTS - Scheduling Execution

  • Steps to Schedule Execution:
    Open SQL Server Enterprise Manager
    Make the DTS package a "Local Package".
    Right click on the local package and click "Schedule Package".
    Complete the Job Schedule options.
  • View all Scheduled Task
    Management, SQL Server Agent, Jobs - here is where you can see the jobs that you created in the above step.
  • Note: The SQLServerAgent service needs to be running on the target server for the package to be executed. 

File Formats - Pros & Cons

  • file - fastest execution and less chance for corruption.
  • local server - saved in your MS SQL environment (not your apps DB), take longer to load (no metadata), execute slower that a file but faster than a repository. Potential of package corruption (lower than repository).
  • repository - saved in your MS SQL environment (not your apps DB), load faster than local server but slower than file, and are the slowest to execute.  Potential of package corruption. Metadata tracks changes to the package as well as monitors who has executed the package and when.
    • To turn on metadata, edit the DTS package and click the "Advanced" tab and click the options under "Lineage"

DTS - First Steps (SQL 2000)

  • Download a zip file of the files used in this example.
  • Create your first DTS package.
    • Open up Query Analyzer and run the script:  sql_ex_dts_basics_A_createDBandTables.sql
      • This will create a blank DB called: MyTempDB.
      • Then it will create a table in the DB called: MyTable
    • Open up Enterprise Manager (Programs, Microsoft SQL Server, Enterprise Manager)
    • Expand your SQL server and right click on "Data Transformation Services"
    • Create a DTS Package to Import records.
      • Right click on "Local Packages", "New Package"
      • Click "Connection" and choose "Microsoft OLE DB Provider for SQL Server"
      • (Deal with the Authentication section).
      • Under Construction

Running DTS Packages

  • Command Line (DOS Prompt and/or batch files)
    • DTSRun /F <filename of dts package>
      Ex: DTSRun /F mydtspackage.dts
      Ex: DTSRun /F mydtspackage.dts >  mydtspackage.log
    • FYI:  The file location for DTSRun is normally:
      C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSRUN.exe
  • Enterprise Manager
  • Query Analyzer
  • Scheduled DOS Task

Calling DOS from a DTS package ( via WshShell )

  • WARNING:  For Win2000 OS make sure that you fully qualify your path.  For XP, you can get by without fully qualifying the path based on the path of the external DTS package.
  • Example:
    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run "C:\myexample\testbatchfile.bat", 8, True
  •  
exec Exec
  • exec master..xp_cmdshell '<DOS Command>' - Running a DOS Command from SQL:
    Ex: exec master..xp_cmdshell 'dir c:'
    - this will return a windows listing equivalent to the DOS command:
    c:\> dir c:
     
DB Cursors DB Cursors - tech/mssql/sql_ex_cursors.txt

Example:
-- Other Variables
declare @txtcodelist nvarchar(255)
-- Cursor Variables
declare @id int
declare @code char(1)
declare @codedesc nvarchar(255)

DECLARE cursorCodes CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
select id, code, codedesc
  from MyTable

set @txtcodelist = ''
OPEN cursorCodes

FETCH NEXT FROM cursorCodes
  INTO @id, @code, @codedesc

WHILE (@@FETCH_STATUS = 0) BEGIN

  set @txtcodelist = @txtcodelist + convert(nvarchar, @id) + ': ' + @code +
' - ' + @codedesc + '; ' + char(13)+ char(10)

  FETCH NEXT FROM cursorCodes
    INTO @id, @code, @codedesc

end

Print 'Code list: ' + char(13)+ char(10) + @txtcodelist

/*****************************************************/
/* Close the cursor and deallocate it. */
/*****************************************************/
Close cursorCodes
DEALLOCATE cursorCodes

 

Raise an Error Raise an Error - tech/mssql/sql_ex_raiseerror.txt
Note: I need to study this more.  I've used the following in a DTS package.
For more info, use SQL Query Analyzer help and search for "RAISERROR".

-- Note: If this was running in a DTS package, the process will show an error and you can view the message.
declare @ErrorMsg nvarchar(255)
set @ErrorMsg = 'Warning: You have exceeded the max value of: ' + convert(nvarchar(255), 500)
RAISERROR (@ErrorMsg, 16, 1)
 
DB Stress Tool DB Stress Tool - this is a simple set of SQL statements that can be run in SQL Query Analyzer (1 or more Query Analyzer windows) to put a stress on the DB.
Stack Space Error Stack Space Errors:

SQL Error:
Internal Query Processor Error: The query processor ran out of stack space during query optimization.

Possible Reason:
An " in " clause is being used that has so many values that the stack space get's filled up.
 

Getting lock data sp_lock
sp_lock1
sp_lock2 - advanced locking info.
 
Locks & Objects - troubleshooting Locks & Objects - troubleshooting

exec sp_lock -- This will show all the locking info.
exec sp_lock 53 -- This will show locking info for Process ID 53.
select object_name(<place objectid number here>) -- Will give you the Object name (ie: table etc...) - usually!

Locks that are OK: (In my opinion)
S (Shared). The holding session is granted shared access to the resource.
IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.

exec sp_who -- Shows who is doing what!
exec sp_who 'active' -- Display onlyactive processes
exec sp_who '51' -- specifies the process_id (Note: All user processes start at 51.)
exec sp_who 'sa' -- filter by login name.
exec sp_who 'NT AUTHORITY\SYSTEM' -- filter by login name.

The Microsoft Article is here http://msdn.microsoft.com/en-us/library/aa238824(SQL.80).aspx

SQL Tracking Thoughts - Auditing Columns SQL Tracking Thoughts - Auditing Columns

dateTimeCreated datetime default CURRENT_TIMESTAMP not NULL,
dateTimeModified datetime default CURRENT_TIMESTAMP not NULL,
modifiedBy varchar(20) default substring(SUSER_SNAME(),1,20) not null,
modifiedByAppName varchar(20) default substring(APP_NAME(),1,20) not null

Use a Trigger to update the 3 modified by fields.

-- Start: <yourtablename>_update update trigger

if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[<yourtablename>_update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  drop TRIGGER <yourtablename>_update
go

CREATE TRIGGER [<yourtablename>_update] ON [dbo].[<yourtablename>]
FOR UPDATE NOT FOR REPLICATION AS
BEGIN

  SET NOCOUNT ON

  /* trigger_nestlevel must be 1. */

  IF ( (SELECT trigger_nestlevel() ) = 1 ) BEGIN

    begin transaction

    update A set
    [modified] = GETDATE(),
    [modifiedBy] = substring(SUSER_SNAME(),1,20),
    [appName] = substring(APP_NAME(),1,20)
    from <yourtablename> A
    inner join INSERTED I on I.<yourcolumnname> = A.<yourcolumnname>
    --Optional inner join, only modify if content changes. Just OR all the column names.

    inner join DELETED D on D.<yourcolumnname> = A.<yourcolumnname>
      where D.<yourcolumnnameToCompare> <> A.<yourcolumnnameToCompare>

    commit

  END -- IF

END -- CREATE TRIGGER
go
-- Stop: <yourtablename>_update update trigger
 

Default Dates:
getDateUTC() - GMT datetime (UTC).
getDate() - current server date
CURRENT_TIMESTAMP - current server date
 

System Tables System Tables - each DB has the following system tables in MSSQL 2000

select * from syscolumns
select * from syscomments
select * from sysdepends
select * from sysfilegroups
select * from sysfiles
select * from sysfiles1
select * from sysforeignkeys
select * from sysfulltextcatalogs
select * from sysfulltextnotify
select * from sysindexes
select * from sysmembers
select * from sysobjects
select * from syspermissions
select * from sysproperties
select * from sysprotects
select * from sysreferences
select * from systypes
select * from sysusers

Books