Last updated September 20, 2021

SQL Examples with Microsoft SQL Server

Here's my notes on using Sql Server for a decade.

Working with Databases

Creating Databases:
```
CREATE DATABASE myNewDatabase
```

Deleting Databases:

DROP DATABASE database_name [, database_name...]

Renaming a database

USE master
   GO 
   EXEC sp_dboption myOldData, 'Single User', True
   EXEC sp_renamedb 'myOldData', 'myNewData'
   EXEC sp_dboption myNewData, 'Single User', False
   GO

Working With Tables
Traditionally known as CRUD - Create, Read, Update, and Delete
1. Creating a Table
  1. syntax:
```
  CREATE TABLE tablename (column_name column_type [(column_width[,column_precision])] ...)
```
    example:
```
CREATE TABLE practice
(
 fname     VARCHAR(20)   NOT NULL,
 lname     VARCHAR(30)   NOT NULL,
 hire_date SMALLDATETIME NULL,
 ssn       CHAR(11)      NOT NULL
)
GO
-- 'NOT NULL' is the default.
```
```
EXECUTE sp_help practice -- shows structure of table
```
  2. ALTER TABLE practice
```
ALTER TABLE XYZ ADD mi CHAR(1) NULL
GO
```
2. "SELECT INTO"
  Tables can also be created with the "SELECT INTO" construct SELECT select_list INTO new_table_name FROM table_list WHERE search_condition Note: if new_table_name starts with #, it will be put in the temp tables ##, it will be in temp tables and be global (accessable to all other sessions) Example using Temp tables:
```
SELECT * INTO #tempcustomers2 FROM customers
SELECT * FROM #tempcustomers2
```
  Temp tables go away when the server is restarted or at the end of the connection.
3. Deleting a table: DROP TABLE table_name
4. Add a column: ALTER TABLE SDSurveys ADD KQLogic VARCHAR (1000) NULL
5. Add a column with check for existence first
```
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn')
BEGIN
   ALTER TABLE MyTableName ADD newcolumn varchar(32) NOT NULL DEFAULT ''
END
```
6. How to delete, or drop, a column:
  Always a good idea to test if the column is there if you are running from a script
```
IF EXISTS (SELECT * FROM dbo.syscolumns 
    WHERE id = object_id(N'[dbo].[Projects]')
    AND name = 'ProjectManager')
       ALTER TABLE Projects DROP COLUMN [ProjectManager]
```
7. Show list of columns in a table: sp_columns table_name

Working With Rows

Inserting Rows into a Table

Syntax:

INSERT INTO table_name [(column_id1,column_id2...)]
   VALUES (value1,value2,...)
examples:
INSERT INTO autos (1996,'ford') -- INTO is an optional keyword in SQLServer
INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word
INSERT INTO title VALUES(1001,'The Odyssey','Homer',NULL)

add multiple rows to a table

INSERT INTO table (c17,c4,c8,c3) SELECT a,b,c,d FROM ...

add multiple rows to a table with stored procedure

INSERT INTO table
(c17,c4,c8,c3)
EXECUTE sp_mystored_procedure

INSERT rows from this database into a table in another database

INSERT INTO OtherDatabaseName..users 
SELECT * FROM users WHERE loginid ='mfincher'

UPDATEing Rows in a Table

UPDATE changes the values in an existing row

UPDATE tbl SET col = value|col|expr
UPDATE table_name SET column_id = expr  WHERE condition

Examples:

    update users set password = 'newpass' WHERE user = 'quest'
    update users set password = 'newpass' WHERE (UserID > 1) AND (UserID < 113)
    update users set password = 'newpass', change='Y' WHERE user = 'quest'
    UPDATE discounts SET discount = discount + .1 WHERE lowqty >= 100
    UPDATE employees SET LNAME = UPPER(LNAME)

updating a value based on other tables

UPDATE titles SET ytd_sales = 
(SELECT SUM(qty)
 FROM sales
 WHERE sales.title_id = titles.title_id
 AND ord_date BETWEEN '01/01/94' AND '12/31/94')

example of adding a record based on data in another table

INSERT adult SELECT
  ju.member_no,  ad.street,  ad.city,  ad.state,  ad.zip,
  ad.phone_no,
  DATEADD(YY, 1, GETDATE())
FROM
  juvenile ju -- aliasing juvenile to ju
JOIN
  adult ad    -- aliasing adult to ju
ON
  ju.adult_member_no = ad.member_no
WHERE
  (DATEADD(YY, 18, ju.birth_date) < GETDATE())

Deleting rows from a Table DELETE FROM table_name [WHERE search_condition] example:

DELETE FROM mytable WHERE userid < 50
DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table)
TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction
DELETE titles WHERE title_id in (SELECT title_id FROM ...)

How to format a number with two decimal places

-- this will return a field 13 characters wide, 10 before the decimal point and two after
select  CONVERT(DECIMAL(12,2),myField) from myTable
-- if your number is larger than that you get this error:
-- Msg 8115, Level 16, State 8, Line 1
-- Arithmetic overflow error converting int to data type numeric.
-- or you can use the STR function
select str(myField, 12, 2) from myTable

Example of CAST, Datepart, and STR

In the example below, "dtime" is a column containing the datetime. "datepart(yy,dtime)" produces the year, "str(datepart(dy,dtime),3)" produces a right justified day-of-the-year.

SELECT cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)  as 'Year  Day', 
       COUNT(time) AS 'Key Question Pages',
       avg(time) AS 'avg time (msec)' 
FROM sdrespondentpagetimes
WHERE name='Key'
group by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)
 order by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3) asc

The results:

Year  Day Key Question Pages avg time (msec) 
--------- ------------------ --------------- 
2004  51  13                 768
2004  54  10                 747
2004  56  6                  1090
2004  57  34                 702
...
2004 351  1                  6000
2004 362  14                 548
2005   4  3                  629
2005   5  20                 834
2005   7  10                 469
2005  10  1                  3593
2005  11  1228               269

SQL Server Utilities [Note: use osql only if your version of Sql Server is earlier than 2005. Use "sqlcmd" for new versions. You are better off using Invoke-Sqlcmd inside PowerShell.]
1. osql - the command line batch interface to SQL Server before 2005
  1. To EXECUTE a single statement use the -Q option
```
       osql -Umoi -P"mypassword"
          -Q"INSERT DATA_TABLE (column1, column2) values col1_value, col2_value"
```
  1. To have osql run a script file, (e.g., testSQL.SQL), use the -i option
```
osql -Umyuserid -P"mypassword" -itestSQL.SQL
```
  2. Then to get things back out use the -o option:
```
osql -Umoi -P
-Q"SELECT column1, column2 FROM DATA_TABLE" -oOutput_file
```
  3. To specify a host: -H delphi
  4. To specify a server: -S delphi
  5. To specify a server running SQLExpress: -S "delphi\SqlExpress"
  6. to specify a database: -d db3
  7. to surpress the headers: -h-1
  8. to specify a separator: -s!
  9. to set the line width to 5000: -w5000
  A better way to handle the password is to set an environmental variable, "OSQLPASSWORD", so the "-U" option may be omitted. In "sqlcmd" you can use the "-E" option and use your current identity.
2. To get a list of all databases on a server
```
SELECT DISTINCT name FROM SYSDATABASES
```
3. To see which service pack (if any is installed)
```
SELECT SERVERPROPERTY('ProductLevel')
Returns:
'RTM' = shipping version.
'SPn' = service pack version
'Bn', = beta version.
```
4. To impersonate a user:
```
     SETUSER 'mynewname'
     GO
     (SQL statements)
```
  use SETUSER to get back to the original user
5. To add a login for a new user for a database:
```
     EXECUTE sp_adduser 'newloginid'
     EXECUTE sp_addlogin 'newloginid','password'
```
6. To find the size of all databases
```
EXEC sp_helpdb  
-- or for one database
EXEC sp_helpdb 'dbname'
```
7. To get a list of all databases on a server
```
SELECT name FROM master..sysdatabases
-- or 
EXEC sp_databases
```
8. To grant a user permission to a database:
```
     USE mydatabase
     EXECUTE sp_adduser newloginid
```
9. To grant a user permission to a database table:
```
GRANT [EXECUTE|ALL] ON TableName TO myloginid
```
10. Note: If a stored procedure is not the first item in a series of SQL statements, it must be prefaced with 'EXECUTE'.
11. To unload a dll: dbcc xp_netq (FREE)
12. To have SQLServer run a simple diagnostic on a db named mydb:
```
dbcc checkdb(mydb) 
```
  also try dbcc checkalloc(db4)
13. To get the name of the current database
```
SELECT db_name()
```
14. to show the names of all databases
```
USE master
SELECT name FROM sysdatabases
```
15. to get the date
```
SELECT getDate()
```

SQL Server Results

Three ways to change column headings

SELECT First = au_fname,
       au_lname 'Last Name',
       au_id AS 'Identification Number'
FROM authors
Produces:       
First      Last Name                  Identification Number 
----------------------------------------------------------- 
Johnson    White                      172-32-1176           
Marjorie   Green                      213-46-8915

Retrieving Results

General to get unique values:

SELECT DISTINCT column_name FROM mytable -- note: this also does an implicit sort

to get frequency of column values:

SELECT column_name, COUNT(column_name) as mycount FROM table_name
      GROUP BY column_name ORDER BY mycount [ASC | DESC]

