Create Maintenance Plan for scheduled backup task

My friend (work as Network engineer) asked me how we can create maintenance plan for backup Sql Server database so I wrote this article to simplify and clarify the task to him.

First of all, What is Maintenance Plan?
Maintenance plan contains group of tasks as graphical tool to simplify the job of DBA to perform tasks and configure its options like backup database, shrink database,… without need to write any T-SQL statements.

Maintenance plan Tasks:

1. Backup Database task
    Described As the title of task
2. Check Database Integrity task
    Checks the structural integrity of the database. For Examples would include checking disk space allocations inside SQL, checking consistency between system metadata tables, and checking the structures that make tables.
3. Execute SQL Server Agent Job task
    The Execute SQL Server Agent Job Task can be used to run a SQL Server Agent Job which is created on the SQL Server Instance. This task is only available when you are creating a Maintenance Plans using SSIS designer.
4. Execute T-SQL statement task
    Described As the title of task
5. History Clean Up task
    Deletes the historical data related to database backups and restore activities, SQL Server Agent Job history, database maintenance plan history etc
6. Maintenance clean up task
    Remove the older files like maintenance plan execution reports, database backup files etc
7. Notify Operator task
   Send messages to the SQL Server Agent Operator when a task has successfully completed or failed
8. Rebuild Index task
   Recreates an index with new settings you define in the wizard. Although this task can reorganize an index
9. Reorganize Index task
    Perform "Fragmentation" and it exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file
10. Shrink Database task
    Reduce the physical size of the database and logs by removing the empty data and log pages.
11. Update statistics task
    Force the SQL Engine to reexamine statistics on columns and indexes

A DBA can create a database maintenance plan either by using the Maintenance Plan Wizard or by using the SQL Server Integration Services (SSIS) designer (for Advanced Options)

Let's see an example which create scheduled automated backup and Send an Email by using Database mail.

We can divide this example into three part as following:
a. Create scheduled backup
b. Configure the Database mail and Operator
c. Add Notify Operator task to maintenance plan

Steps to Create scheduled backup:

1. Connect to SSMS
2. Right click on Maintenance Plans folder and choose New Maintenance Plan…
3. Enter a name for your maintenance Plan. Ex: " AdventureWorks_ScheduledBackup"
4. At the left bottom of maintenance plan designer add the following tasks:
    a. Shrink Database task
    b. Backup Database task
    c. Maintenance clean up task

5. Now let's configure the options for each task

6. So right click on Shrink database task and choose Edit
7. Select AdventureWorks database and then click ok

8. Now let's configure the options for Backup Database task

9. So right click on Backup database task and choose edit
10. Select AdventureWorks database ,full type backup ,place of storing backup and the start name convention (bak) then select ok

11. Now let's configure the options for maintenance cleanup task

12. So right click on it maintenance cleanup task and choose edit
13. Select option as the below image

14. Now let's configure the schedule for the above plan

15. So at the above of maintenance plan designer select

16. And select as the below image

Steps to Configure Database mail and Operators:

1. You can refer to this link to know how to configure Database mail for SQL Server 2008
2. To adding new Operator just go to Operators folder and choose New operator

3. Enter the name and email of current operator as following:

Steps to add notify operator task to our maintenance plan

1. So add two Notify Operator Task to our maintenance plan as following
2. As you see one for success task and one for failed task

3. Now right click on each Notify Operator task and choose edit

4. Select the option as follow for each task

 
Finally save maintenance plan and test it.

Spatial Data types

Sql Server 2008 introduced new types of spatial data including the following types:
1. Geometry data type.
A data type used to store two and three dimensional data coordinates. It is used to store planar (flat-earth) data. It is generally used to store XY coordinates that represent points, lines, and polygons in a two-dimensional space
2. Geography data type.
A data type used to store ellipsoidal data, such as latitude and longitude coordinates. It is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface

So by using the above types you can store data like latitude,lognlatitude,…which can be used in conjunction with for example Microsoft Virtual Earth to provide a visual representation of your geospatial data.

These types implemented by .Net CLR data types which mean they can support various properties and methods specific to the data.

Let's see examples:

USE TestDb
GO

CREATE TABLE EmployeesAddress
(EmployeeID int IDENTITY PRIMARY KEY,
AddressName NVARCHAR(50),
Location    GEOGRAPHY)
GO

--The Point extended static geography method constructs an
--instance representing a point that includes information 
--on the longitude, latitude, and SRID.
--The Parse extended static geography method returns a geography instance when the input
--is expressed in the OGC WKT representation.
INSERT INTO EmployeesAddress
VALUES
('ALRiyadh',
    GEOGRAPHY::Parse('POINT(-83.0086 39.95954)'));
GO    
    
SELECT * FROM EmployeesAddress
GO

--Other example use GEOMETRY data type
DECLARE @s GEOMETRY
SET @s = 'LINESTRING (  69 26, 69 23, 69 21, 67 20, 65 20, 
          63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20, 
          44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38, 
          50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45, 
          69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63, 
          66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71, 
          51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56, 
          41 54, 42 53 )'
          
SELECT @s

Configure Database mail and test it

This service introduced with Sql Server 2005 to send email messages. so you can get benefits from this services to send query result or report to user as text or attachment by using SMTP protocol.


Note:
SQLMail exists to current version for backward compatibility. You should not use SQLMail in new development because support will be removed in a future version of SQL Server.
SQLMail is based on MAPI (Messaging Application Programming Interface) and Database mail depends on Service Broker. Database Mail can be encrypted for additional security. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed.

Now follows these Steps to configure database mail:

1.  Open SSMS and connect to your instance name
2. Expand Management folder and right click on "Database mail" and choose configure database mail
3. On welcome page wizard click next
4. Choose "Setup database mail …" and click next
5. Enter Profile name and description to your first account and click on "Add"
6. Enter your SMTP account. And then click ok and then next

• 
  
   Configure the appropriate public or private profiles, and then click Next
• 
  
   Configure the parameters then click next then finish.

Now to send email for testing, there are two ways:
1. By using sp_send_dbmail
2. By right click on the Database mail and choose send Test E-mail…

So let's see the first option:

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'LocalMail',
        @recipients = 'almanhaj1@gmail.com'
        ,@body = 'Hi fadi'
        ,@subject = 'Test Email' ;

Note:

When you go through the steps of the above wizard ,it will ask you to enable database mail service because by default the service is disabled.

FileStream Data Type

Filestream data type used to store data(unstructured data) as varbinary(MAX) in separate file in the file system that means not with database file. This increase the performance of read and writes to database of this type.

A VARBINARY (MAX) column with FILESTREAM attribute is not restricted to the 2 GB limit SQL Server imposes on Large Value Types. The size of the file is limited by the size of the disk volume only.

Why FileStream data type?

If you think to separate your files storing from its structured storage, This separation can cause data management complexities and if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

Note:Your data must be large than 1 mb in size to involve the filestream data type.

To implement Filestream in your database do the following:

1. Enblae Filestream in Sql server 2008
2. Create filegourp that contains the filestream database file

You can enable Filestream in Sql server in three ways:

1. At the time of installation SQL Server 2008
2. or by using T-SQL (sp_configure)
3.  Or by SQL Server Configuration manager

Let's see the easiest way which is by Sql Server Configuration manager:

So first open SQL Server Configuration manager and then right click on your instance name and choose properties and select Filestream tap and enable it.

Let's see an example:

1. After you enabled the Filestream in your instance
2.  Create a folder called "TestDb" in C: partition
3. Then run this command to create a database with three fille (.mdf,.ldf,filestream data container or folder)

CREATE DATABASE TestDb 
ON
PRIMARY ( 

    NAME = TestDB, 
    FILENAME = 'C:\TestDb\TestDb.mdf'

), 
FILEGROUP TestDbFS CONTAINS FILESTREAM( 

     NAME = TestDbFS,
     FILENAME = 'C:\TestDb\TestDbFS')

