SQL Server Code Examples

SQL Code Examples

DDL

Create Table

  • Its always good to specify the null or NOT NULL because different shops maintain different standards
CREATE TABLE ticket_master( 
    ticket_id INTEGER NOT NULL ,
    event_id INTEGER NULL,
    date DATETIME NULL
)
go

With PRIMARY KEY

  • Its good to give a name for every constraint in the table because we can disable it to load the bulk data to bypass the validation using enable and disable the constraints
CREATE TABLE ticket_master( 
    ticket_id INTEGER NOT NULL CONSTRAINT pk_ticket_master PRIMARY KEY,
    event_id INTEGER NULL,
    date DATETIME NULL
)
go

With Check Constraint

  • recommended to sneak in the check constraint instead of writing a front end validation or trigger to check the values
CREATE TABLE employee( 
    emp_no INTEGER NOT NULL PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    sal money CHECK ( sal > 500),
    state CHAR(2) CONSTRAINT chk_emp_state CHECK ( state IN ('TX','NY','NJ','GA')) 
)
go

Default

  • The default value will get assigned only if you do not have that column specified int the insert statement
CREATE TABLE customer(
    cust_no INTEGER NOT NULL PRIMARY KEY,
    cust_name VARCHAR(20),
    crt_id char(10) DEFAULT USER
)
go

More than one column as PRIMARY KEY

  • Good to know how to define two columns as a PRIMARY KEY. if more just keep adding the column names inside the paranthasis.
CREATE TABLE accounts(
    account_no INTEGER NOT NULL,
    act_type char(2) NOT NULL,
    name VARCHAR(25) NULL,
    address VARCHAR(40) NULL,
    CONSTRAINT pk_accounts PRIMARY KEY (account_no, act_type ) )
go

Foreign Key

  • Referential Integrity not allowing some invalid values in the child records
CREATE TABLE sales_force( 
    emp_id NOT NULL INTEGER CONSTRAINT fk_sales_force FOREIGN KEY REFERENCES employee (emp_id),
    state char(2) NOT NULL,
    phone_number CHAR(10),
    CONSTRAINT pk_sales PRIMARY KEY ( emp_id, state ) 
)
go

Alter Table add column

  • will be used when users wants to add another column for an existing structure
ALTER TABLE sales_force
ADD extension CHAR(5) NULL
go

Alter Table modify column

  • used when users want to change the property or length of the column which already exits in the database
ALTER TABLE sales_force
ALTER COLUMN extension CHAR(5) NULL
go

Alter table drop column

  • Only SQL Server 7.0 allows to drop a column from table
ALTER TABLE sales_force
DROP COLUMN extension

Enable a constraint

  • By default the constraint will be enabled
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT chk_emp_state
go

Disable a constraint

  • Usually we disable a constraint while doing the bulk inserts or updates, speeds up the process
ALTER TABLE employee NOCHECK CONSTRAINT chk_emp_state
go

Create Index

  • makes select faster
CREATE INDEX ind_salesforce ON sales_force(phone_number)
go

Create unique index

  • Used to maintain the uniqueness of one or more column
  • Cannot have two PRIMARY KEYs where as we can have more than one
  • unique indexes on a table
CREATE UNIQUE INDEX ind_name table_name(column_name)
go

Creating a copy of a table with another name

  • Usually used while copying rows from one table to the temp table in stored procedures etc
SELECT * INTO employee_copy 
FROM employee
go

Copying table structure to another with out rows

  • Just in case (interview question)
SELECT * INTO employee_copy 
FROM employee WHERE 1 = 2
go

Create a user named JON

  • DBA job
sp_addlogin JON
go

Create a Role named CUSTINFO

  • Used if application has more number of roles and used by different departments
sp_addrole 'custinfo'
go

**Include JON as CUSTINFO role

  • Once a new user joins as CUSTINFO just asssign him to the role so that maintenance is easier
sp_addrolemember 'custinfo','JON'
go

Can I rename the sales_force table?

  • Using sp_rename object_name, new_object_name
sp_rename sales_force, sales_bullet

DML

DML Statements

