Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Thursday, 12 June 2014

SQL Coding Guidelines - II

n  When ALIAS name is given to the table/ view, then use the ALIAS consistently in the query.
n  Do not mix aliases and table name in the query. And do not use the table name as alias in the query.
n  LIKE operation should NOT be done with INTEGER data type in the WHERE clause.
n  Avoid the usage of HAVING clause wherever possible as it uses a worktable.
n  DATE data type should not be compared with INTEGER data type, i.e., before converting.
n  Always pass the date format parameter to the CONVERT function while converting a string into DATETIME.
n  E.g., @var1 = CONVERT(datetime,@char_date1,120)
n  Always TRIM the variable, if a DATE is converted into a CHARACTER data type. Store the converted value in to a temporary variable instead of inline comparison
n  E.g., @var1 = ltrim(rtrim(CONVERT(varchar(10),@date1,120)))
n  STAR (*) should not be given in result set.
     E.g.
                SELECT * FROM table1
    Here column names should be specified explicitly.
n  For E.g. :
                      SELECT * from Customer can be written as
                     
                                SELECT CustomerID, CustomerFirstName, City                   FROM Customer
n  This technique results in reduced disk I/O and better performance.
n  Do not return different result sets from the same stored procedure.
n  In UNION/ UNION ALL statements ensure that the corresponding selected columns match in DATATYPE.
n  Use UNION ALL when selecting Record sets which are mutually Exclusive instead of UNION.
n  As UNION internally creates a worktable to sort and select distinct rows. This will impact performance.
n  table1 (col1 int, col2 int, col3 char(25), col4 char(4))
n  table2 (col11 int, col21 int, col31 char(25), col41 int)
n  Wrong Union query – col4 and col41 are not same data type
n  SELECT col1, col2, col3, col4
n  FROM table1
n  UNION
n  SELECT col11, col21, col31, col41
n  FROM table1
n  ALIAS name should be given to the right of the column.
                E.g. SELECT col1 function, col2 position
n  Cursors should not be used.
n  Use Temporary tables, instead of cursors.
n  Order by, Distinct clause to be used minimally.
e.g.,  Index on Employee_family table is empid, Srl_no, family_member_name
        Not Recommended – Sort is unnecessary as the index covers the sort keys. Sort would create a temporary table and physically sort the rows which is a performance overhead on query
                 
              SELECT   empid,ename , family_member_name
               FROM Employee_family
               WHERE   empid  =   ‘E0010’
                                      AND srl_no   = 1
               AND family_member_name =   ‘Sudha’
                                     ORDER BY empid, Srl_no, family_member_name
 
n  DISTINCT clause should be sparingly used
n  Many times DISTINCT is used because of bad query which is returning multiple result rows
–        Wrong query
SELECT DISTINCT emp_name, dept_name, total_salary
FROM employee, employee_salary, dept
WHERE employee.dept =  dept.dept
AND employee.dept = ‘HR’
AND employee.empid between 1000 and 2000
–        Above query would come out with multiple rows for same employee
–        Problem is cartition product because ‘employee’ table and ‘employee_salary’ table are not joined on empid
 
n  Avoid Using IF NOT EXISTS.
      Not Recommended
           IF NOT EXISTS    (  SELECT  1
                                             FROM customer
                                         )
           BEGIN
                                       ….. -- Logic Incorporated
           END
       Recommended
                     IF EXISTS    (  SELECT  1
                                     FROM customer
                                )
           BEGIN
                                       select @tmp = @tmp   -- Do not incorporate logic here                                            unless it requires
           END
           ELSE
           BEGIN
                          ……   -- Logic Incorporated
           END
 
n  Use @@rowcount to minimize the SELECT on the tables and IF EXISTS checks.
For Ex : Consider the sample BR given below.
  1. Given Part Pricelist should exists in the table else throw error
Status of the part pricelist has to be "fresh" else throw error

n  The Optimal code  would be as follows :
SELECT @Ppl_status = ppl_status
FROM  
WHERE ppl_no =             @ppl_no
               
 
IF @@ROWCOUNT = 0
BEGIN
                --Throw error for BR 1.
                RAISERROR("Given Part Pricelist does not exists in the table")
                RETURN              
END
 
IF @Ppl_status <> 'FRESH'
BEGIN
                --Throw error for BR 2.
                RAISERROR("Part Pricelist is not in Fresh status, can not modify")          
                RETURN              