LOG ON (                         

      NAME = TestDbLog,
      FILENAME = 'C:\TestDb\TestDbLog.ldf')
GO

4. Open c:\TestDb in windows explorer and you see the following:


You will see a folder “$FSLOG” and a file “filestream.hdr”.
The folder “$FSLOG” is the FILESTREAM equivalent of the Database Transaction Log file. “filestream.hdr” contains important metadata information used by SQL Server internally.

5. Create a table has a filestream attribute

USE TestDb
GO

CREATE TABLE [dbo].[Employees](
    -- Table which use filestream must has 
    -- UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE column
   Id        UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   FullName NVARCHAR(50),
   EmpPhoto VARBINARY(MAX) FILESTREAM NULL

)
GO

6. Now let's insert a record to the above table

USE TestDb
GO
--This code to load the image from the disk to
-- @image variable
DECLARE @image AS VARBINARY(MAX)
SELECT @image = CAST(bulkcolumn AS VARBINARY(MAX))

      FROM OPENROWSET(
      BULK
      'C:\TestDb\1.png',
      SINGLE_BLOB ) AS a

-- Then inserted to a table          
INSERT INTO Employees (Id, FullName, EmpPhoto)
SELECT NEWID(), 'Fadi Ahmad',@image
GO

7. And try to run this command

USE TestDb
GO

SELECT *, EmpPhoto.PathName() FROM Employees
GO

You will see that the image data displayed as binary data.

Windows PowerShell with SQL Server 2008

What is Windows PowerShell?

Windows PowerShell is a command-line shell and scripting environment that allows you to automate administrative and development tasks by creating robust scripts.
Unlike the most command line system that accept and return text string, the windows PowerShell supports and uses XML and objects (.Net Objects).

Why PowerShell?

• PowerShell enables easy integration between “On Premise” and “Hosted” services
• PowerShell scripts are repeatable and reusable
• PowerShell script libraries can be developed and enhanced over time, and used at multiple customer locations
• PowerShell can access data stores such as the registry, the certificate store, IIS 7 or Active Directory as if they were the file system. 
• …..

History of PowerShell

 What the system administrators need:

• Productivity – ease of use, easy to learn
• Repeatability – reuse scripts – especially for routine tasks
• Power - Ability to manipulate all server components and their settings – users, applications, files, permissions, etc.

Features of PowerShell:

Environment for running PowerShell:

• Shell Environment
• Integrated Scripting Environment (support debugging)
• And others like Idera’s PowerShell Plus editor/console.

PowerShell v2 is included in Windows 7 and Windows 2008 R2 – No need to download.

And which PowerShell cmdlets are available..?

Windows Server 2008 (AD, ADFS, Hyper-V, IIS, etc…), Exchange Server 2007, ILM 2007 Sync Engine, SQL Server 2008, Outlook Live…

Now how to use both technologies together:
When you install SQL Server 2008, it's by default installed the PowerShell 1.0, Sql server PowerShell provider and others utilities allow you to run the Sqlcmd with PowerShell environment.

Note: SQLPS.exe is a PS mini-shell built on PS v1 which means that PowerShell V2 additional cmdlets and features wouldn’t be available from within SQLPS. For this reason you can’t use PowerShell V2 scripts in a SQL Agent job. you can use PS V2 to extend management on your SQL box by some reconfiguration.

Benefits of using Windows PowerShell:

• You can invoke Sqlcmd through windows PowerShell and executed.
• You can run script that scheduled by Sql server agent jobs.
•  …..

You can run the Sql Server PowerShell by using two methods:

1. From SSMS by using start PowerShell option
2. From cmd by using sqlps
3. From Shell Envirnment