to join two tables the SQLServer way:

SELECT tablea.A1, tableb.B1 FROM tablea, tableb
    WHERE tablea.mykey = tableb.mykey

to get info from two tables

UPDATE table1 SET mycolumn = '2' WHERE userid IN
         ( SELECT userid FROM table2 WHERE table2.dissat <> 'somevalue')

Aliasing tables

SELECT a.title_id, a.au_id, b.au_id
FROM titleauthor a
INNER JOIN titleauthor b
ON a.title_id = b.title_id
WHERE a.au_id < b.au_id

how to preface info in a column with a string, 'x-',
```
UPDATE mytable SET mycolumn = 'x-'+mycolumn
```

wildcards used in the LIKE clause:

Wildcard	Meaning
%	Any string of zero or more characters
_	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])

LIKE 'Mc%' searches for all names that begin with the letters "Mc" (McBadden).
SELECT column_name FROM table WHERE mystring LIKE '%[XYZ]' 

To find all fields which contain a carriage return:
SELECT userid, Zip FROM mytable WHERE Zip like '%'+char(10)+'%'

To find fields containing an '_', which is tricky since '_' is usually the "any character" flag.
SELECT labelInfo FROM mytable WHERE labelInfo like '%[_]%'

Advanced Topics in Retrieving Results

Limit the number of rows returned

(note this can be very dangerous since it affects all subsequent commands)

SET rowcount 4 
SELECT TableName,ColumnName,Language Title,LU_Table,Description,Logic,Type FROM DataGroup 
SET rowcount 0 -- turns off rowcounting

UNION This concatenates result set together

USE sqldemo
SELECT * FROM authors   --23 rows
UNION
SELECT * FROM newauthors  --8 rows
ORDER BY authors.au_lname

UNION will surpress duplicates, use UNION ALL to show all rows

GROUP BY and HAVING

/*
**  This query will search through the member_no column looking for
**  member numbers that have duplicate values and also show the number
**  of duplicates found for each member number.
*/
  SELECT member_no, Number_of_Dups = count(*)
  FROM member
  GROUP BY member_no
  HAVING count(*) > 1

Stored Procedures

General syntax of stored procedure

CREATE PROCEDURE pname
   @var vartype[=defaultvalue][,@var2 vartype]
AS
   ...
GO

Declaring Variables

DECLARE @varname type[,@varname type]* -- to define a variable
DECLARE @x int
SELECT @x = 5

Simple Example

CREATE PROCEDURE emplist
AS
  SELECT empname, title, salary
  FROM emp
  ORDER BY title
GO

Simple Example with one argument

CREATE PROCEDURE AuthorState
@statevar CHAR(2) = '__' -- if no arg passed, '__' is the default
AS SELECT state, au_fname, au_lname FROM authors WHERE state LIKE
@statevar ORDER BY au_lname GO To test it: EXECUTE AuthorState
'UT'

Fun things to know and tell about Stored Procedures
1. Stored Procedures can have up to 255 parameters
2. EXECUTE sp_helptext mystoredproc -- shows the source code
3. EXECUTE sp_depends mystoredproc -- see what tables are associated with the sp
4. SELECT name FROM sysobjects WHERE type = 'P'-- to see all the stored procedures
5. sp_makestartup -- makes an existing sp a startup procedure
6. sp_ummakestartup -- removes an existing sp a startup procedure
7. in transact sql to get the results of a select into a variable:
```
SELECT @LanguageStringID = MAX(LanguageStringID) FROM
LanguageString
```

How to drop a stored procedure

IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'P' AND name = 'addadult' )
   DROP PROCEDURE addadult

or you can do this:

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
   DROP PROCEDURE [dbo].[YourStoredProcName]
GO

example of complete sp:

CREATE PROCEDURE addadult
    @lastname         shortstring,
    @firstname        shortstring,
    @middleinitial    letter = NULL,
    @street           shortstring = NULL,
    @city             shortstring = NULL,
    @state            statecode = NULL,
    @zip              zipcode = NULL,
    @phone_no         phonenumber = NULL