END


n  Do Not add dummy select statements in the Stored procedure. For debugging take a copy of the original procedure, rename the procedure and debug.

n  For Header fetch and header refresh procedures, there should be NO JOINS in the final select statement, all values have to be fetched in variables and these variables have to be used in the Final select statement.
n  For Ex : For selecting the Part description at the header level, don't make a join with the Prt_PrCrl_CentralRefList_Info table in the final select. Get the Part no from the main table ( like CWO header table) into a variable @part_no.
                From the table Prt_PrCrl_CentralRefList_Info, select the part descriprion into a variable @part_desc for the value in @part_no.Use @part_desc in the final select statement.

n  Whenever insert statement is used, the column names should be given explicitly.
       Not Recommended
                INSERT INTO customer
                VALUES (‘C001’,’Jene’, 24)
                Recommended
                   INSERT INTO customer                                                                                      (cust_id,cust_name,cust_age)
                VALUES (‘C001’,’Jene’, 24)
        Incase a column is added in future following this method will not raise an error

n  For Updates / Deletes on Main Table
      ( Page ) the Following checks should be done :
–        Existence Check
–        Status Check
–        Timestamp Check
–        These checks should also be done for Updates / Inserts from Link Pages Also.

n  Document number generation after completing all the checks and just before Insertion into the main table.
n  Sequencing of Checks On Input Parameters :
–        NULL Checks in the order of appearance on Screen
–        Existence Check if applicable in the Order of Appearance on screen
–        Each business rule must be executed in the sequence in which it is specified by the functional consultant

n  All the DML Operations should be done only after the validation checks been done. This will reduce the unwanted  I/O operations.
n  If the logic happened to be same across procedures, write a common procedure/function and call it in the respective procedures.
n  Avoid repeated reads on the same table, wherever possible
n  Line comments inside select statement should not be used
                Do not write
                                Select @createdate = getdate() -- System            date is stored as Creation Date
                Instead write it as
                                /*System date is stored as Creation Date*/
                                Select @createdate = getdate()
n  Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:
Not Recommended
                    SELECT LocationID FROM Locations WHERE      Specialities LIKE '%pples‘
     Recommended
       SELECT LocationID FROM Locations WHERE                   Specialities LIKE 'A%s'

n  Use Derived tables wherever possible
  e.g.,
    SELECT MIN(Salary)
FROM Employees
WHERE EmpID IN
(
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary Desc
)
   Can be written as
                SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A
The second query performs twice as fast as the first query.

n  Do not call functions repeatedly in the procedure instead assign it once to a variable and use it across.

   e.g.,
       To find the Length of a string, to find the       current date use
     
         SELECT @len_tmp = LEN(name_tmp)
                    SELECT @date_tmp = getdate()

n  ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data.
   e.g.,
     Old Join
SELECT a.au_id, t.title
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE 'Computer%'

ANSI Join
    SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE 'Computer%'

n  Usage of Alias for table names is mandatory especially while joining tables.
    Not Recommended
   SELECT first_name,       last_name,       country
   FROM   employee,       countries
   WHERE  country_id = id
   AND    lastname   = 'HALL';
                Recommended
    SELECT e.first_name,       e.last_name,       c.country
    FROM   employee e,       countries c
    WHERE  e.country_id = c.id
   AND    e.last_name  = 'HALL'


n  Tables should be joined in the order of the volume of data present in each table.
        e.g., largest table should be joined first, then the table with less data on so on.
      The WHERE clause should be framed in such a way that maximum no.of rows are filtered by the first one or two conditions.
        e.g.,
                                SELECT    *  
                                FROM   c, b, a, d
              WHERE d.name = 'JONES'
                                AND d.join_column = 12345
               AND d.join_column = a.join_column
               AND a.join_column = b.join_column
                                AND b.join_column = c.join_column


n  Do not use column numbers in the ORDER BY clause. Consider the following example in which the second query is more readable than the first one:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

n  Handling Null Columns in the table
n  Make it a practice to include ISNULL check for every NULL column in a table.
n  All NOT NULL columns in a table would have NOT NULL values
n  ‘Blank Space’ is not equal to NULL
 e.g., Here MiddleName is stored as null in customer table
  SELECT FirstName + ' ' + MiddleName + ' ' + LastName
  FROM Customers
    The result is Null here.

    Instead use IsNull or Coalesce function
  SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
  FROM Customers


