MS SQL Survival Guide - Misc
(SQL 2000)

• select @@version
• Versions
  • MS SQL Server 2000
    • v8.00.2055 - Dec 16, 2008
  • MS SQL Server 2005
  • MS SQL Server 2008

• 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
   

• 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

• 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.

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

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_ex_fully_qualified_names.txt - example of accesses different DBs without using the 'use' command.
   

• 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...

• 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
       

• 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".

• 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>'

• 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.)

• 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.

• 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.

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".

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.... 

• 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 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.

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'
 

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 ) ); 

Sun's Resources:

• http://java.sun.com/docs/books/tutorial/jdbc/basics/_retrievingTable.html
  Paragraph type version.
• http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html
  Table versions.
  

• 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.

• 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
   

• 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'
   

--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)

 

• Convert from another type to a string.
  Print 'Hello ' + convert(nvarchar, 123)
  Displays: Hello 123

• 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

• SET NOCOUNT ON - this command will remove the results count from showing up in the messages.
• Print 'Hello'
• Print 'Hello ' + convert(nvarchar, 123)
•  

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>

 

Ex:
SELECT 'Hello'
UNION
SELECT 'World'

One result set created:
Hello
World

/*
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
*/
 

• Stored Procedures are stored in the DB.

Create a Stored Procedure - simple

Create a Stored Procedure - with input parms

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

• "-- ": 2 dashes and a space.  Beginning of line or at the end.
• /* */ - multi-line comment.

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

• Syntax: create database <database name>
  ex: create database MyTempDBCreate

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

• Syntax: use <db_name>
  ex: use MyTempDBCreate

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

• sql_ex_tables.txt - SQL example.

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)
  )

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.

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

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
   

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'.
  • sql_ex_insert_from_another_table_ntext.txt
     

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)
    

Deletes

• Delete syntax:
  delete from <table> where <search condition>
• Delete Subqueries syntax:
  delete <TableAlias_A>
    from <table> <TableAliasA>
    <optional joins here>
    where <search condition>

• tech/mssql/sql_ex_select.txt - SQL code used for the "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.... 

     

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

• 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',

(need to create example...)

-- 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

• tech/mssql/sql_ex_globalvar.txt - SQL code used for "Global Variables" examples.

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

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
  
   

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
   

*************************
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.

Example: Looping with Labels
 

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
*/
 

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

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)
 

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 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:
   

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

 

• tech/mssql/sql_ex_dbstresstool_a_setup.txt - creates the DB & test table.
• tech/mssql/sql_ex_dbstresstool_b_test.txt - creates the stress on the DB.  Can be run in 1 or more Query Analyzer windows.  Just change the TestCode for each window.
• tech/mssql/sql_ex_dbstresstool_c_drop_db.txt - drops the DB.

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.
 

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!

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

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
 

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