SQL Server System Defined Database

SQL Server 2008 and 2005 have five system defined databases: master, model, tempdb, msdb, and resource. These databases are used by SQL Server for its own operation and management. Let’s see these database use and function.

Master Database

1. Master database contains all of the System level information for Sql Server.
2. It contains all system configuration settings details for Sql Server. We can see the system configuration information by using the system defined "SYSCONFIGURES" table.
   

   
    Select * from SYSCONFIGURES 
   
   

3. It contains all existing databases details for Sql Server. We can see the database information and where the actual file persists by using the system defined "SYSDATABASES" table
   

   
    Select * from SYSDATABASES 
   
   

4. It contains all login accounts details for Sql Server. We can see the login account information by using the system defined "SYSXLOGINS" table.
   

   
    Select * from SYSXLOGINS 
   
   

5. It contains all users details for Sql Server. We can see the user information by using the system defined "SYSUSERS" table
   

   
    Select * from SYSUSERS 
   
   

6. Primary data of Master database is stored in master.mdf file which default size is 11 MB and Master database log is stored in Masterlog.ldf file which default size is 1.25 MB.

TempDB Database

1. TempDB databse contains all temporary objects like temporary tables and temporary stored procedures for Sql Server.The temporary objects are created by preceding “#” to the temporary object name.
   Example#tEmp table gets created in school database.
   

   
    Use School
   CREATE TABLE #tEmp
   (
   EmpID int
   EmpName Varchar(50)
   ) 
   
   

   When the above TSQL statement gets executed, if we see at the tempDB, this temporary table will exist there. Note that we create this table in School Database. But it exists in tempDB. We can access #tEmp table from any other database.
   
2. TempDB database is recreated every time when we re-start the SQL Server. Hence, when the database server gets restarted, the temporary objects will be removed from TempDB database.
3. Primary data of TempDB database is stored in tempDB.mdf file which default size is 8 MB and TempDB database log is stored in templog.ldf file which default size is 0.5MB.

Model Database

1. Model database work as a template for all the databases created on Sql Server.
2. If we want to keep some generic database objects like as tables, function stored procedures into the newly created database then we put these objects into Model database. Hence when we create a new database then available databse objects in Model database, would be copied into newly created database.
   Note : At the creation of a new database,if any database connection is opened for Model database then We can not able to create new database.
   
3. Primary data of Model database is stored in model.mdf file which default size is 0.75 MB and Model database log is stored in modellog.ldf which default size is 0.75MB.

MSDB Database

1. MSDB database is used by SQL Server Agent to schedule alerts, jobs, and to record operators.
   Example If we create a Database Maintenance Plan to take backup of a particular database on daily basis, then each and every entry will be stored in system defined “SYSJOBS” table in MSDB Database.
   
2. Primary data of Msdb database is stored in msdbdata.mdf file which default size is 12 MB and Msdb database log is stored in msdblog.ldf which default size is 2.25MB.

Resource Database

1. Resource database is also a system defined database that is hidden from user view like as another system defined database. It contains the system defined objects.
   
2. We can see the Resource database file by navigating to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.
3. Using the OBJECT_DEFINITION system function, we can view the contents of the resource database.
   

   
    SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects')) 
   
   

 

Different Types of SQL Server Functions

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). For more about stored procedure and function refer the article Difference between Stored Procedure and Function

Types of Function

1. System Defined Function

   These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server
   

   1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      
      
      System Scalar Function

      Scalar Function

      Description

      abs(-10.67)

      This returns absolute number of the given number means 10.67.

      rand(10)

      This will generate random number of 10 characters.

      round(17.56719,3)

      This will round off the given number to 3 places of decimal means 17.567

      upper('dotnet')

      This will returns upper case of given string means 'DOTNET'

      lower('DOTNET')

      This will returns lower case of given string means 'dotnet'

      ltrim(' dotnet')

      This will remove the spaces from left hand side of 'dotnet' string.

      convert(int, 15.56)

      This will convert the given float value to integer means 15.

   2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      
      System Aggregate Function

      Aggregate Function

      Description

      max()

      This returns maximum value from a collection of values.

      min()

      This returns minimum value from a collection of values.

      avg()

      This returns average of all values in a collection.

      count()

      This returns no of counts from a collection of values.