Select all the columns

  • do this just development and testing, do not write this type of sql in production application
SELECT * 
FROM sales_force;
go

SELECT * 
FROM ticket_master;
go

Select specific columns

  • best way to write the sql in application even when you are selecting all the columns
SELECT cust_no, cust_name 
FROM customer
WHERE state_cd = 'TX'
go

SELECT emp_no 
FROM employee
go

Concatinating two strings in sql

  • used while writing sql's for reports and displaying in a different format from the database data
SELECT cust_name + ' contact name is ' + cust_contact + ' lives in ' + cust_state
FROM customer
go

you will get the result as AtoZ Corp contact name is Bob Smith lives in CA

Concatination string and a number

  • just an example which concates the integer and a string.
SELECT 'The customer number is ' + CONVERT( CHAR, cust_no) + ' and name is ' + cust_name
FROM customer

group by

  • we should include all the columns except the agrregate function columns in the group by cluase
SELECT invoice_no, SUM(qty * price) Price, COUNT(*) Invoice_Count
FROM invoice_detail
WHERE state_cd = 'TX'
GRIYO BY invoice_no
go

SELECT cust_name, invoice.inovice_no, SUM ( invoice_detail.qty * invoice_detail.price )
FROM invoice, invoice_detail
WHERE invoice.invoice_no = invoice_detail.invoice_no
GROUP by cust_name, invoice.invoice_no
go

group by having

  • Having clause is nothing but a where clause on the rows which got seggregated by the group by clause
SELECT invoice_no, SUM(qty * price)
FROM invoice_detail
WHERE state_cd = 'TX'
GROUP by invoice_no
HAVING count(*) > 4
go

SELECT invoice.cust_name, invoice.invoice_no, SUM ( invoice_detail.qty * invoice_detail.price )
FROM invoice, invoice_detail
WHERE invoice.invoice_no = invoice_detail.invoice_no
GROUP BY cust_name, invoice.invoice_no
HAVING SUM( invoice_detail.qty * invoice_detail.price ) BETWEEN 20000 AND 40000
go

Range Queries

  • is nothing but between clause, we can also say a particular column > some thing and < something
SELECT invoice_detail.invoice_no, COUNT(*) Line_Items
FROM invoice, invoice_detail
WHERE invoice.invoice_no = inovice_detail.invoice_no
AND invoice.invoice_date BETWEEN getdate() - 90 AND getdate()
GROUP BY invoice_detail.invoice_no
go

Sub Query

  • inner most query executes first and then feeds the result set to the where clause for the outer query
SELECT cust_no, cust_name
FROM invoice
WHERE invoice_no in ( SELECT invoice_no FROM invoice_detail WHERE item_id = 7890 )
go

Correlated Sub Query

  • First the outer query executes, then for each row the inner query gets executed.
SELECT cust_no, cust_name
FROM invoice i
WHERE EXISTS (SELECT invoice_no FROM invoice_details id WHERE i.invoice_no = id.invoice_no )

Outer Joins

  • displaying matching and unmatching records
SELECT a.customer_no, customer_name
FROM customer a FULL OUTER JOIN invoice b
WHERE a.customer_no =* b.customer_no

Like statement
--used in searches. Like if you know the book name starts with JAVA and dont know the whole title then we can use like so that we will get all books in JAVA and pick the one you wanted

SELECT cust_no, cust_name
FROM customer
WHERE cust_name LIKE 'JAVA%'
go

Existence of data

SELECT customer_no, customer_name
FROM customer
WHERE EXISTS
(SELECT 1 FROM invoice WHERE invoice.customer_no = customer.customer_no )

Using CASE

SELECT customer_no, customer_name,
CASE WHEN credit_allowed = 'Y' then 'Yes' ELSE 'No' END, phone_number
FROM
customers;

AND, OR, NOT IN

SELECT customer_no, customer_name, phone_number
FROM customer
WHERE state_cd = 'TX' OR state_code = 'AR';

SELECT customer_no, customer_name, phone_number
FROM customer
WHERE state_cd = 'TX' AND credit_limit > 5000;

SELECT customer_no, customer_name, phone_number
FROM customer
WHERE state_cd NOT IN ( 'TX','AR');