Note:
In SQL Server 2008, Windows PowerShell support is limited to the SMOs relating to the Database Engine and Service Broker. A Windows PowerShell provider for SQL Server Analysis Services (SSAS) is available on the CodePlex Web site at http://www.codeplex.com/ powerSSAS.

SQL Server uses a hierarchy to represent how objects are related to each other within a server. The root node of Sql server is SQLSERVER: drive and under this folder there are:

1. SQLSERVER:\SQL Contains database objects
2. SQLSERVER:\SQLPolicy Contains policy-based management objects, such as policies and facets.
3. SQLSERVER:\SQLRegistration Contains registered server objects, such as server groups and registered servers

For example if we want to refer to Production.Product in AdventureWorks database :
SQLSERVER:\SQL\VAIOPC\SQL2008\DataBases\AdventureWorks\Production.Product

Note:

If your instance named as default so in this case you refered as DEFAULT:

SQLSERVER:\SQL\VAIO-PC\DEFAULT\DataBases\AdventureWorks\Production.Product

You can see the above path by do the following steps:

1. Right click on AdventureWorks database from SSMS
2. Choose start PowerShell
3. Then note the path as this image:

Cmdlets are small compiled pieces of functionality that provide a single piece of functionality. They have a verb-noun syntax e.g. Get-Help

SQL SERver 2008 include a list of cmdlets with its alias and Notices PowerShell cmdlets can be referred to with their full names or with any of a number of aliases.

Get-Location Returns the current node name. Aliases: gl, pwd.

Set-Location Changes the current node. Aliases: sl, cd, chdir.

Get-ChildItem Lists the objects stored at the current node. Aliases: gci, dir, ls.

Get-Item Returns the properties of the current item. Alias: gu.

Move-Item Moves an item. Aliases: mi, move, mv.

Rename-Item Renames an object. Aliases: rni, rn, ren,

Remove-Item Deletes an object. Aliases: ri, del, rd, rm, rmdir.

Tips to simplify the typing on the command line:

• Use cd.. to return to previous folder
• Use cls to clear the screen
• Use tab to complete the partial word
• Use Up and Down to scroll the previous commands
• Use the –force parameter to view system objects such as the sys schema and the objects in it.

What about I need help??

There is a command –lets called "Get-Help" Provides help information about each cmdlet.

For example try to run the following:

Get-Help rename


Finally, let's see an example how we can run a query by using Invoke-Sqlcmd:

1. Connect to SSMS
2. Go to AdventureWorks and right click on it
3. Choose start PowerShell
4.  Write the following code :
   
   Invoke-Sqlcmd –Query "SELECT TOP 10 * FROM Production.Product" then press enter and you will get the following result:

New Data types in Sql Server 2008

Sql server 2008 introduces new types that help the developer to have various choices to deal with various data.


Note:

In previous version "The DATETIME data type stores both the date and the time portion together as single column value. This data type supported dates between January 1, 1753 and December 31, 9999, with the time portion being accurate to 3.33 milliseconds and required 8 bytes of storage space. The SMALLDATE data type requires less storage space then a DATETIME data type, only 4 bytes, but supports a smaller date and time range. SMALLDATE only supports dates from January 1, 1900 through June 6, 2079, and the time portion is only accurate down to the minute."

Now let's see the now data types which introduced with sql server 2008.

Date Type (3 byte)
When you don't need the time portion of a DATETIME, you can use it

DECLARE @RES DATE
SET @RES = GETDATE()
SELECT @RES

Time Type (The size can be 3, 4, or 5 bytes, depending on the chosen precision)
When you don't need the date portion of a DATETIME, you can use it

DECLARE @RES TIME
SET @RES = GETDATE()
SELECT @RES

-- The default precision is 7 digits
-- you can display from 1 to 7 digits
DECLARE @RES2 TIME(7)  
SET @RES2 = GETDATE()
SELECT @RES2

DATETIME2 type(6 to 8 bytes)
DATETIME2 shows the greatest range of precision of date and time

DECLARE @RES DATETIME2
SET @RES = GETDATE()
SELECT @RES