AS 
    DECLARE  @member_no  member_no

    IF  @middleinitial = NULL OR
        @street        = NULL OR
        @city          = NULL OR
        @state         = NULL OR
        @zip           = NULL OR
        @phone_no      = NULL 
    BEGIN
      PRINT "   "
      PRINT "   Please reenter this Adult Member."
      PRINT "   You must provide Last name, First name, Middle initial, "
      PRINT "    Street, City, State, Zip, and Phone number."
      PRINT "   "
      RETURN
    END

    BEGIN TRANSACTION

    SELECT @member_no = (SELECT max(member_no) FROM member HOLDLOCK) + 1

        INSERT member 
            ( member_no,  lastname,  firstname,  middleinitial, photograph)
        VALUES    
            (@member_no, @lastname, @firstname, @middleinitial, NULL )
        IF @@error <> 0
         BEGIN
           ROLLBACK TRAN
           RETURN
         END
        INSERT adult
            ( member_no,  street,  city,  state,  zip,  phone_no, expr_date)
        VALUES
            (@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
        IF @@error != 0
         BEGIN
           ROLLBACK TRAN
           RETURN
         END

         PRINT "      "
         PRINT "        *** Adult Member added ***   "
         PRINT "      "


    COMMIT TRANSACTION
GO

Another Example

CREATE PROCEDURE overdue_books 
AS
SELECT member.lastname,copy_no,due_date FROM loan 
JOIN member ON loan.member_no = member.member_no
    WHERE DATEDIFF(DAY,GETDATE(),due_date)>1
GO

OUTPUT parameter

Example using the OUTPUT parameter

** This script file creates a procedure, LOANCOUNT, 
** that returns a status of 0 if a member has any 
** loans. If a member has no loans, it returns a
** status of -1. 
USE library 
GO CREATE PROC loancount @member_no member_no, @loancount int OUTPUT AS IF EXISTS (SELECT
* FROM loan WHERE member_no = @member_no) 

BEGIN SELECT
@loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN
(0) END ELSE RETURN (-1) 
GO 
--NOTE: if any syntax errors in a batch, nothing in batch runs 
-- all the statements are rolled together for syntax checking 
-- if any statements have problems the "GO" is not executed.

How to write a FUNCTION

Functions are more flexible to use than stored procedures. Here's a simple function that returns an integer.

IF OBJECT_ID(N'NumberProcessedInTimeSpan', N'FN') IS NOT NULL 
   DROP function [dbo].[NumberProcessedInTimeSpan]
GO
CREATE function NumberProcessedInTimeSpan (@startDate DateTime, @endDate DateTime ) 
returns int 
AS 
BEGIN
RETURN (select COUNT(*)
   from JobMetrics WITH(NOLOCK)
      where
      ((StartTime < @endDate) and (StartTime > DATEADD(HOUR,-3,@startDate)))
       )
END
GO
-- test case
DECLARE @q int
exec @q = NumberProcessedInTimeSpan '2014-11-11 22:01:00',  '2014-11-11 22:11:00'
print @q

Control of Flow

IF(condition)
  BEGIN
  ...
  END
ELSE
  BEGIN
  ...
  END

Example:

  IF (SELECT COUNT(member_no) 
      FROM loan
      WHERE member_no = @param) <= 4
  RETURN 1
  ELSE
  RETURN 2
GO

WHILE

Syntax WHILE condition BEGIN ... END BREAK/CONTINUE

Example of simple WHILE statement

set nocount on
declare @i int SELECT @i = 0
while (@i < 10)
   BEGIN
   SELECT 'i = ' + convert(char(5),@i)
   SELECT @i = @i + 1
   END 
set nocount off

Code to insert 3000 rows into the database

DECLARE @i INT
SET @i=10000
WHILE(@i <= 30000)
   BEGIN
   INSERT INTO mytable (date, thread, level, logger, server, userid, message) 
   VALUES
   (GETDATE(),'0000','INFO','XYZ1','LogFile',@i,'my message')
   SET @i=@i+1
END

Delete rows from a table in small batches like slicing a salami.

Using the WHILE statement this stored procedure deletes records older than a specific number of days. Deleting all the rows can cause the database to freeze or the query can timeout.

-- trims table to only keep specific number of previous days in table which must have a datetime field named 'dtime'
-- example:  EXEC sd_trimTable 'practice', 50
--           this would remove all rows older than 50 days in slices of 1000
IF OBJECTPROPERTY(object_id('dbo.sd_trimTable'), N'IsProcedure') = 1
   DROP PROCEDURE dbo.sd_trimTable
GO
CREATE PROCEDURE sd_trimTable @tableName char(128), @trimDays int
AS
   DECLARE @trimDate datetime
   DECLARE @cmd char(240)
   DECLARE @counter int
   SET @trimDate = DATEADD(day,-@trimDays,GETDATE())
   SET @counter = 1
   SET @cmd = 'DELETE TOP (1000) FROM '+ (@tableName)+'  WHERE dtime <  ''' + CONVERT(VARCHAR(10), @trimDate, 111) + ''''
   SELECT @cmd
   WHILE 1 = 1
      BEGIN
        EXEC (@cmd)
        IF @@ROWCOUNT = 0
          BREAK
        WAITFOR DELAY '00:00:00.2' --give other processes time to access table
    END
GO

Example to show space used by each table

DECLARE @next VARCHAR(30)
SELECT @next = ' '  --Makes it "not null"
WHILE @next IS NOT NULL
BEGIN
  SELECT @next = MIN(name)
  FROM sysobjects
  WHERE type = 'U'  --User Tables
  AND name > @next
  ORDER BY name
  EXECUTE sp_spaceused @next
END

RETURN used for 1 non-null integer

CREATE PROCEDURE checkmem
  @param INT
AS
  IF (SELECT COUNT(member_no) 
      FROM loan
      WHERE member_no = @param) <= 4
  RETURN 1
  ELSE
  RETURN 2
GO
To use this construct:
DECLARE @ret INT
EXECUTE @ret = checkmem 100

Misc commands

Example to create many databases

set nocount on
declare @i int SELECT @i = 2
declare @si char(1)
while (@i < 5)
   BEGIN
   SELECT @si = convert(char(1),@i)
   SELECT 'i = ' + convert(char(5),@i)
   exec('CREATE DATABASE db'+@si+' ON sndat = 1 LOG ON snlog = 1')
   SELECT @i = @i + 1
   END 
set nocount off

How to prevent division by zero errors
Use the NULLIF function. If @total is zero, NULL is returned for the division operation.
```
  SELECT @mydate as 'Date Time',@total AS 'total', @completes AS 'Number of completes', 
        @completes*100/NULLIF(@total,0)
```
You can also set Sql Server to just report divide by zero problems as 0
```
SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF
```

To see which programs are connecting to your sql server instance.

SELECT program_name, count(*)
FROM
Master..sysprocesses
WHERE ecid=0
GROUP BY program_name
ORDER BY count(*) desc

To count how many processes are blocked

SELECT count(*) from master..sysprocesses 
WHERE blocked <> 0

To find processes blocked for more than 6 minutes (360000 millisecs)

SELECT * from master..sysprocesses WHERE blocked <> 0 and waittime > 360000

To get information on which jobs are blocked and by whom

SELECT 
    p1.SPID AS blockedSPID, p2.SPID AS blockingSPID
,p1.kpid, p1.blocked, db_name(p1.dbid) AS 'db_name', p1.status,
p1.waittime, p1.lastwaittype,p1.cpu, p1.physical_io, p1.memusage,
p1.login_time, p1.last_batch
FROM master..sysprocesses p1
    JOIN
    master..sysprocesses p2 ON p1.blocked = p2.spid

How to trim down the size of log files in MSSQL 2008
This is dangerous for production machines, but for development boxes where you really don't care about recovery this should reduce the log size.
```
ALTER DATABASE [MyDatabaseName]  SET RECOVERY SIMPLE
go
BACKUP log [MyDatabaseName] WITH TRUNCATE_ONLY
go
DBCC SHRINKDATABASE ([MyDatabaseName], 10, TRUNCATEONLY)
go
```

How to see all collations in a database

SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT null

How to write the SQL to change all collation sequences

from Paul Deen, http://members.chello.nl/p.deen7/knowledgebase/sql_change_collation.htm

SELECT 'Alter Table [' + table_catalog + '].[' + table_schema + '].[' + table_name + '] alter column [' +  column_name + '] ' + case data_type when 'char' then 'char' when 'varchar' then 'varchar' when 'text' then 'text' end + case when data_type <>'text' then '(' + convert(varchar,character_maximum_length) + ')' else '' end + ' collate SQL_Latin1_General_CP1_CI_AS ' + case when is_nullable='YES' then 'NULL ' else 'NOT NULL' end
FROM INFORMATION_SCHEMA.COLUMNS
WHERE collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
AND data_type in ('char', 'varchar', 'text')
AND table_name in (select table_name from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE')

How To Obtain The Size Of All Tables In A SQL Server Database

From http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
SELECT *
FROM   #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
DROP TABLE #t

Print the current time immediately in seconds

we use RAISERROR since it's supposably not buffered

DECLARE @str char(240)
SELECT @str = convert(varchar(19), getdate(), 121)
RAISERROR('Trimming 100,000 rows at %s', 0, 1, @str) WITH NOWAIT

sp_tables

sp_tables -- to show a list of tables in a database:
-- or
SELECT name FROM sysobjects WHERE type = 'u' -- to see the user tables only

Change the size of varchar column
You may need to add the 'NOT NULL' option if your column is an indexed column
```
ALTER TABLE MyTableName ALTER COLUMN MyColumnName varchar(32)
```

Show all constraints of type DEFAULT_CONSTRAINT

SELECT * FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND type_desc = 'DEFAULT_CONSTRAINT'

Read more than 256 characters from an ntext field

OK, there's the right way to do this with pointer and READTEXT, but you can cheat and use the SUBSTRING command to read from an ntext field

SELECT TOP 1000
       SUBSTRING([TextData],1,255)
      ,SUBSTRING([TextData],256,400)
      ,[Duration]/1000 as durationMs
  FROM [zzzzz].[dbo].[SDWhistory_1103]
  where cpu is not NULL and textdata is not null and cpu > 0
  order by duration desc

Modifying column properties with constraints

Sometimes you can't modify a column if it has a constraint. First you must drop the constraint, then modify the column, then re-add the constraint.

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SampleSources]') AND name = N'PK_SampleSources')
   ALTER TABLE [dbo].[SampleSources] DROP CONSTRAINT [PK_SampleSources]
alter table SampleSources Alter Column name varchar(65)  NOT NULL
/****** Object:  Index [PK_SampleSources]    Script Date: 08/06/2010 09:11:22 ******/
ALTER TABLE [dbo].[SampleSources] ADD  CONSTRAINT [PK_SampleSources] PRIMARY KEY CLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Working with parameterized SQL

You have to declare and set the variable

declare @id int 
select @id = 4140
SELECT userid FROM mytable WHERE userid > @id ORDER BY userid

loop thru all the tables in a database and UPDATE their Statistics

set nocount on
declare @next varchar(30)
SELECT @next = ' '
while @next IS NOT NULL
 BEGIN
 SELECT @next = MIN(name) FROM sysobjects WHERE type = 'u' and name > @next
 SELECT 'next is ' + @next
  IF @next IS NOT null
     BEGIN
           EXECUTE ('UPDATE STATISTICS ' + @next)
     END
 END

set nocount on

Loop through all the tables in a database and print the number of rows

set nocount on
declare @tablename varchar(90)
declare @mycount varchar(90)
SELECT @mycount = ' '
SELECT @tablename = ' '
while @tablename IS NOT NULL
 BEGIN
 SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename
 IF @tablename IS NOT null
     BEGIN
           SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename
           execute (@mycount)
      END
 END

loop thru all the databases and print the name. (Useful for performing operations on each database).

set nocount on
use master
declare @next varchar(30)
SELECT @next = ' '
while @next IS NOT NULL
 BEGIN
 SELECT @next = MIN(name) FROM sysdatabases WHERE dbid > 5 AND name > @next
 --SELECT 'next is ' + @next
  IF @next IS NOT null
     BEGIN
           SELECT 'Looking at database',@next
           END
 END

set nocount on

RAISERROR

This writes a message to the console and event log

RAISERROR('** this is my message',16,1) WITH LOG
RAISERROR('** this is my message',servity,state) WITH LOG

--error serverity 1-16 ok, 17-18 serious, 19-25 fatal

Adding Space to a Database: Add 1 MEG:

ALTER DATABASE library ON library_dev1 = 1 -- MEG to increase by

To increase size of log file:

ALTER DATABASE library ON logdevname = size in 2k

To make the log file less verbose

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

      PRINT 'this is a print'
      PRINT 'SELECT sorta does the same thing, but not quite, sometimes you need a PRINT'

The PRINT statement is buffered. To get your output immediately (unfortunately there's no FLUSH command) you can use RAISERROR instead.

        set @msg = convert(char(25),GETDATE()) + ': my cool message'
        RAISERROR ( @msg,0,1) WITH NOWAIT

CASE statement

-- SIMPLE CASE EXPRESSION
SELECT Category = 
   CASE type
           WHEN 'popular_comp' THEN 'Popular Computing'
           WHEN 'mod_cook'     THEN 'Modern Cooking'
           WHEN 'business'     THEN 'Business'
           WHEN 'psychology'   THEN 'Psychology'
     WHEN 'trad_cook'    THEN 'Traditional Cooking'
           ELSE 'Not Yet Categorized'
         END,
       CONVERT(varchar(30), title) AS 'Shortened Title',
       Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY type

SEARCHED CASE EXPRESSION

SELECT m.member_no, 
       m.lastname,
       'Member Type' = 
          CASE 
            WHEN m.member_no IN
              (SELECT member_no 
         FROM juvenile j
               WHERE j.member_no = m.member_no)
      THEN 'Juvenile'
            ELSE 'Adult'
          END,
       expr_date
FROM member m, adult a, juvenile j
WHERE j.adult_member_no = a.member_no
AND (m.member_no = a.member_no OR m.member_no = j.member_no)
AND expr_date < GETDATE()
ORDER BY m.member_no

BuiltIn Functions

Aggregate Functions: SUM, AVG, MIN, MAX, and COUNT example:

SELECT SUM(ytd_sales) FROM titles

to get the number of something:
   SELECT COUNT(*) FROM Datagroup WHERE Language = 'ENU'
COUNT([ALL|DISTINCT]expression) -- note: count can have options 


   - GROUP BY
   - COMPUTE or COMPUTE BY
   - WITH CUBE or WITH ROLLUP

Example of COMPUTE

SELECT respondents.surveyName,
       min(minValue) as 'desired min age', 
       min(a1) as 'actual min age', 
       abs(min(minValue) - min(a1)) as 'diff min age',
       max(maxValue) as 'desired max age', 
       max(a1) as 'actual max age', 
       abs(max(maxValue) - max(a1)) as 'diff max age',
      (min(minValue)+max(maxValue))/2  as 'desired avg age' ,
       avg(a1) as 'actual avg age',
       abs((min(minValue)+max(maxValue))/2 - avg(a1)) as 'diff avg age',
       abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+ abs((min(minValue) + 
       max(maxValue))/2 - avg(a1)) as 'Total Skew'
from respondents join surveyDirectorQuotas on surveyDirectorQuotas.surveyName = 
     respondents.surveyName 
--where respondents.surveyName = 'EClientE2'
 and quotaQuestion = '_age'
group by respondents.surveyName 
COMPUTE count(respondents.surveyName),
sum(abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+ 
   abs((min(minValue)+max(maxValue))/2 - avg(a1)))

NULLIF (expr1,epr2) returns null if the two expressions are equal

SELECT AVG(NULLIF(royaltyper, 100))
       AS 'Average Shared Royalty %'
FROM titleauthor

COLESCE(expr1,expr2,...exprN) -- returns first non-null value
ISNULL
Wonderful function that returns a specified value (in this example -1) if the query returns null.
```
SELECT ISNULL( (SELECT userid FROM billing WHERE pid = @pid) ,-1)
```

Misc

/* What database is now open? */
SELECT "Open Database is:",DB_NAME()

/* What is it's unique database identifier? */
SELECT "Database ID is:",DB_ID()

/* What is the current host machine name? */
SELECT "Host Name is:",HOST_NAME()

/* What is the login name of the current user? */
SELECT "Login Name is:", SUSER_NAME()

/* What is the database user name of the current user? */
SELECT "DB User Name is:",USER_NAME()

ARITHMETIC OPERATORS

SELECT price, 
       (price * 1.1) AS 'New Price', 
       title
FROM titles

--Round to 2 decimals
SELECT price, 
       CONVERT(MONEY, (price * 1.1)) AS 'New Price', 
       title
FROM titles

MATHEMATICAL FUNCTIONS

ABS (numeric_expr)  Absolute value of the numeric
CEILING (numeric_expr)  Smallest integer greater than or equal to
                          the numeric expression. 
COS (float_expr)  Trigonometric cosine of the specified angle (in radians)
RAND  ([seed])  Random approximate numeric (float) value between 0 and 1,
ROUND (numeric_expr, length)  Numeric expression rounded off to the
                        length (or precision)

Example:

SELECT price,
       FLOOR(price) AS 'Floor Price',
       CEILING(price) AS 'Ceiling Price'
FROM titles

TRANSACTIONS

BEGIN TRANSACTION -- forces all or none of the following commands
INSERT ...
INSERT ...
IF()
  ROLLBACK TRANSACTION
COMMIT TRANSACTION

Using @@error

IF @@error <> 0 BEGIN ROLLBACK TRAN
RETURN END

LOCKS

SELECT * FROM authors (nolock)  -- will read even locked pages

SET TRANSACTion isolation level READ UNCOMMITTED -- lets reader look
SELECT * FROM authors

sp_tableoption tablename,'INSERT row lock', true -- to turn on InsertRowLock

String Functions

Misc

CONVERT (type, var)     Converts types (eg, convert(real,'12.5'))
RTRIM (char_expr) Removes trailing blanks.
LOWER (char_expr) Converts uppercase character data to lowercase.
LTRIM (char_expr) Removes leading blanks.
SUBSTRING(expression, start, length)  Returns part of a
               character or binary string.
STUFF (char_expr1, start, length, char_expr2) Deletes length
               characters FROM char_expr1 at start and then inserts
               char_expr2 into char_expr1 at start. 
UPPER (char_expr) Converts lowercase character data to uppercase.

Examples:

SELECT 'The price for ' + 
CONVERT(char(38), title) -- doing a substring but appends spaces to make it 38
+ ' is $' + CONVERT(varchar(10), price) FROM titles

SELECT fname + ' ' + lname + ' was hired on ' +
       CONVERT(char(8), hire_date, 1)
FROM employee

SELECT LOWER(lastname + SUBSTRING(firstname,1,2)) FROM member WHERE lastname = 'ANDERSON'

--Round to 2 decimals
SELECT price, 
       CONVERT(MONEY, (price * 1.1)) AS 'New Price', 
       title
FROM titles

-- shows last name, authors first initial
SELECT au_lname + ', ' + SUBSTRING(au_fname,1,1) + '.' AS 'Name',
       au_id
FROM authors

Dates

/* What is the current date and time? */
SELECT 'Current date and time is',GETDATE()

/* What is a week from today? */
SELECT 'A week from today is',DATEADD(WEEK,1,GETDATE())

/* How many days between now and end of century? */
SELECT 'Days until Year 2000 is',DATEDIFF(DAY,GETDATE(),'01/01/2000')

/* What is the name of the current month? */
SELECT 'Name of this month is',DATENAME(MONTH,GETDATE())

/* What is the name of the day for today? */
SELECT 'Day of the week is',DATENAME(WEEKDAY,GETDATE())

/* What is the day of the month? */
SELECT 'Current day of month is',DATEPART(DAY, GETDATE())

/* get date differences in seconds */
SELECT userID, DATEDIFF(second, dtime , getdate()) FROM respondents

How to sum counts per day into a total. Sometimes you have a field you want to find the total for a day, like how many invoices did we send each day of this last year. Here's and example of getting a count for every day of the year

SELECT datepart(year,dtime) AS 'year',datepart(month,dtime) AS 'month',datepart(day,dtime) AS 'day', count(*) AS 'count' FROM sdRespondents
GROUP BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
ORDER BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)

How to get 5 or 10 minute intervals

declare @minutes int 
set @minutes = 5
select GETUTCDATE(), 
   DATEPART( hour, dtime) as hour, 
   (DATEPART(minute,dtime) / @minutes) * @minutes as '5 min interval', 
machineName,COUNT(machineName) as employees from employees with(nolock)
where dtime > dateadd(day,-1,getutcdate())
group by  machineName,DATEPART( hour, dtime),(DATEPART(minute,dtime) / @minutes)
order by DATEPART( hour, dtime), (DATEPART(minute,dtime) / @minutes),machineName

How to fill leading zeros

This gives just one date column, so it can be graphed easier

SELECT CONVERT(char(4),datepart(year,dtime))+'-'+ 
       RIGHT('00'+CONVERT(varchar,datepart(month,dtime)),2)+'-'+
       RIGHT('00'+CONVERT(varchar,datepart(day,dtime)),2) AS 'date', count(*) AS 'count' 
  FROM myTable WITH(NOLOCK)
  WHERE dtime > DATEADD(day,-365,GETUTCDATE())
GROUP BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
ORDER BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)

Produces:

date             count
---------- -----------
2011-12-29          76
2011-12-30         277
2011-12-31         121
...

How to import a CSV file into a SQL table?

BULK INSERT MyTableName FROM 'c:\MyCsvFile.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

ORDER BY

ORDER BY sorts results sets

example:

SELECT column_name, COUNT(column_name) as mycount
   FROM table_name ORDER BY mycount [ASC | DESC]

ORDER BY can have mulitple arguments

  SELECT colA,colB,colC FROM table1 ORDER BY colB, colC

and you can add in the direction of the sort

  SELECT colA,colB,colC FROM table1 ORDER BY colB ASC, colC DESC

ORDER BY will also take a number specifying which columns to sort by. In this case the second and third, so it would be colB and colC.
```
  SELECT colA,colB,colC FROM table1 ORDER BY 2, 3
```

example:

SELECT member_no,loan=count(member_no)
FROM loanhist
GROUP BY member_no
HAVING count(member_no) > 50

member_no loan        
--------- ----------- 
2         442         
3         130         
4         104         
5         260         
6         78

Another Example

SELECT member_no,
       date=loanhist.out_date,
       fine=loanhist.fine_assessed,
       paid=loanhist.fine_paid
FROM loanhist
WHERE (member_no BETWEEN 100 AND 150) AND (fine_assessed - ISNULL(fine_paid,0) > 0)
ORDER BY member_no
COMPUTE sum(fine_paid), sum(fine_assessed) BY member_no

JOINs - We are all family here

Joins allow you to retrieve columns from two or more tables usually within the same database, but also among different databases.

Two types of JOINs ANSI - Join Operators: INNER JOIN, CROSS JOIN, LEFT/RIGHT/FULL OUTER JOIN SQL Server - Join Operators: =,<>, *= and =*

Examples of SQL Server

SELECT pub_name, title
FROM titles
INNER JOIN publishers  --Note: INNER is optional
ON titles.pub_id = publishers.pub_id

SELECT pub_name, title
FROM titles, publishers
WHERE titles.pub_id = publishers.pub_id

Join Types and estimated usage from Joe Temborius:

% use  Join Types
90     Inner
0      Cross
6      Outer
1      Self
3      Union

Example of ANSI INNER JOIN

SELECT stor_name, qty, title
FROM titles
INNER JOIN sales
ON titles.title_id = sales.title_id
INNER JOIN stores
ON stores.stor_id = sales.stor_id

Example of multiple ANSI INNER JOIN

SELECT co.isbn,
       co.copy_no,
       co.on_loan,
       ti.title,
       ti.author,
       it.language,
       it.cover,
       it.loanable
FROM title ti
       INNER JOIN copy co
       ON ti.title_no = co.title_no
       INNER JOIN item it
       ON it.title_no = co.title_no
WHERE
       co.isbn = 1500 OR co.ISBN = 1000
ORDER BY co.isbn

Example of LEFT OUTER JOIN

SELECT member.member_no,
       lastname,firstname,middleinitial,
       isbn,
       log_date
FROM member LEFT OUTER JOIN reservation
 ON member.member_no = reservation.member_no
WHERE member.member_no = 340 OR member.member_no = 341
ORDER BY member.member_no

Example of SQLServer Join

SELECT member_no , count(*) FROM reservation 
WHERE isbn IN 
(SELECT item.isbn 
FROM title 
JOIN item 
ON title.title_no = item.title_no
WHERE title LIKE "%Programmer%")
group by member_no

A small example of joins given:

CREATE TABLE table1
(
 mykey     int NOT NULL ,
 letter1    char(1) NOT NULL,
)
INSERT INTO table1 (mykey,letter1) VALUES (1,'a')
INSERT INTO table1 (mykey,letter1) VALUES (2,'b')
INSERT INTO table1 (mykey,letter1) VALUES (3,'c')

CREATE TABLE table2
(
 mykey     int NOT NULL ,
 letter2    char(1) NOT NULL,
)
INSERT INTO table2 (mykey,letter2) VALUES (1,'d')
INSERT INTO table2 (mykey,letter2) VALUES (2,'e')
INSERT INTO table2 (mykey,letter2) VALUES (4,'g')

which creates two tables:

TABLE: table1
mykey [int(10)]	letter1 [char(1)]
1	a
2	b
3	c

TABLE: table2
mykey [int(10)]	letter2 [char(1)]
1	d
2	e
4	g

More Examples of JOINs

INNER JOIN - connects only the common rows.

SELECT table1.mykey,table1.letter1,table2.letter2
FROM table1
INNER JOIN table2
ON table1.mykey = table2.mykey
which produces:
mykey       letter1 letter2 
----------- ------- ------- 
1           a       d       
2           b       e

CROSS JOIN - creates the cartesian product (all possible combinations)

SELECT table1.mykey,table1.letter1,table2.letter2
FROM table1
CROSS JOIN table2


which produces:
mykey       letter1 letter2 
----------- ------- ------- 
1           a       d       
1           a       e       
1           a       g       
2           b       d       
2           b       e       
2           b       g       
3           c       d       
3           c       e       
3           c       g

OUTER JOIN - three types

LEFT OUTER JOIN - selects all valid rows from the first table

SELECT table1.mykey,table1.letter1,table2.letter2
FROM table1
LEFT OUTER JOIN table2
ON table1.mykey = table2.mykey

RIGHT OUTER JOIN - selects all valid rows from the second table

SELECT table1.mykey,table1.letter1,table2.letter2
FROM table1
RIGHT OUTER JOIN table2
ON table1.mykey = table2.mykey

mykey       letter1 letter2 
----------- ------- ------- 
1           a       d       
2           b       e       
(null)      (null)  g

FULL OUTER JOIN - selects all matching rows from both

SELECT table1.mykey,table1.letter1,table2.letter2
FROM table1
FULL OUTER JOIN table2
ON table1.mykey = table2.mykey

mykey       letter1 letter2 
----------- ------- ------- 
1           a       d       
2           b       e       
3           c       (null)  
(null)      (null)  g

Multiple OUTER JOINs

CREATE TABLE table3
(
 mykey     int NOT NULL ,
 letter3    char(1) NOT NULL,
)
INSERT INTO table3 (mykey,letter3) VALUES (1,'d')
INSERT INTO table3 (mykey,letter3) VALUES (2,'e')
INSERT INTO table3 (mykey,letter3) VALUES (5,'h')

Then we execute,

SELECT table1.mykey,table1.letter1,table2.letter2,table3.letter3
FROM table1
FULL OUTER JOIN table2 ON table1.mykey = table2.mykey
FULL OUTER JOIN table3 ON table1.mykey = table3.mykey

Which yields:

mykey       letter1 letter2 letter3 
----------- ------- ------- ------- 
1           a       d       d       
2           b       e       e       
3           c       (null)  (null)  
(null)      (null)  g       (null)  
(null)      (null)  (null)  h

JOIN on NULL

SQL Server considers a NULL to be ambiguous, so one NULL is not the same as another NULL. You can force a JOIN if you know the NULLs should match by using something like "(c.color = p.color OR (c.color IS NULL AND p.color IS NULL))".

CREATE TABLE colors (
  team      varchar(16) NOT NULL,
  color     varchar(32) NULL,
)
CREATE TABLE points (
  color     varchar(32) NULL,
  points    int
)

INSERT INTO colors VALUES ('lobsters','red')
INSERT INTO colors VALUES ('swans','white')
INSERT INTO colors VALUES ('jellyfish',NULL)

INSERT INTO points VALUES ('red',100)
INSERT INTO points VALUES ('white',90)
INSERT INTO points VALUES (NULL,80)
SELECT * FROM colors c JOIN points p ON c.color = p.color
-- returns:
--lobsters         red                              red                              100
--swans            white                            white                            90
SELECT * FROM colors c JOIN points p ON (c.color = p.color OR (c.color IS NULL AND p.color IS NULL))
-- returns:
--lobsters         red                              red                              100
--swans            white                            white                            90
--jellyfish        NULL                             NULL                             80

Subqueries

Three ways to connect queries with subqueries
1. =,>,< ...
2. IN
3. EXISTS

Example 1

--This is a 'classic' subquery.
--Show all titles that have a price 
--larger than average of all titles.
USE pubs
SELECT price, title
FROM titles
WHERE price >
(SELECT AVG(price) FROM titles)

Example 2

SELECT title_id AS Title,
       qty AS Quantity,
       (SELECT SUM(qty) FROM sales) AS 'Total Sales',
       (CONVERT(money,qty)/(SELECT SUM(qty) FROM sales))*100 
       AS '% of Total'
FROM sales

Example 3

SELECT title_id, title
FROM titles
WHERE title_id IN
(SELECT title_id FROM sales)

Triggers

Triggers are bits of code that are executed when an operation occurs on a table. Triggers can be set for INSERT,UPDATE, or DELETE

Example

  CREATE TRIGGER deltrig
  ON emp
  FOR DELETE
AS
  DECLARE @currentuser VARCHAR(30)
  SELECT @currentuser = 'Sorry, ' + USER_NAME()
  PRINT @currentuser
  PRINT 'Employees can''t be deleted!'
  ROLLBACK TRANSACTION
GO

Example - get info about a trigger

SELECT name, crdate 
FROM sysobjects
WHERE type = 'TR'

EXECUTE sp_helptext deltrig -- show code, if not encrypted

EXECUTE sp_depends deltrig -- what tables does it affect

drop trigger IF EXISTS ( SELECT name FROM sysobjects WHERE
type = 'TR' AND name = 'member_insert' ) DROP TRIGGER
member_insert

an INSERT trigger creates a special table, called 'inserted' with the inserted row DELETE triggers create 'deleted' with the deleted rows These tables can be queried.
to create your own messages -- sp_addmessage (>50000)
sp_configure 'nested triggers',0 -- prevents cascading triggers

Example:

CREATE TRIGGER member_insert
    ON member
    FOR INSERT
AS
/* 
** Don't allow duplicate primary keys.
** This is normally done via a unique 
** index rather than a trigger.  It is
** included here just as an example.     
*/

IF (SELECT count(*)
    FROM member, inserted
    WHERE member.member_no = inserted.member_no) > 1
BEGIN
    RAISERROR ('Transaction will not be processed. Two members cannot have the same member_no.',10,1)
    ROLLBACK TRANSACTION
END
ELSE

Example:

/*
** Auto generate a primary key value, if needed
** (A zero got in there because it is the default; 
** ie, the user did not enter a member number.)    
** Works only if one row at a time is being added. 
*/

IF (SELECT count(*)
    FROM member
    WHERE member.member_no = 0) > 0
BEGIN
    UPDATE member
        SET member_no = (SELECT max(member_no) FROM member) + 1
    WHERE member_no = 0
END
GO

Data Integrity/IDENTITY

An identity forces SQL server to have a unique value in a field. The starting value and the increment value can be set as follows

CREATE TABLE stores2
(
  stor_id  int identity(1000,10) NOT NULL , -- starts at 1000, increments by 10
  stor_name varchar (40) NULL ,
)

To see the values of these,

SELECT IDENT_SEED('class'), IDENT_INCR('class') -- shows initial identity and increment

to set directly an IDENTITY field (for testing etc to override defaults)

   SET IDENTITY_INSERT [TableName] ON

Example:

   SET IDENTITY_INSERT Pubs..class ON -- allows an identity to be inserted

IDENTITYCOL is a keyword which substitues for the name of the identity column

SELECT MAX(IDENTITYCOL) FROM stores2 
-- identitycol is a keyword refering to a tables' identity column

-- Defining constraints

Primary Key
Unique
Foreign Key
Default
Check

in syscomments a field called 'text' contains your constraits
sp_helpconstraint or sp_help <tablename> -- more friendly

Comments

Comments are surrounded with '/*' and '*/', but cannot, for some
bizzare reason contain a 'GO' command.

Everything after a '--' is a comment

CONSTRAINTs

Constraints can be set on the value of valid fields.

CREATE TABLE Tbl_lvl ( 
ItemCode CHAR(6) NOT NULL, 
UnitCost SMALLMONEY NOT NULL, 
UnitPrice SMALLMONEY NOT NULL, 
CONSTRAINT pricediff
CHECK (UnitPrice > UnitCost) ) GO

Constraints can be named

CREATE TABLE Payroll
(
 Empno CHAR(3) NOT NULL,

 Checkno CHAR(10) NOT NULL
 CONSTRAINT U_Checkno
    UNIQUE NONCLUSTERED (checkno),

 Paydate SMALLDATETIME NOT NULL
 CONSTRAINT DF_Paydate
    DEFAULT GETDATE(),
 
 Amount SMALLMONEY NOT NULL 
 CONSTRAINT CK_Amount
    CHECK (Amount BETWEEN 10000 AND 100000),

 CONSTRAINT PK_Payroll
    PRIMARY KEY CLUSTERED
    (empno,checkno,paydate)
    WITH FILLFACTOR=25,
 CONSTRAINT FK_Payroll
    FOREIGN KEY (empno) REFERENCES emp (empno)
)
-- must drop constraint before index
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'member_ident')
  ALTER TABLE member
    DROP CONSTRAINT member_ident

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'member_ident')
    DROP INDEX  member.member_ident

ALTER TABLE member
  ADD CONSTRAINT member_ident PRIMARY KEY CLUSTERED (member_no) WITH FILLFACTOR = 90

How to specify a cascading deletion
When the source of the foreign key is deleted, this row will also be deleted automagically if you append "ON DELETE CASCADE". This is called a Cascade Delete
```
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [FK_MyTable_name__MainTable_name] 
   FOREIGN KEY ([name]) REFERENCES [MainTable] (name) ON DELETE CASCADE
```

Constraints can set a default value:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'state_default')
  ALTER TABLE adult
    DROP CONSTRAINT state_default
GO
ALTER TABLE adult
  ADD CONSTRAINT state_default DEFAULT 'WA' FOR state
GO

to add a primary key restaint:

alter table mytable ADD primary key (mykeyid, myothercolumn)

Views

CREATE VIEW authorlist
(AuthorName, Location)
AS
SELECT SUBSTRING(au_fname + ' ' + au_lname,1,25),
       SUBSTRING(city + ', ' + state + '  ' + zip,1,25)
FROM authors
GO

SELECT * FROM authorlist

--
-- see your views:
SELECT name, crdate 
FROM sysobjects
WHERE type = 'V'

-- to see your source code for the view
EXECUTE sp_helptext AuthorList

EXECUTE sp_depends AuthorList -- list tables needed for this view


CREATE VIEW Managers
WITH ENCRYPTION
AS
SELECT empno, empname, title 
FROM emp 
WHERE title='MANAGER'
WITH CHECK OPTION -- updates/inserts into this view must satisfy WHERE clause
GO

SELECT * FROM managers

sp_helptext managers

--This should fail the CHECK
INSERT INTO managers 
VALUES ('999', 'GATES, BILL', 'MCT') -- fails because MCT != MANAGER

What Version of SQLServer do I have and what Service Packs does my SQL Server have installed?
try
SELECT @@version

CREATE VIEW with JOIN

create view summary
  as
  select RuleId, Stakeholder, ProductType, Language, LanguageCode, MeasureName, Assessment, AcceptanceThreshold 
     from Rules r 
     join Measures m on r.MeasureID = m.MeasureId
     join Types t on t.TypeID = r.TypeID
     join Languages l on l.TableSuffix = t.Language

OBJECT_ID()

How to conditionally delete a view

-- old style
IF EXISTS ( SELECT name FROM sysobjects
            WHERE type = 'V' AND name = 'stuff' )
     DROP VIEW stuff
-- new style
IF OBJECT_ID('stuff', 'V') IS NOT NULL 
    DROP VIEW stuff  
go
-- another way
  IF OBJECTPROPERTY(object_id('dbo.stuff'), N'IsView') = 1
     drop view stuff
  go

How to conditionally delete a table

IF OBJECT_ID('traffic_data', 'U') IS NOT NULL 
    DROP TABLE traffic_data
GO

Common second parameters to OBJECT_ID()

/*
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
FN = SQL scalar function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
*/

How to drop a foreign key constraint

ALTER TABLE [UserGroupResolutions] DROP CONSTRAINT FK_UserGroupResolutions_groupName_Groups_name

Isolation levels via Table Hints
If the results of your queries do not always have to be perfect, you can increase query speed and reduce contention by using "READ UNCOMMITTED" or "WITH(NOLOCK)", which does not lock a table during a SELECT and allows "dirty" reads. This is dangerous, but in certain situations can be advantageous.
```
SELECT COUNT(*) FROM dbo.Authors WITH(NOLOCK) 
```
This will not lock the table and make the query faster.

Use the following command to make an entire session use no locking
```
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
```

INDEXs

CREATE INDEX [CLUSTERED|NONCLUSTERED] name ON table (col1,col2,...)

EXECUTE sp_helpindex emp shows info on index

CNTRL -- skips to next heading
limit: composite index length < 900 chars

SET SHOWPLAN ON -- debug statement to show if indexes are being used
GO
SELECT * 
FROM authors 
WHERE city = 'Kent'   --table scan

SELECT * 
FROM authors 
WHERE au_id = '123-45-6789' --clustered index
SET SHOWPLAN OFF
GO
--

--------------
-- OPTIMIZER HINTS
--------------
SET SHOWPLAN ON
GO
SELECT au_lname, au_fname, phone
FROM authors  (INDEX = 1) -- INDEX=0 is none, INDEX=indexname
WHERE au_lname = 'Smith'
SET SHOWPLAN OFF
GO

--------------
-- UPDATE STATISTICS -- updates statistics that the optimizer uses 
to determine which index to use on a query
--------------

--Plug in a value for TABLENAME

SELECT 'Index Name' = sysindexes.name, 
       'Statistics Date' = STATS_DATE(sysindexes.id, sysindexes.indid)
FROM sysobjects, sysindexes
WHERE sysobjects.name = 'authors' 
AND sysobjects.id = sysindexes.id

UPDATE STATISTICS authors

-- show fragmentation
DECLARE @tbl INT
SELECT @tbl = OBJECT_ID('sales')
DBCC SHOWCONTIG (@tbl)

------------- check databases
DBCC CHECKDB   -- should be run once a week on all database, 
               -- should be done before a backup

--
daily - dump database, update statistics on all indexes
hourly - dump transaction log
weekly - drop and recreate indexes
-- show performance data
SET SHOWPLAN ON -- shows indexes being used
SET STATISTICS IO ON 
SET STATISTICS TIME ON --

-- drop an index if it exists
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'reserve_item_link')
    DROP INDEX  reservation.reserve_item_link
GO

-- example of showing the elapsed time
DECLARE @message char(255)  DECLARE @began datetime  SELECT @began = GETDATE()

CREATE UNIQUE  INDEX reserve_ident ON reservation (member_no, isbn)
       WITH FILLFACTOR = 75

CREATE INDEX reserve_item_link ON reservation (isbn)

SELECT @message = 'Time (in minutes:seconds) to create reservation related indexes.  '
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
     + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
PRINT @message
GO

System Variables

SELECT 'Connections:    ',@@connections
SELECT 'CPU Busy        ',@@cpu_busy
SELECT 'Cursor Rows     ',@@cursor_rows
SELECT 'DBTS            ',@@dbts
SELECT 'Error           ',@@error
SELECT 'Fetch Status    ',@@fetch_status
SELECT 'Identity        ',@@identity
SELECT 'Idle            ',@@idle    
SELECT 'IO Busy         ',@@io_busy    
SELECT 'Language ID     ',@@langid 
SELECT 'Language        ',@@language
SELECT 'Max Connections ',@@max_connections
SELECT 'Max Precision   ',@@max_precision
SELECT 'MS Version      ',@@microsoftversion
SELECT 'Nest Level      ',@@nestlevel
SELECT 'Options         ',@@options
SELECT 'Pack Received   ',@@pack_received
SELECT 'Pack Sent       ',@@pack_sent
SELECT 'Packet Errors   ',@@packet_errors
SELECT 'Procedure ID    ',@@procid
SELECT 'Row Count       ',@@rowcount -- how many rows were effected on last operation
SELECT 'Server Name     ',@@servername
SELECT 'Service Name    ',@@servicename
SELECT 'SPID            ',@@spid
SELECT 'Text Size       ',@@textsize
SELECT 'Time Ticks      ',@@timeticks
SELECT 'Total Errors    ',@@total_errors
SELECT 'Total Read      ',@@total_read
SELECT 'Total Write     ',@@total_write
SELECT 'Tran Count      ',@@trancount
SELECT 'Version         ',@@version

BCP bulk copy

rem
rem   BCPDEMO.BAT
rem
rem   This batch file exports all AUTHORS FROM the
rem   PUBS database to an ASCII file called RESULTS.TXT
rem 
rem   /c indicates 'character' format vs. /n for 'native' format
rem   /t indicates the field terminator (default is TAB)
rem   /S indicates the Server to connect to
rem   /U indicates the Login ID
rem   /P is the password (if any)
rem
rem   Type in BCP with no options to see parameter summary
rem 
rem   After you run this, go into NOTEPAD and 
rem   check out the results in 'results.txt'
rem
rem   *** CHANGE SERVER NAME BELOW BEFORE RUNNING ***

bcp pubs.dbo.authors out results.txt /c /t"|" /Sstudentx /Usa /P

EXTENDED STORED PROCEDURES

--Display all 'registered' ESP's
EXECUTE sp_helpextendedproc

-- to get the last inserted identity
INSERT Respondents (test) VALUES ('N') SELECT @@IDENTITY 


---- misc xp_*
USE master
GO

--Display all devices
EXECUTE xp_cmdshell 'dir c:\mssql\data\*.dat /os'
PRINT ''

--Send a message to the NT Event Log
EXECUTE xp_logevent 60000, 'Stop sign - worst kind!', error
PRINT ''

--Show current NT accounts that can access SQL Server
EXECUTE xp_logininfo
PRINT ''

--Display current SQL Server version and build info
EXECUTE xp_msver
PRINT ''

--Display all local NT groups
EXECUTE xp_enumgroups

UNDOCUMENTED ESP's

--XP_REGREAD allows you to read Registry Key values

SET NOCOUNT ON
GO

DECLARE @org VARCHAR(50)
DECLARE @own VARCHAR(50)

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
                'RegisteredOrganization',@param = @org output

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
                'RegisteredOwner',@param = @own output

PRINT 'Whoever set up SQL Server for this class typed these in:'
PRINT ' '
SELECT 'Registered Organization is '+UPPER(@org)
SELECT 'Registered Owner is        '+UPPER(@own)

SET NOCOUNT OFF
GO
-->

CURSORS

Cursors allow an entire result set to be captured and manipulted as a single piece of data.

******************************************************-
DECLARE @currentvalue char(32)
DECLARE mycursor CURSOR FOR
  SELECT distinct myvalue FROM mytable
OPEN mycursor
FETCH NEXT FROM mycursor INTO @currentvalue 
WHILE (@@fetch_status = 0)      -- while OK
  BEGIN
        print @currentvalue
        exec('SELECT mycolumn FROM ' + @currentvalue ) -- some useful work
  FETCH NEXT FROM mycursor INTO @currentvalue
  END
CLOSE mycursor
DEALLOCATE mycursor
GO


******************************************************-

--Count the number of members that have
--a 'J' as a middle initial value

USE Library

SET NOCOUNT ON

DECLARE @mivalue char(1)
DECLARE @counter int
DECLARE @strcounter char(50)

SELECT @counter=0

DECLARE micount CURSOR FOR
SELECT middleinitial FROM member

OPEN micount

FETCH NEXT FROM micount 
INTO @mivalue   -- move to first row

WHILE (@@fetch_status = 0)      -- while OK
  BEGIN
  
  IF @mivalue = 'J' SELECT @counter = @counter + 1
  
  FETCH NEXT FROM micount
  INTO @mivalue

  END

CLOSE micount

DEALLOCATE micount

SELECT @strcounter = 'Number of J initials:' +CONVERT(char(10),@counter)
PRINT @strcounter
GO

-- RAISERROR
sp_addmessage 55555, 16, 'Mitchs test message',us_english ,true 

RAISERROR(55555,16,1)

Convert a database from local time to utc time.

First we find all constraints defining the default value for the column to be "GETDATE()". Then we drop those constraints, and add the default constraint "GETUTCDATE()" instead. Secondly we go through all the columns and adjust their time from local to UTC.

/* Converts all fields with a default value containing "GETDATE()" to "GETUTCDATE()" and updates existing times to utc */
set nocount on
declare @tableName varchar(255)
declare @columnName varchar(255)
declare @constraintName varchar(255)
declare @newConstraintName varchar(255)
declare @myDropCmd varchar(512)
declare @myCreateCmd varchar(512)
declare @timeZoneCommand varchar(512)
declare @timeZoneOffset int

select @timeZoneOffset = Datediff(hh,GETDATE(),GETUTCDATE())

-- original base query from 'MeanOldDBA' at 
-- http://www.developmentnow.com/g/113_2004_9_0_0_433030/Search-for-defaults-in-table-DDL.htm
DECLARE mycursor CURSOR FOR 
   SELECT
   so1.name AS table_name,
   sc1.name AS column_name,
   so2.name AS default_name
   FROM
   sysobjects so1
   INNER JOIN syscolumns sc1 ON so1.id = sc1.id
   INNER JOIN sysobjects so2 ON sc1.cdefault = so2.id
   INNER JOIN syscomments sc2 ON so2.id = sc2.id
   WHERE
   sc2.text LIKE '%getdate()%'
OPEN mycursor
   FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
   WHILE (@@fetch_status = 0)      -- while OK
     BEGIN
        print '-- Table: ' + @tableName + ', Constraint: ' + @constraintName+ ', Column: ' + @columnName
        SELECT @myDropCmd = '  ALTER TABLE ['+@tableName+'] DROP CONSTRAINT ['+@constraintName+']'
        PRINT @myDropCmd
        EXECUTE (@myDropCmd)
        SELECT @newConstraintName = 'DF_'+@tableName+'_dtime_utc'
        SELECT @myCreateCmd = '  ALTER TABLE ['+@tableName+'] ADD CONSTRAINT ['+@newConstraintName+'] DEFAULT (GETUTCDATE()) FOR ['+@columnName+']'
        PRINT @myCreateCmd 
        EXECUTE (@myCreateCmd)
        select @timeZoneCommand =  '  UPDATE ' + @tableName + ' SET ' + @columnName + ' = DATEADD(HOUR, '+convert(varchar(3),@timeZoneOffset)+', '+@columnName+')'
		PRINT @timeZoneCommand
		EXECUTE (@timeZoneCommand)

        FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
     END
CLOSE mycursor
DEALLOCATE mycursor
GO

Misc Tips

When SQLServer UPDATEs a row, it really deletes i replaces it with a new one

SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue 
WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14

How to convert a varchar column to nvarchar?
You can convert a column in place.
```
ALTER TABLE MyTableName ALTER COLUMN [MyColumnName] NVARCHAR(3000)
```

Finding gaps in a sequence

This finds any non-consecutive numbers in the userID column.

SELECT A.userID + 1
FROM SDRespondents AS A
WHERE NOT EXISTS (
    SELECT B.userID FROM SDRespondents AS B 
    WHERE A.userID + 1 = B.userID)
GROUP BY A.userID;

Set a database to single use only.

This is useful in simulating database failure to stop general access to a database.

EXECUTE sp_dboption library, 'dbo use only', TRUE -- dbo user only
EXECUTE sp_dboption library, 'dbo use only', FALSE

 alter database myDatabaseName set SINGLE_USER WITH ROLLBACK IMMEDIATE
 alter database myDatabaseName set MULTI_USER

User Defined Datatypes (UDDT)

  EXECUTE sp_addtype zipcode, 'char(10)'
  EXECUTE sp_addtype phonenumber, 'char(13)', NULL
  EXECUTE sp_droptype <typename>  -- to get rid of it

to get a list of UDDT

  sp_help

example:

CREATE TABLE member (
member_no member_no NOT NULL, -- member_no is a User Defined Data Type
lastname shortstring NOT NULL,
firstname shortstring NOT NULL,
middleinitial letter NULL,
photograph image NULL
)

USE Master
EXECUTE sp_dboption mydb, 'trunc. log on chkpt.',true
to set SELECT into/bulkcopy
EXECUTE sp_dboption mydb, 'SELECT into/bulkcopy',true

chars are treated as varchar's if NULL is a possible value.

Binary Large OBject - BLOB

SET NOCOUNT ON    --suppress 'rows affected' msg
SET NOCOUNT OFF

Logic don't use "= NULL" use "IS NULL" because its ANSI NULLs fail all comparisons "NULL = NULL" is false Order of Presedence: (),NOT,AND,OR
the EXECUTE command can be used to build commands from parts

Example 1

create proc displaytable
@tbl varchar(30)
as
EXECUTE ('SELECT * FROM ' + @tbl)

Example 2

SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
SELECT @cmd
EXECUTE (@cmd)

Terms:
A batch is a set of one or more SQL statements submitted together and executed as a single group. A script is a series of one or more batches submitted one after the other. A script is a file, with a default extension of .SQL Comparison operators (=,<>,>,<,>=,<=), BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL

The System supplied databases.

Database	Function
Master:	Controls all user databases and SQL Server as a whole
Model:	Serves as a template when creating new user databases
Msdb:	Provides support for the SQL Executive service
Tempdb:	Used for temporary working storage for all connections
pubs:	A sample database

2. 
   Allocation Unit: 1/2MB increments of database storage space
   Extent: Eight 2K pages of table or index storage space
   Page: The basic unit of I/O in SQL Server (2K in size)

Cursors

--deleteViews
-- This deletes all views which start with an X
-- trivial example of using cursors in sql server 6.5
IF EXISTS ( SELECT name FROM sysobjects
            WHERE type = 'P' AND name = 'deleteViews' )
    DROP PROCEDURE deleteViews
go
******************----------------
GO

CREATE PROCEDURE deleteViews
@databasename varchar(30)
AS
DECLARE @viewname SYSNAME,
        @sqls CHAR(255),
        @fullviewname CHAR(255)

SELECT @sqls = 'DECLARE view_cursor CURSOR FOR SELECT name FROM '
+ @databasename + '..sysobjects WHERE type = ''v'' and name LIKE ''X%'' '

--  

exec(@sqls)

OPEN view_cursor
FETCH NEXT FROM view_cursor INTO @viewname
WHILE (@@FETCH_STATUS=0)
BEGIN
    SELECT @fullviewname = @databasename + '..' + @viewname
    SELECT 'current viewname is ' + @fullviewname
    --EXEC ('DROP VIEW ' + @fullviewname)

    FETCH NEXT FROM view_cursor INTO @viewname  
END
CLOSE view_cursor
DEALLOCATE view_cursor
GO

Get all the column names in a table

SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
where name = 'mycolumn')

Easy way is to access meta data is the 'information_schema' view:
```
SELECT * FROM information_schema.columns
```

How to restore a database FROM a .BAK file

RESTORE FILELISTONLY 
    FROM DISK = 
    'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
RESTORE DATABASE SDRespondents
   FROM DISK = 
    'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
   WITH MOVE 'SDRespondents_Data' TO 
     'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
   MOVE 'SDRespondents_log' TO 
     'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
GO

Restoring database in Sql Server 2008 from thumb drive on E:

It's more concise

RESTORE DATABASE [MY_DB_NAME] FROM  DISK = N'E:MY_DB_NAME.bak' 
  WITH  FILE = 1,  NOUNLOAD,  STATS = 10

How to retrieve data as xml
Use "FOR XML AUTO" or "FOR XML EXPLICIT"
```
SELECT * FROM sdgroups FOR XML AUTO
```
How to increase the timeout setting
When you get an exception like this:
```
Exception: System.Net.WebException Message: The operation has timed out
```
You need to increase the timeout setting by opening Enterprise Manager, right clicking on your server and select "Properties", then "Connections", then set "Remote query timeout" to be something larger.

Connection Strings

Bad example where password is embedded (but occassionally useful for testing on dev boxes)

Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName

Real solution where password is not in connection string

Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName

Working with Devices
Devices are containers for databases and their logs.
1. Creating Devices
```
DISK INIT creates devices:
To create data devices you must be in master.

DISK INIT name='LIBRARY_DEV1',
 physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
 vdevno = 100, -- must be unique, use sp_helpdevice()
               -- to find currently used device_number(s)
 size = 10240  -- in 2K blocks
 DISK INIT
 name='LIBRLOG_DEV2',
 physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
 vdevno = 101,
 size = 4096
```
2. Resizing Devices -- you can only increase the size of a device, never shrink DISK RESIZE name='MASTER', SIZE=15360 -- size is in 2k pages
3. Deleting Devices Note: When using EntepriseManager to drop a device, it does not drop the .DAT file, instead use: sp_dropdevice logical_name [, DELFILE] -- deletes .DAT file too

Globally Unique Identifier - GUID

GUIDs are 16 byte binary integers created to be unique identifiers across database instances.

Create a GUID

SELECT newid()

Produces:

B3A15B59-AD75-4D8B-8888-0D839C84C301

An example

We create the first row by using newid() to have sqlserver create the GUID for us, the second row we create by feeding it a GUID directly.

CREATE TABLE guidpractice
(
 guid      UNIQUEIDENTIFIER,
 fname     VARCHAR(20)   NOT NULL,
 lname     VARCHAR(30)   NOT NULL,
)
insert guidpractice (guid,fname,lname) values (newid(),'Adam','Smith')
insert guidpractice (guid,fname,lname) values ('857CFD44-8BB4-4D05-AEF1-22B62142A7FF','Adam','Smith')
select * from guidpractice

Produces:

DA4414FD-7178-4DE2-8C77-86817B04ECF8	Adam	Smith
857CFD44-8BB4-4D05-AEF1-22B62142A7FF	Adam	Smith

You can have sqlserver create Guids by default

CREATE TABLE guidpractice2
(
 guid      UNIQUEIDENTIFIER DEFAULT newid(),
 fname     VARCHAR(20)   NOT NULL,
 lname     VARCHAR(30)   NOT NULL
)
GO
INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
SELECT * FROM guidpractice2

Produces:

guid                                 fname                lname
------------------------------------ -------------------- ------
D6A672EB-39A5-42E9-92C6-0C7DD0F9324D Adam                 Smith
609876C7-92A4-4D78-8393-19D72904F194 Adam                 Smith

Misc SQL Stuff

when SQLServer UPDATEs a row, it really deletes it and replaces it with a new one

DATEDIFF

SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue 
WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14

Using local variables

You can define local variables with the "@" sign. These are used in parameterized sql commands.

DECLARE @lang varchar(12)
set @lang = 'en-UK'
SELECT name from SDTextDefinitions 
WHERE text LIKE '%buy%' and lang=@lang

Using LIKE with a scalar variable and wildcards

You need to preface it with the wildcards in separate strings like this

DECLARE @lang varchar(12)
DECLARE @searcher varchar(12)
set @lang = 'en-UK'
set @searcher = 'buy'
SELECT name from SDTextDefinitions 
WHERE text LIKE '%'+@searcher+'%' and lang=@lang

Example of declaring a date variable and using DATEADD

DECLARE @mydate datetime
SET @mydate = '2010-11-11 00:00:00.00'
SELECT count(time) AS 'number', avg(time) AS 'time(ms)' FROM myTable 
    WITH (nolock)
    WHERE 
    dtime > @mydate AND dtime < DATEADD(day,1,@mydate)

Example of printing the date with milliseconds (ISO8601)

set @msg = convert(char(25),GETDATE(),126) -- msg = 2011-11-07T13:08:41.933

Wait, pause, sleep command

  WAITFOR DELAY '00:00:59' -- sleeps for 59 seconds

SELECT @cmd = "create database "+@projabbrev+" ON "+ 
@datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
SELECT @cmd
EXECUTE (@cmd)

Get all the column names in a table

SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
where name = 'mycolumn')

Easy way is to access meta data is the 'information_schema' view:
```
SELECT * FROM information_schema.columns
```

How to restore a database FROM a .BAK file

RESTORE FILELISTONLY 
    FROM DISK = 
    'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'

The above command will show two rows. The database name and the log file name are in the first column, 'LogicalName'. These should be inserted below for 'SDRespondents_Data' and 'SDRespondents_log' respectively.

RESTORE DATABASE SDRespondents
   FROM DISK = 
    'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
   WITH MOVE 'SDRespondents_Data' TO 
     'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
   MOVE 'SDRespondents_log' TO 
     'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
GO

How to see all active requests

SELECT start_time, [text], status, total_elapsed_time,
		 DB_Name(database_id), blocking_session_id, wait_type,
		 wait_time, cpu_time, command,
		 logical_reads, text_size, row_count, session_id
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S
WHERE session_id <> @@SPID;

How to retrieve data as xml
Use "FOR XML AUTO" or "FOR XML EXPLICIT"
```
SELECT * FROM sdgroups FOR XML AUTO
```

How to select fields that are only letters and numbers?

Since SQL doesn't have a isAlphaOrNumber() function you can use this user function from our friends at simple-talk.com

IF OBJECT_ID(N'IsAlnum') IS NOT NULL
   DROP FUNCTION IsAlnum
GO
CREATE FUNCTION dbo.[IsAlnum] (@string VARCHAR(MAX))  
/*
Select dbo.isalnum('how many times must I tell you')
Select dbo.isalnum('345rtp')
Select dbo.isalnum('co10?')
*/
RETURNS INT
AS BEGIN
      RETURN CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @string) > 0 THEN 0
                  ELSE 1
             END
   END
GO
  

create table test  (Description varchar(255))
insert into test (Description) VALUES ('imok')
insert into test (Description) VALUES ('imok2')
insert into test (Description) VALUES ('i''mNot ok')

select * from test where dbo.IsAlnum(Description) = 1 /* selects only rows containing only letters and numbers */

How to validate a sql statement before executing it?
One option is to user FMTONLY, but it's rumored to cause false negatives.
```
SET FMTONLY ON
-- lets test the next statement
select * from test2
SET FMTONLY OFF

Msg 208, Level 16, State 1, Line 5
Invalid object name 'test2'.
```
You can also use "SET PARSEONLY ON" and "SET PARSEONLY OFF" , but this just checks syntax, not if a tablename is misspelled.

Connection Strings

Bad example where password is embedded (but occassionally useful for testing on dev boxes)

Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName

Real solution where password is not in connection string

Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName

Working with Devices
Devices are containers for databases and their logs.
1. Creating Devices
```
DISK INIT creates devices:
To create data devices you must be in master.

DISK INIT name='LIBRARY_DEV1',
 physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
 vdevno = 100, -- must be unique, use sp_helpdevice()
               -- to find currently used device_number(s)
 size = 10240  -- in 2K blocks
 DISK INIT
 name='LIBRLOG_DEV2',
 physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
 vdevno = 101,
 size = 4096
```
2. Resizing Devices -- you can only increase the size of a device, never shrink DISK RESIZE name='MASTER', SIZE=15360 -- size is in 2k pages
3. Deleting Devices Note: When using EntepriseManager to drop a device, it does not drop the .DAT file, instead use: sp_dropdevice logical_name [, DELFILE] -- deletes .DAT file too
Resetting a user's password
```
sp_password 'myusername', 'my+stro'
```

THE END
A SQL query walks into a bar, approaches two tables, and says, "Mind if I join you?"