Table Aliases

SELECT cust.customer_no, cust.customer_name, SUM(inv.invoice_amt)
FROM customer cust, invoice inv
WHERE cust.customer_no = inv.customer_no

Insert into Syntax1 ( all the columns provided )

INSERT INTO sales_force
VALUES ( 1001, 'TX', '9728909000')
go

Insert into Syntax2 ( columns specified, recommended )

INSERT INTO sales_force(emp_id, state, phone_number)
VALUES (1002, 'CA', '3031237890')
go

Insert into Select... (Multi row insert)

INSERT INTO sales_force 
SELECT * FROM sales_forceold
go

Insert into Select with a subquery

INSERT INTO sales_force
SELECT * FROM sales_forceold
WHERE state IN ( SELECT state FROM master_state WHERE state_status_Code = 'AC' )
go

Update table

UPDATE sales_force SET phone_number = '8882348765'
WHERE emp_id = 1002
go

Update with a sub query

UPDATE sales_force SET phone_number = isnull
((SELECT phone_number from customer WHERE customer.cust_id = sales_force.cust_id), sales_force.phone_number)

Delete one or more row

DELETE sales_force WHERE state = 'CA';

Truncate the table

TRUNCATE TABLE sales_force;

Datetime functions

SELECT 
RTRIM(CONVERT(CHAR,datediff(hh,'08-04-1999', getdate())%24)) +':'+
RTRIM(CONVERT(CHAR,datediff(mm,'08-04-1999', getdate())%60)) + ':' +
RTRIM(CONVERT(CHAR,datediff(ss,'08-04-1999', getdate())%60));
SELECT DATEADD(DAY, -3, getdate());
SELECT DATEPART(hh,getdate());

Batches

TSQL Examples TSQL (Batch)

Example 1 (Simple TSQL)

DECLARE @var_run_no integer -- declare the variable ( name and data type)
SELECT @var_nun_no = 0 -- Initialize the counter
WHILE @var_run_no < 100 -- While loop
    BEGIN --Start of the loop
            SELECT @var_run_no --Display the variable ie counter
            SELECT @var_run_no = @var_run_no + 1 -- Increase the variable
    END -- End the loop

Example 2 (Simple TSQL with nocount on)

DECLARE @var_run_no integer
SET nocount on -- wont display no of rows affected
SELECT @var_run_no = 0
WHILE @var_run_no < 100
    BEGIN
        SELECT @var_run_no
        SELECT @var_run_no = @var_run_no + 1
    END

Example 3 (Creating temporary tables)

DECLARE @var_run_no integer
SET nocount on
SELECT @var_run_no = 0
CREATE TABLE #test ( a integer) --Creating a temp table to insert all the variables
WHILE @var_run_no < 100 -- While loop
    BEGIN
        INSERT INTO #test VALUES ( @var_run_no ) -- Inserting rows into the temp table
        SELECT @var_run_no = @var_run_no + 1
    END
SELECT * FROM #test --Select all the rows from temp table
DROP TABLE #test -- Drop the table which we created at the beginning.

Example 4 (Embedded SQL)

USE invoice --make the invoice database as the current database
GO
DECLARE @amount_by_cust FLOAT
DECLARE @var_custno INTEGER
DECLARE @var_custname VARCHAR(20)
SELECT @var_custno = 1234 --Assign 1234 to the cust_no variable
SELECT @var_custname = cust_name FROM customer WHERE cust_no = @var_custno --Embedded SQL
IF @@rowcount = 0 -- Check the number of rows if 0 display the error
    BEGIN
        RAISERROR(-20400, 'Customer number does not exists in the database') --Raise Error function
        RETURN --Exit the batch
    END
SELECT @amount_by_cust = sum(qty * price)
FROM invoice_detail
WHERE invoice_no EXISTS(SELECT 1 
FROM invoice
WHERE cust_no = @var_custno AND invoice.invoice_no = invoice_detail.invoice_no )
SELECT 'The total amount for customer ' + @var_custname + ' is ' + CONVERT(char, @amount_by_cust) --Return the information as a row

