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

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




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

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

4. COLESCE(expr1,expr2,...exprN) -- returns first non-null value
5. 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)
   

6. 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()
   

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

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

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

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

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

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

5. How to import a CSV file into a SQL table?

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

ORDER BY sorts results sets

1. example:

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

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

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

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

5. 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 allow you to retrieve columns from two or more tables usually within the same database, but also among different databases.

1. Two types of JOINs ANSI - Join Operators: INNER JOIN, CROSS JOIN, LEFT/RIGHT/FULL OUTER JOIN SQL Server - Join Operators: =,<>, *= and =*
2. 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
   

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

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

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

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

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

8. More Examples of JOINs
   1. 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       
      

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

   3. OUTER JOIN - three types
      1. 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
         

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

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

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

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

1. Three ways to connect queries with subqueries
   1. =,>,< ...
   2. IN
   3. EXISTS
2. 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)
   

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

4. Example 3

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

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

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

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

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

4. 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.
5. to create your own messages -- sp_addmessage (>50000)
6. sp_configure 'nested triggers',0 -- prevents cascading triggers
7. 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
   

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

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

   SET IDENTITY_INSERT [TableName] ON

Example:

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

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

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

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)

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

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

2. How to conditionally delete a table

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

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

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

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)

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

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

2. How to convert a varchar column to nvarchar?

   You can convert a column in place.

   ALTER TABLE MyTableName ALTER COLUMN [MyColumnName] NVARCHAR(3000)
   

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

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

   or

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

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

6. 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
7. the EXECUTE command can be used to build commands from parts
8. Example 1

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

9. Example 2

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

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

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

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

13. Get all the column names in a table
    

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

14. Easy way is to access meta data is the 'information_schema' view:
    

    SELECT * FROM information_schema.columns
    

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

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

17. How to retrieve data as xml

    Use "FOR XML AUTO" or "FOR XML EXPLICIT"

    SELECT * FROM sdgroups FOR XML AUTO
    

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

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

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

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

1. Create a GUID

   SELECT newid()
   

   Produces:

   B3A15B59-AD75-4D8B-8888-0D839C84C301
   

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

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

1. when SQLServer UPDATEs a row, it really deletes it and replaces it with a new one
2. DATEDIFF

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

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

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

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

6. Example of printing the date with milliseconds (ISO8601)

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

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

8. Get all the column names in a table
   

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

9. Easy way is to access meta data is the 'information_schema' view:
   

   SELECT * FROM information_schema.columns
   

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

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

12. How to retrieve data as xml

    Use "FOR XML AUTO" or "FOR XML EXPLICIT"

    SELECT * FROM sdgroups FOR XML AUTO
    

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

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

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

16. 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
17. Resetting a user's password

    sp_password 'myusername', 'my+stro'