Learn SQL Server basics in this tutorial for beginners new to SQL Server, please find below few basic topics and features.

Last updated on December 26, 2016

1. Working with Databases
   1. Create new Database:

       
      CREATE DATABASE NewTestDatabase
      

   2. Delete Database:

       
      DROP DATABASE database_name [, database_name...]
      

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

      
      
      
   4. Working With Tables

      TCRUD - Create, Read, Update, and Delete

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

            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

         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. Delete 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
   5. Working With Rows
      1. Inserting Rows into a Table
         1. 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)
            

         2. add multiple rows to a table

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

         3. add multiple rows to a table with stored procedure

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

         4. INSERT rows from this database into a table in another database

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

      2. UPDATEing Rows in a Table
         1. UPDATE changes the values in an existing row

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

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

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

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

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

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

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

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

   7. SQL Server Results
      1. 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           
         
         

      2. Retrieving Results
         1. General to get unique values:

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

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

         3. to join two tables the SQLServer way:

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

         4. to get info from two tables

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

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

         
         
         
      4. how to preface info in a column with a string, 'x-',
         

         UPDATE mytable SET mycolumn = 'x-'+mycolumn
         

      5. 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 '%[_]%'
         
         

      6. Advanced Topics in Retrieving Results
         1. 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
            

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

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

   8. Stored Procedures
      1. General syntax of stored procedure

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

      2. Declaring Variables

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

      3. Simple Example

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

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

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

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

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

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

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

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

   10. Control of Flow
       1. Control of Flow
          1. IF

             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
             
             

          2. WHILE
             1. Syntax
                WHILE condition BEGIN ... END BREAK/CONTINUE
             2. 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
                

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

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

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

       2. Misc commands
          1. 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
             
             

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

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

          4. To count how many processes are blocked

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

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

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

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

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

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

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

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

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

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

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

       4. Show all constraints of type DEFAULT_CONSTRAINT

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

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

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

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

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

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

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

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

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

       13. To make the log file less verbose

           ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
           

       14. PRINT

                 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
           

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

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

Please find below few useful links which are explained in detail about "SQL SERVER".

Sql Server Central
Free educational web site covering SQL concepts.

Sql Tutorial
Free educational web site covering SQL concepts and clauses

Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours, Third Edition presents the key features of SQL (Structured Query Language) in an easy to understand format with updated code examples, notes, diagrams, exercises, and quizzes.

SQL Tutorial - Learn SQL
This SQL tutorial will teach you how to use commonly used SQL commands so you will be able to apply most of the knowledge gathered from this SQL tutorial to many different databases.

Introduction to Structured Query Language
This page is a tutorial of the Structured Query Language (also known as SQL).

A Gentle Introduction to SQL
Interactive SQL tutorial. SQL Server, Oracle, MySQL, PostgreSQL and Access. SELECT statements including joins and functions.

Sql Course
Interactive/On-line SQL Tutorial with SQL Interpreter & live practice database.

W3Schools
In our SQL tutorial you will learn how to use SQL to access, define, and manipulate the data in a database system, like Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Access.

SQL Tutorial
This SQL Tutorial site is aimed to teach beginners the building blocks of SQL.

SQL Tutorial
A short sql tutorial that covers the basic commands.

SQLCourse2.com
This unique SQL Tutorial is the sequel to the highly successful SQLCourse.com site and will provide you with more advanced easy-to-follow SQL Instruction and the ability to practice what you learn on-line with immediate feedback.

SQL Tutorial
This document is a general tutorial on the database sublanguage - SQL.

SQL Syntax
This document is written for users who wish to run their own queries of the database.