If then Else control structure

DECLARE @var_num1, @var_num2, @var_num3 integer
SELECT @var_num1 = 10, @var_num2 = 20, @var_num3 = 15
IF @var_num1 > @var_num2 -- If statement
    BEGIN -- If true
        IF @var_num1 > @var_num3
            SELECT 'Biggest number is ' + CONVERT(char, @var_num1)
        ELSE
            SELECT 'Biggest number is ' + CONVERT (char, @var_num3)
    END
ELSE -- If false
    BEGIN
        IF @var_num2 > @var_num3
            SELECT 'Biggest number is ' + CONVERT ( char, @var_num2)
        ELSE
            SELECT 'Biggest number is ' + CONVERT (char, @var_num3)
    END --End of the If statement

Display Customer Name for cust 1010 using embedded select statement

DECLARE @cust_nm_hold VARCHAR(20)
DECLARE @varcust_no INTEGER
SELECT @varcust_no = 1010 -- Assigning the value to the variable
-- Embedded SQL should be used only if the where clause is a PRIMARY KEY( one row should be the result).
SELECT @cust_nm_hold = cust_name
FROM customer
WHERE cust_no = @cust_number
IF @@rowcount = 0
    BEGIN
        raiserror('Customer does not exists in the database', 16, 1) --Raise the error so that calling program can check
        return -- Send the control back to the calling program
END
SELECT @cust_nm_hold

Cursors

DECLARE @var_custname INTEGER
--cursor definition
DECLARE c1 CURSOR FOR SELECT cust_name FROM asset WHERE state_cd = 'TX'
OPEN c1 --Open actually executes the sql associated to the cursor
    FETCH c1 INTO @var_custname -- Fetches the current row in the cursor, by default it starts from the top row
    WHILE @@fetch_Status = 0 --Check the fetch status, if its 0 fetch returned a row from the cursor variable
        BEGIN
            SELECT @var_custname --Display the selected customer name which is in the variable
            FETCH c1 INTO @var_custname --Fetch the next row
        END
CLOSE c1 --Close the cursor
DEALLOCATE c1 --Any resource associated with the cursor gets removed

SavePoint, Commit and Rollback

DECLARE @business_success CHAR(1)
--Make the TSQL as a transaction so that we can commit or rollback using begin transaction
BEGIN transaction
--We can have as many save points in the program so that we can rollback till that point
    SAVE transaction ccc
    --insert the first row into the table
    INSERT INTO number_table VALUES ( 12, 34)
    --create another transaction named ddd
    SAVE transaction ddd
    --insert the second row
    INSERT INTO number_table values ( 13, 35)
    --The following rollback statement rolls back the transaction till the save point ddd, so what it means is after that save point definition in the TSQL batch all the operations will get rolled back
    ROLLBACK transaction ddd
COMMIT transaction

Stored Procedure

IF exists ( SELECT 1 FROM sysobjects WHERE name = 'datetime_function' )
    drop procedure datetime_function -- Drop the procedure if exists before creating
GO

CREATE PROCEDURE datetime_function( @from_date VARCHAR(30), @to_date VARCHAR(30) )
AS
DECLARE @days VARCHAR(4)
--If you declare a variable as VARCHAR with out the length then you can assign only one character to the variable. ie if you assign 'SUNDAY' to a variable @var_day VARCHAR then @var_day will have just 'S' as its value
DECLARE @var_hour VARCHAR(2)
DECLARE @var_minutes VARCHAR(2)
DECLARE @var_seconds VARCHAR(2)
IF @from_Date IS NULL
    BEGIN
        raiserror('From date cannot be null',16,1)
        RETURN
    END
IF @to_Date IS NULL
    BEGIN
        raiserror('To date cannot be null',16,1)
        RETURN
    END
-- Gets the days between 2 dates
SELECT @days = CONVERT(VARCHAR,datediff( dd, @from_date, @to_date ))
-- Gets the hours between 2 dates and % by 24 so that we will get number of horur difference
SELECT @var_hour = CONVERT(VARCHAR,( datediff( hh, @from_date, @to_date )%24) )
-- Gets the minutes from 2 dates and do a % 60 so that we will get the number of minutes with in that hour
SELECT @var_minutes = CONVERT(VARCHAR,( datediff( mm, @from_date, @to_date )%60) )
-- Gets the seconds from 2 dates and do a %60 so that we get the number of seconds with in that minute
SELECT @var_seconds = CONVERT(VARCHAR,( datediff( ss, @from_date, @to_date )%60) )
--Now concate all the VARCHAR variables so that we get the result as wanted
SELECT @days + ' days ' + @var_hour + ' hours ' + @var_minutes + ' min ' + @var_seconds + ' seconds '

Calling Stored Procedure

declare @var_date1 datetime
declare @var_date2 datetime
select @var_date1 = getdate() --Assign todays date to the variable
select @var_date2 = '04/04/2000'
exec datetime_function @var_date1, @var_date2 --Call the function so that we will get the result displayed

Procedure with OUTPUT arguments
By default the arguments for stored procedure is IN. Suppose we want to send the value back to the calling program using the arguments then we should define the argument as OUT. You can pass a value in the place of an IN parameter where as we must pass a variable in the place of IN/OUT and OUT arguments Creating Stored Procedure with OUTPUT variable
Create a procedure which takes the state_cd as the IN argument and send the no of customers as the OUTPUT parameter

CREATE PROCEDURE proc_getcustcount ( @var_statecd VARCHAR(3), @var_custcount integer OUTPUT)
as
If @var_statecd = '' or @var_statecd is null
begin
raiserror('Variable State Code cannot be null', 16, 1)
return
end
--Assign the count(*) value to the output variable. If you dont assign a value to the output variable then the variable will have either NULL value or the in coming value
Select @var_custcount = count(*)
from invoice_detail
where invoice_no in
( select invoice_no from invoice_hdr where stat_cd = @var_statecd )

Calling a procedure which has output variable in the argument list

declare @customer_count integer
declare @customer_state VARCHAR(2)
Select @customer_state = 'TX'
--While executing the stored procedure which has output variables then while calling that stored procedure we need to specify that sending variable as output other wise the program wont return the value to the calling program
exec proc_getcustcount ( @customer_state, @customer_count output)
Select 'The total number for this customer ' || convert(VARCHAR(10), @customer_count)

REF Cursor (Cursor output parameter in a stored procedure)

--We can pass a cursor back to the calling program as an output parameter
CREATE PROCEDURE get_customer_contact
@var_state_cd VARCHAR(3),
@var_cust_cursor cursor varying output --declaration of cursor should be cursor varying
as
--Assign the select statement to the cursor variable
set @var_cust_cursor = cursor for
select cust_contact from customer where state_cd = @var_state_cd
open @var_cust_cursor -- just open the cursor and end the program so that just the pointer goes back to the calling program

Calling the procedure which has ref cursor

declare c1 cursor --this is how we declare a cursor variable
declare @var_state VARCHAR(3)
declare @var_cust_name VARCHAR(20)
Select @var_state = 'TX'
Exec get_customer_contact @var_state, @c1 output -- call the stored procedure by passing the cursor variable
While @@fetch_status = 0
begin
   Fetch @c1 into @var_cust_name
   select @var_cust_name --Display customer name
end
close c1 --Close the cursor
deallocate c1 --Remove the reference of the cursor from the server
go

Create procedure with RECOMPILE

  • is used to change the execution plan when ever you execute the stored procedure. The advantage being, if particular tables goes through lots and lots of changes every day probably the execution plan wont be effective so using RECOMPILE option we can get the new execution plan according to the data.
  • This stored procedure takes two arguments and a Output cursor argument