2. User Defined Function

   These functions are created by user in system database or in user defined database. We three types of user defined functions.
   

   1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.
      

      
       --Create a table 
      CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       FirstName varchar(50) NULL,
       LastName varchar(50) NULL,
       Salary int NULL,
       Address varchar(100) NULL,
      )
      --Insert Data
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      --See created table
      Select * from Employee 
      
      

      

      
       --Create function to get emp full name 
      Create function fnGetEmpFullName
      (
       @FirstName varchar(50),
       @LastName varchar(50)
      )
      returns varchar(101)
      As
      Begin return (Select @FirstName + ' '+ @LastName);
      end 
      
      

      

      
       --Calling the above created function
      Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 
      
      

      

   2. Inline Table-Valued Function

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.
      

      
       --Create function to get employees
      Create function fnGetEmployee()
      returns Table
      As
       return (Select * from Employee) 
      
      

      

      
       --Now call the above created function
      Select * from fnGetEmployee() 
      
      

      

   3. Multi-Statement Table-Valued Function

      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.
      

      
       --Create function for EmpID,FirstName and Salary of Employee
      Create function fnGetMulEmployee()
      returns @Emp Table
      (
      EmpID int, 
      FirstName varchar(50),
      Salary int
      )
      As
      begin
       Insert @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      --Now update salary of first employee
       update @Emp set Salary=25000 where EmpID=1;
      --It will update only in @Emp table not in Original Employee table
      return
      end 
      
      

      

      
       --Now call the above created function
      Select * from fnGetMulEmployee() 
      
      

      

      
       --Now see the original table. This is not affected by above function update command
      Select * from Employee 
      
      

      

Note

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 21000 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.

 

Different Types of SQL Server Stored Procedures

 

A stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. Benifit of Stored Procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic. Hence stored procedure improve performance to execute sql statements. For more about stored procedure refer the article CRUD Operations using Stored Procedures.

Stored procedure can accepts input and output parameters. Stored procedure can returns multiple values using output parameters. Using stored procedure, we can Select,Insert,Update,Delete data in database.

Types of Stored Procedure

1. System Defined Stored Procedure

   These stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.
   
   System Defined Stored Pocedure

   System Procedure

   Description

   sp_rename

   It is used to rename an database object like stored procedure,views,table etc.

   sp_changeowner

   It is used to change the owner of an database object.

   sp_help

   It provides details on any database object.

   sp_helpdb

   It provide the details of the databases defined in the Sql Server.

   sp_helptext

   It provides the text of a stored procedure reside in Sql Server

   sp_depends

   It provide the details of all database objects that depends on the specific database object.

2. Extended Procedure

   Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.
   Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.
   

   
    declare @logmsg varchar(100)
   set @logmsg = suser_sname() + ': Tried to access the dotnet system.'
   exec xp_logevent 50005, @logmsg
   print @logmsg 
   
   

   Example The below procedure will display details about the BUILTIN\Administrators Windows group.
   

   
    EXEC xp_logininfo 'BUILTIN\Administrators'
   
   

3. User Defined Stored Procedure

   These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

4. CLR Stored Procedure

   CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.

Note

1. We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.
2. The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.
3. In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure

 

SQL Server Transactions Management

A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

1. Implicit Transaction

   Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

2. Explicit Transaction

   Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

Transactions Example

 CREATE TABLE Department
(
 DeptID int PRIMARY KEY, 
 DeptName varchar(50) NULL,
 Location varchar(100) NULL,
 )
GO
 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY, 
 Name varchar(50) NULL,
 Salary int NULL, 
 Address varchar(100) NULL,
 DeptID int foreign Key references Department(DeptID)
) 

 --Now Insert data
INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
GO
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
SELECT * FROM Department
SELECT * FROM Employee 

 BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
 IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
 IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans 
 END
END CATCH 

 --Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

 --Transaction with Save Point BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
IF @@TRANCOUNT > 0
 BEGIN SAVE TRANSACTION trans;
 END
INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi') 
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans
 END
END CATCH 

 --Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

 

Database Normalization Basics

Normalization or data normalization is a process to organize the data into tabular format (database tables). A good database design includes the normalization, without normalization a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.