--Note number of precision between the two results
DECLARE @RES2 DATETIME
SET @RES2 = GETDATE()
SELECT @RES2

DATETIMEOFFSET type (8 to 10 bytes)
Here provide you with extra time zone

DECLARE @RES DATETIMEOFFSET
SET @RES = GETDATE()
SELECT @RES

HIERARCHYID type (stored as varbinary <= 900 bytes)

Hierarchal data is defined as a set of data items related to one another in a hierarchy, that is, a parent node has a child and so forth
We use this type to store hierarchical data to simplify the retrieve data instead of using recursive operation or other complex way. So if you remember ParentID or ReportToId column now with this type no need for this column as foreign key.
With HierarchyID, the data type stores the whole path to the current record in the “Id” column.

Note:
HIERARCHYID type is a SQL CLR UDT (Common Language Runtime User Defined Type).

Let's see this example:

USE TestDb
GO

--Create Table that contains Id as hierarchyid data type
CREATE TABLE Employees
(
    Id       HIERARCHYID NOT NULL PRIMARY KEY,
    FullName NVARCHAR(256) NOT NULL,
    
)
GO

--Insert some data to this table with its path
INSERT INTO Employees(Id, FullName) VALUES
    (hierarchyid::GetRoot(), 'Fadi Ahmad Abdulwahab'),
    ('/1/', 'Shadi Ahmad Abdulwahab'),                
    ('/1/1/', 'Tareq Ahmad Abdulwahab'),        
    ('/1/1/1/','Rola Ahmad abdulwahab'),
    ('/2/','Rana Ahmad abdulwahab'),
    ('/2/1/','Rasha Ahmad abdulwahab')                
GO

-- Now let's retrieve the data
SELECT 
-- Id stored as binary format
Id, 
-- complete path of current record in the tree
Id.ToString() 'Record Path',
-- level of current record in the tree
Id.GetLevel() 'Record  Level',
-- Get childs of parent start from first root not the root '/'
Id.GetAncestor(1) 'partents of /./..',
-- Get childs of parents start from second root not the first root '/1/'
Id.GetAncestor(2) 'Parents of /././ ...',
FullName
FROM Employees
GO

--Let's do some filtering
--Here i want to retrieve only all records thier parent is /2/
SELECT 
Id, -- stored as binary format
Id.ToString() 'Record Path', -- output path
Id.GetLevel() 'Record  Level', -- output level
FullName
FROM Employees
WHERE Id.IsDescendantOf('/2/') = 1
GO

Others methods supported with HIERARCHYID:

•  GetAncestor: Returns a HierarchyID that represents the parent of this HierarchyID node.
•  GetDescendant: Returns a child node of this HierarchyID node.

When using SQLCLR?

Always make your natural choice for data operations "T-SQL" to get benefits of ease of use and deploy and query plans and caching query plans,..etc unless you face the following issues:

•  Complex and heavily operations
• Pattern matching
• Deals with data out of Sql server
• Needs to write extended stored procedure by using C++ and COM.

Why Procedural and recursive code works much better in CLR?

CLR code and higher-level programming languages such as C# and VB .NET have offered better support for procedural and recursive code. This is because such languages are designed from the ground up to support per method call-based stack frames and activation records, and they do not suffer from limits such as a maximum call depth of 32 in recursive operations like in T-SQL.

So there are times or reasons to use SQLCLR or T-SQL and basically for example writing outer join using T-SQL in stored procedure easier than CLR stored procedure and better performance and it does not need to host additional resources like SQLCLR engine or more step to get the result.

Shortly

"The use of CLR within SQL Server is an important aspect of making SQL Server more extensible, allowing developers to do more than what is possible with just the T-SQL language and its system functions"

The following types of objects can be created using SQLCLR:

1. Stored procedures
2.  Scalar or Table-valued UDFs
3. Triggers (DML, DDL, and logon triggers)
4. User-defined aggregates
5. User-defined types (UDTs)