Tuesday, 15 April 2014

Basic SQL


Constraints
Primary Key Constraint
Unique Key Constraint
Foreign Key Constraint
Not Null Constraint
Check Constraint

A Constraint is a property that we can assign to column in table. 
What is Primary Key?
             Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.
Right click the student table and set primary key


It is look like 

Primary key constraint doesn’t accept null values.









AUTO INCREMENT
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.








Query Level


CREATE TABLE [dbo].[tblStudent](
[StudId] [int] IDENTITY(1,1) NOT NULL,
[StudName] [varchar](20) NULL,
[Class] [int] NULL,
 CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED
(
[StudId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.
When creating the clustered index, SQL server 2008 reads the id column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand the Indexes. You will see



Unique Key Constraint
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
CREATE TABLE Persons(P_Id int NOT NULL UNIQUE,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

Foreign key constraint
A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables.
NOT NULL Constraint
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.



Check Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
EX:
CREATE TABLE brand (
    code        char(8)       NOT NULL,
    valid       int           NOT NULL,
    rowid       numeric(10,0) IDENTITY,
    CONSTRAINT brand_pk PRIMARY KEY (code),
 -- The following UNIQUE constraint lets the pair of values be
-- the target of  a foreign key reference.
    CONSTRAINT brand_is_valid UNIQUE (code, valid),
 CONSTRAINT valid_check CHECK (valid IN (0,1))
);

CREATE TABLE product (
    code        char(8)       NOT NULL,
    valid       int           NOT NULL,
 -- The column "code" is a PK in the referenced table, so this still works. It's
    -- a 1:0 or 1:1 relationship.
    CONSTRAINT product_pk PRIMARY KEY (code),
 -- The next constraint requires a unique constraint on the pair of
    -- columns in the table "brand".  By itself, it references every row
    -- in "brand". That's too many rows.
CONSTRAINT product_fk FOREIGN KEY (code, valid)
 REFERENCES brand (code, valid),
 -- But this constraint restricts the foreign key references to only those
-- rows that have valid = 1 in the table "brand".
    CHECK (valid = 1)
);


Brand Table Values



Check Constraint
Table name Brand
CONSTRAINT valid_check CHECK (valid IN (0,1))




 Example Condition :
CHECK (id BETWEEN 100 and 9999)
CHECK (name = upper(name))



The DELETE statement conflicted with the REFERENCE constraint "product_fk". The conflict occurred in database "SampleDB", table "dbo.product".
The statement has been terminated.
How to delete Brand table value?

  • First Delete product table row.
  • After Delete Brand table value.






Monday, 7 April 2014

Top in SQL SERVER 2008

The TOP keyword allows you to return the first n number of rows from a query based on the number of rows or percentage of rows that you define.
The first rows returned are also impacted by how your query is ordered.
In previous versions of SQL Server, developers used SET ROWCOUNT to limit rows returned or impacted.

We can use the top keyword with the following queries:
Select
Insert
Delete
Update
Basically, most of us know about top, we can use it in a select query to get the top element from the query.

But top can also be used in Insert, Delete and Update commands for the table as well.

We will see how we can use top with Insert, Delete and Update.



Two sample tables



1 Top with select statement

Syntax: - SELECT TOP 10 * FROM <TNAME>

Example:- 


SELECT top(1) Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]



2 Top with Insert Statement

It is when we have to insert only the top few records that we can use this:

Syntax:-
 

Insert top (10) into <TName> (column1, column2.....)
Select column1, column2..... From <TName1> 
or 
Insert into <TName> (column1, column2.....)
Select TOP (10) column1, column2..... From <TName1> 

Example:- 

INSERT TOP (2) INTO [dbo].[SampleTable1]
SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

ORDER BY Class DESC


SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]


SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable1]


 Top with Delete Statement

Deleting top records from a table:

Syntax: - Delete top (10) from <TName> where <Cond>

Example:- 


SELECT  Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]
DELETE Top(1) FROM [SampleDB].[dbo].[SampleTable]
SELECT  Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]



Top with Update statement

Updating top records from a table:

Syntax: - Update top (10) <TName> set Colmn1=<value> 

Example: - 


UPDATE TOP (1) [dbo].[SampleTable] SET Class='Hindi' WHERE Class='Msc'