Normal Forms

We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. Generally we organize the data up to third normal form. We rarely use the fourth and fifth normal form.
To understand normal forms consider the folowing unnormalized database table. Now we will normalize the data of below table using normal forms.

1. First Normal Form (1NF)

   A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:
   
   1. Separate the repeating fields into new database tables along with the key from unnormalized database table.
   2. The primary key of new database tables may be a composite key
   1NF of above UNF table is as follows:
   

2. Second Normal Form (2NF)

   A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(means the value of field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.
   The process of converting the database table into 2NF is as follows:
   1. Remove the partial dependencies(A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.
   2. If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.
   2NF of above 1NF tables is as follows:
   

3. Third Normal Form (3NF)

   A database table is said to be in 3NF if it is in 2NF and all non keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.The process of converting the table into 3NF is as follows:
   
   1. Remove the transitive dependecies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key.Hence its value is determined, indirectly by the primary key )
   2. Make separate table for transitive dependent Field.
   3NF of above 2NF tables is as follows:
   

4. Boyce Code Normal Form (BCNF)

   A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:
   
   1. Remove the non trival functional dependency.
   2. Make separate table for the determinants.
   BCNF of below table is as follows:
   

5. Fourth Normal Form (4NF)

   A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:
   
   1. Remove the multivalued dependency.
   2. Make separate table for multivalued Fields.
   4NF of below table is as follows:
   

6. Fifth Normal Form (5NF)

   A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:
   
   1. Remove the join dependency.
   2. Break the database table into smaller and smaller tables to remove all data redundancy.
   5NF of below table is as follows:
   

 

Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application.

During database designing and data manipulation we should consider the following key points:

1. Choose Appropriate Data Type

   Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

2. Avoid nchar and nvarchar

   Does practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.

3. Avoid NULL in fixed-length field

   Does practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

4. Avoid * in SELECT statement

   Does practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.
   

   
    -- Avoid
   SELECT * FROM tblName
   --Best practice 
   SELECT col1,col2,col3 FROM tblName 
   
   

5. Use EXISTS instead of IN

   Does practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.
   

   
    -- Avoid 
   SELECT Name,Price FROM tblProduct 
   where ProductID IN (Select distinct ProductID from tblOrder)
   --Best practice 
   SELECT Name,Price FROM tblProduct 
   where ProductID EXISTS (Select distinct ProductID from tblOrder)
   
   

6. Avoid Having Clause

   Does practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.
   

7. Create Clustered and Non-Clustered Indexes

   Does practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

8. Keep clustered index small

   Does practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes

9. Avoid Cursors

   Does practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.

10. Use Table variable inplace of Temp table

    Does practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

11. Use UNION ALL inplace of UNION

    Does practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

12. Use Schema name before SQL objects name

    Does practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.
    

    
     --Here dbo is schema name
    SELECT col1,col2 from dbo.tblName
    -- Avoid
    SELECT col1,col2 from tblName 
    
    

13. Keep Transaction small

    Does practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks. Please refer the article SQL Server Transactions Management

14. SET NOCOUNT ON

    Does practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:
    

    
     CREATE PROCEDURE dbo.MyTestProc
    AS
    SET NOCOUNT ON
    BEGIN
    .
    .
    END 
    
    

15. Use TRY-Catch

    Does practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH. Please refer the article Exception Handling by TRY…CATCH

16. Use Stored Procedure for frequently used data and more complex queries

    Does practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

17. Avoid prefix "sp_" with user defined stored procedure name

    Does practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

 

Remove duplicate records from a table in SQL Server

Sometimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server.

Suppose we have below Employee table in SQL Server.

 CREATE TABLE dbo.Employee
( 
EmpID int IDENTITY(1,1) NOT NULL, 
Name varchar(55) NULL, 
Salary decimal(10, 2) NULL, 
Designation varchar(20) NULL
 ) 

The data in this table is as shown below:

Remove Duplicate Records by using ROW_NUMBER()

 WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) 
AS duplicateRecCount
FROM dbo.Employee
)
--Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1 

 --See affected table
Select * from Employee 

For more help about ROW_NUMBER(), please follow the MSDN link.