CREATE PROCEDURE get_empbycity @city_nm VARCHAR(20), @state_nm VARCHAR(3), @empnamecursor cursor varying output
with recompile as
   --Delcare local variables to hold the where criteria for the select
   declare @var_where VARCHAR(100)
   --check for null or blank string, if so raise the error
   If (@city_nm = '' or @city_nm is null) and (@state_nm = '' or @state_nm is null )
   begin
        raiserror('Either city or state or both should be supplied', 16, 1)
        return
   end
   --If city and state variables are NOT NULL then
   If @city_nm is NOT NULL and @state_nm is NOT NULL
         Select @var_where = ' where city like ' + @city_nm + '%' + ' and state_nm like ' + @state_nm + '%'
   --If city is NOT NULL and state_nm is null
   If @city_nm is NOT NULL and ( @state_nm is null or @state_nm = '')
         Select @var_where = ' where city like ' + @city_nm + '%'
   --If state is NOT NULL and city is null
   If @state_nm is NOT NULL and ( @city_nm is null or @city_nm = '')
         Select @var_where = ' where state_nm like ' + @state_nm + '%'
   --After creating the where clause set the select to the cursor variable
        set @empnamecursor = cursor for select cust_no, cust_name from customer + @var_where
   --Open the cursor so that calling program can get the handle of this cursor
     Open @empnamecursor

Create procedure with encryption

  • is used to hide the text of the stored procedure so that after going to production no body can see the code behind the stored procedures rolled out.
  • Creating the stored procedure with encryption option

CREATE PROCEDURE history_invoice_data @var_fromdate datetime, @var_todate datetime with encryption as
--If from date is null raise an error and return to the calling program
If @var_fromdate is null
begin
raiserror('From Date cannot be null', 16, 1)
return
end
--If To date is null raise an error and return to the calling program
If @var_todate is null
begin
raiserror ('To date cannot be null, 16, 1)
return
end
--Insert the rows from invoice to invoice_history
insert into invoice_history
select * from invoice where invoice_dt between @var_fromdate and @var_todate
--If there is any error log that error in the error_log table by inserting a row
If @@error != 0
begin
insert into error_log ( log_txt, user_id, error_time ) values ( 'Error occured while historizing the invoice table', user, getdate())
return
end
--Insert the rows from invoice_detail to invoice_detail_history
insert into invoice_detail_history
Select * from invoice_detail
where invoice_no in
( select invoice_no from invoice where invoice_dt between @var_fromdate and @var_todate)
--If there are any errors log the error into error_log table
If @@error != 0
begin
insert into error_log ( log_txt, user_id, error_time ) values ( 'Error occured while historizing the invoice detail table', user, getdate())
return
end

Create Triggers - Can have any number of insert, update and delete triggers on a table ( 7.0 )

Example 1

  • Example not to do any DML operation on sunday
create trigger tr_invoice_restriction on invoice for insert, update, delete as
declare @var_weekday VARCHAR(15)
  --Get the weekday from the system
  select @var_weekday = datename(weekday,getdate())
  --Convert the uppercase to lowercase if any
  select @var_weekday = lower( @var_weekday)
  --Instead of all the variables used we can write like Select rtrim(lower(datename(weekday,getdate())))
  If rtrim(@var_weekday) = 'sunday'
  begin
     raiserror('Cannot do any changes on sunday', 15,1 )
     return
  end

Example 2

  • This is one is encrypted and not executed while replication process is doing an insert.
create trigger tr_invoice_detail on invoice_detail for insert
with encryption NOT FOR REPLICATION as
--Update the item table, reduce the item_on_hand by the qty issued
   Update item set item_on_hand = item_on_hand - inserted.qty
   from item, inserted
   where item.item_id = inserted.item_id
--Check the error, if its not ok then raise the error so that whole transaction gets killed
If @@error != 0
 begin
   raiserror('Updating items failed - tr_invoice_detail(source)', 15,1)
   return
 end

Example 3

  • Example of executing a portion of trigger only if a particular column got changed
create trigger tr_update_item on item for update
with encryption not for replication as
--declare a variable to hold the qty_on_hand value from the table
declare @varreorder integer
--If Update - checks whether that column got changed
If update(qty_on_hand)
  begin
   select @varreorder = item.reorder_lvl
   from item,inserted
   where item.item_id = inserted.item_id
  end
If inserted.item_on_hand < @varreorder
begin
        insert into orders (item_id, qty, order_date ) values ( item_id, 20, getdate() )
        If @@error != 0
           begin
             raiserror ( 'Inserting into Order table failed', 15, 1)
             return
           end
end