Note: user-defined aggregates and UDTs, can be created only using SQLCLR; they cannot be created using T-SQL.

Indexes - part 5

Let's see examples with Filtered and covering indexes:

USE TestDb
GO

--Create demo table called Employees Table
CREATE TABLE Employees
(EmpId INT IDENTITY(1,1)
 ,Name VARCHAR(256)
 ,BirthDate DATETIME
 ,JobDetails VARCHAR(100)
)
GO

--Insert dummy data into Employees Table
DECLARE @loop INT
SET @loop = 1000

WHILE @loop > 0
BEGIN
      INSERT Employees  VALUES ('Fadi Ahmad Abdulwahab',
      '1984-05-26 00:00:00.000','Job details or description...' )
      SET @loop = @loop - 1
END

-- Create Clustered Index 
CREATE UNIQUE CLUSTERED INDEX CI_EmpId
ON Employees (EmpID)
GO

--Update this column for test
UPDATE dbo.Employees SET 
BirthDate = '2009-05-26 00:00:00.000'
WHERE EmpId=700

--Create Covering Index
CREATE NONCLUSTERED INDEX NCI_SmallTable ON Employees(EmpId,Name) INCLUDE (BirthDate)

SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

--Create filtered index
CREATE NONCLUSTERED INDEX NCI_BirthDate ON Employees(BirthDate)
WHERE BirthDate >= '2009-05-26 00:00:00.000'

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')

SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

Indexes - part 4

Let's see examples with clustered and nonclustered indexes:

USE TestDb
GO

--Create demo table called Employees Table
CREATE TABLE Employees
(EmpId INT IDENTITY(1,1)
 ,Name VARCHAR(256)
 ,BirthDate DATETIME
 ,JobDetails VARCHAR(100)
)
GO

--Insert dummy data into Employees Table
DECLARE @loop INT
SET @loop = 1000

WHILE @loop > 0
BEGIN
      INSERT Employees  VALUES ('Fadi Ahmad Abdulwahab',
      '1984-05-26 00:00:00.000','Job details or description...' )
      SET @loop = @loop - 1
END

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE EmpId = 700

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')
GO

-- Create Clustered Index 
CREATE UNIQUE CLUSTERED INDEX CI_EmpId
ON Employees (EmpID)
GO

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE EmpId = 700

--Update this column for test
UPDATE dbo.Employees SET 
BirthDate = '2009-05-26 00:00:00.000'
WHERE EmpId=700

 
--Now let's exam this query before we create 
--NonClustered Index 
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

--Let's Create Nonclustered Index on BirthDate column
CREATE NONCLUSTERED INDEX NCI_BirthDate
ON Employees (BirthDate)

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

Indexes - part 3

In part 2 we have seen the clustered index and nonclustered index and let's continue with another new indexes introduced with Sql server 2005/2008


SQL Server 2005 introduces Index covering

By creating a non-clustered index that contains all the columns used in a SQL query called index covering. So the query accesses only the index file; it doesn't touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is index covering. The index is much smaller than the table, which is why the query read many fewer pages.

SQL Server 2008 introduces Filtered indexex

Filtered indexes allow you to create a filter on an index. The index B-Tree will only contain the rows of data that meet the filtering criteria. This allows you to reduce the amount of data contained in an index which means that you are also reducing the data affected in an index by data modifications and also decrease usage of disk space. This often involved partitioning the tables in SQL Server 2005 or archiving data in earlier versions. Ideally we can say that Filtered Index is an optimized non clustered index (Filtered Index can only be created as a nonclustered index on a table), which is best suited for those queries that select a very small percentage of rows from a given table.

Points to remember with Filtered Index:

• They can be used on views only if they are persisted views
• They cannot be created on full-text indexes.

Indexes - part 2

We learned in part 1 some concept of indexes and let's continue by see different type of indexes.

So what is Clustered Index?
The clustered index stores the real data or rows at the leaf level of index.
Note:

• Only one clustered index can contain on a table or view.
•  The indexed values can be sorted in either ascending or descending order.
•  Data in a table is sorted only if a clustered index has been defined on a table.

Terms: A clustered table can be referred to a table has a clustered index and Heap table for a table does not have a clustered index. When you create PK by default Sql server create a clustered index for your table.

Nonclustered Index
The nonclustered index only contains the pointer to actual data rows that means there is additional step in order to get the actual data.

There are differences of nonclustered index if it's on clustered table or heap table so if it's on a clustered table the pointer points to the clustered index to move to actual data row (the leaf node of the nonclustered index contains the clustered index keys) and if it's on heap table the pointer points to actual data row.

Note:

•  nonclustered index cannot be sorted
•  you can create more than one nonclustered indexes for a table or view
•  SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999

Index types:

1. Composite index: you can include up to 16 columns in an index as long as the index does not exceed the 900 byte limit.

2. Unique index: to ensures the uniqueness of each value even if your index contains composite index. For example if you create an index on EmpId and DeptId columns so together must be unique. A unique index is defined when you create a primary key for one column or more or add unique constrain.

Term: Column Cardinality
The cardinality of column is referred to the uniqueness of data inside a particular column

How to design indexes
Some considerations before index you table:

1. More indexes means more disk space so you need to implement the necessary indexes
2. When you update data the indexes automatically updated so this can affect performance

You should consider the following guidelines when planning your indexing strategy:

• For tables that are heavily updated, use as few columns as possible in the index. 
•  If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance. 
•  Use indexes carefully on small tables because the query engine might take longer to navigate the index than to perform a table scan. 
•  For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values. This is why the primary key is often used for the table’s clustered index, although query considerations should also be taken into account when determining which columns should participate in the clustered index.
• The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs. 
•  For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = 'Fadi') should be listed first. 
•  You can also index computed columns if they meet certain requirements. For example, the expression used to generate the values must be deterministic.
•  Try to insert or modify as many rows as possible in a single statement, rather than using multiple queries. 
•  Create nonclustered indexes on columns used frequently in your statement’s predicates and join conditions. 
•  Consider indexing columns used in exact-match queries. 
•  Index should be created on columns used in WHERE clause, Order By, Group By, Distinct etc.
•  All columns used in WHERE clause should be included in single index to get best results.
•  Do not create indexes unless you need them, too many indexes will slow INSERT, UPDATE and DELETE.

Ref : i got the above tips from the following url

When to use it:

Clustered index:

•  For use on columns that are frequently searched for ranges of data
• For use on columns with low selectivity

Nonclustered index:

• For use on columns that are searched for single values
•  For use on columns with high selectivity

Indexes - part 1

One of the most points to know that proper index means fast query retrieving, so we need to know some details about indexes in Sql server 2005 & 2008.

Definition:

You can consider the index in Database as index of book so by using index of book you look up will be faster than if you look up through each page in the book and this is the index in the database.

Scope:

You can create indexes on columns of table or view so this provides fast way to find data or values within these columns.

Example for clarification:

For example if we create a primary key "EmployeeId " and make it as indexed column so if you query by one of values of this column ,Sql server engine first finds that value in the index and then use the index to locate to entire row . vice versa if the table does not has an indexed column, Sql server query engine will look up through each row in this table till find the values and this harms the performance. So Indexes help you when you try to find specific info for example (WHERE clause …) but if you retrieving the whole table the indexes are nothing here.


Type of columns that can be indexed:

Any type of column like integer ,uniqueidentifier or xml,.. can created as indexed column except the (LOB) data type like image ,text or varchar(MAX).


B-tree

An Index is a set of pages or index nodes that organized as above structure "B-tree".

There is at the top of hierarchy structure root called "Root Level" and at the bottom of hierarchy there is root called "Leaf Level" and between these two levels the branches called "intermediate level".

Note:

"Clearly, indexing large amounts of data can significantly improve search performance. a b-tree also optimizes costly disk accesses that are of concern when dealing with large data sets."

For example if you write a query against an indexed column ,Sql server query engine first starts at the root level and move down through intermediate level until it reaches the leaf level.

So if we look for "T" value in indexed column, the query engine will first look in the root level to determine which page to reference in the top intermediate level. In this example, the first page points the values A-M, and the second page, the values N-Z, so the query engine would go to the second page on that level. Then the query engine move down to last or leaf level at the page that include "T" value. The leaf node will contain either the entire row of data or a pointer to that row, based on type of indexes whether it's clustered index or nonclustered index.

How to create schedule back up for sql server 2005 express edition

I have faced this problem from one of our small portal so I found the solution for this issue as following:


1. Using SQLCMD with Accessories -> System Tools -> Scheduled Tasks (Windows Tasks).
For example:

link

2. Using SMO Code.
For example :

Link

3.Using third Party to do the task.
For example:

Link

Forget password of sa account

My Friend asks me about what we can do if we forgot sa password, so what the solutions can solve our problems?


Solution 1:

If you have windows login account which is member of admin group (Builtin\Administrator) because members of this users by default belong to sysadmin privilege so login by this user to windows and then login to sql server management studio with using windows authentication and then reset sa account password.

Note: here you need to start sql server in Single user mode and you can do it by this command:

Stop the sql server service:

net stop ServiceName

You can find you service name as picture below:

 

 

 

 

 

 

 

 

 

Start the service in single user mode:

net start ServiceName \m

Solution 2:

If you have Sql server user login which is member of sysadmin so login by this user to sql server management studio with sql server authentication and then reset sa account password.

Solution 3:

Using third party like "MS SQL SERVER PASSWORD UNLOCKER"

Last solution:

Last killing option is to Uninstall and reinstall sql server.

Best Practices:

• Always do not use sa account to connect through your application to sql server because first of all it's a hackers favorite

• Create a group in windows and then add this group to sql server and grant this group a sysadmin privileges so any time you can add any user to this group then login as sysadmin to Sql server.

Note: the above solutions can also solve of situation of disabled sa account.

How to reset identity increment

My friend asked me how to reset the identity increment for a specific table so the solution as following:

USE TestDb
GO

DBCC CHECKIDENT ('Employees',RESEED,0)

XACT_ABORT setting And Try..Catch

Let's see this example which inserts duplicate Primary key value to student table:

You will find two rows inserted into student table and the transaction does not rollback the code because by default, SQL Server does not roll back a transaction that has an error. If you want the transaction to either complete entirely or fail entirely, you can use XACT_ABORT setting on your connection, as follows:

The above one solution to solve your problem but it's not the proper solution so to provide a more structured way of handling errors that is very similar to the error handling routines of other programming languages, you can now use a TRY. . .CATCH block.

Within the CATCH block, you can commit or roll back the current transaction.

Note:

1. A RAISERROR executed in the TRY block immediately passes control to the CATCH block without returning an error message to the application.

2. A RAISERROR executed in the CATCH block closes the transaction and returns control to the calling application with the specified error message.


Let's see this example:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Last tip you can also use @@ERROR to handle the errors but this old way used with SQL Server 2000 and you can use it.

SCHEMABINDING option

SCHEMABINDING option is used to ensure that you can't drop dependent objects ,for example you have a function which run SELECT statement form Students table so when you try to drop the table you will get an error prevent you to drop the table unless you drop the objects first.


You can use this option with functions and views.

Let's see how we can use it:

WAITFOR statement

WAITFOR statement is used to pause the execution of code to a specific time.


WAITFOR has three different options:

1. WAITFOR DELAY pauses the execution of code for a specified length of time

2. WAITFOR TIME pauses the execution of code until a specified time is reached

3. WAITFOR RECEIVE is used with Service broker


Let's see this example which shows how to use WAITFOR DELAY:

And this example shows how to use WAITFOR TIME: