Category Archives: SQL Server

Introducing CLR Stored Procedures in SQL Server 2012

Contents

 

Introduction

This guide demonstrates how to use SQL Server Common Language Runtime (CLR) integration with SQL Server 2012. In this guide, the following two assemblies will be created using C#:

  • MathAsm
  • StoredProceduresAsm

 

About Common Language Runtime (CLR)

The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Developers use compiled OO languages like C# or Visual Basic .NET to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005, SQL Server 2008, and SQL Server 2012 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

Enable CLR in SQL Server

In SQL Server 2005, SQL Server 2008, and SQL Server 2012, the Common Language Runtime (CLR) is off by default. In an effort to improve security, Microsoft has turned many features “off by default”. This is a big change from the old policy of turning every feature on so that developers weren’t discouraged from using the feature due to difficulties in getting the feature to work.

EXEC sp_configure 'show advanced options', '1'
Go

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

EXEC sp_configure 'clr enabled', '1'
Go

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

EXEC sp_configure 'show advanced options', '0'
Go

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

Compile and Build Assembly

In order to develop SQL CLR assemblies for SQL Server, you must have and utilize the .NET Framework 3.5 installed on your development computer. If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to development SQL CLR assemblies for SQL Server. SQL Server 2005, SQL Server 2008, and SQL Server 2012 support only those assemblies that target the 2.0, 3.0, 3.5, or 4.0 version of the .NET Framework.

As mentioned in the Introduction, this guide will compile and build two assemblies using C# that will be integrated with SQL Server 2012. The source code for both assemblies is presented below.

   Math.cs

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class Math {

    [SqlFunction]
    public static SqlDouble Factorial(SqlDouble x) {
        SqlDouble y = 1.0;
        while(x > 1.0) {
            y *= x;
            x -= 1;
        }
        return y;
    }
}

   StoredProcedures.cs

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures {

    ///  /// Execute a command and send the resulting reader to the client /// 
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetVersion() {
        using (SqlConnection connection = new SqlConnection("context connection=true")) {
            connection.Open();
            SqlCommand command = new SqlCommand("select @@version", connection);
            SqlDataReader r = command.ExecuteReader();
            SqlContext.Pipe.Send(r);
        }
    }

    public const double SALES_TAX = .086;

    [SqlFunction()]
    public static SqlDouble addTax(SqlDouble originalAmount) {
        SqlDouble taxAmount = originalAmount * SALES_TAX;
        return originalAmount + taxAmount;
    }
}

Copy the source code files above to your source directory. For example:

C:\> copy C:\Users\SQLServerAdmin\Downloads\*.cs C:\Programming\c#\SQLCLRIntegrationExample\src\
C:\Users\SQLServerAdmin\Downloads\Math.cs C:\Users\SQLServerAdmin\Downloads\StoredProcedures.cs 2 file(s) copied.

There are two methods to compile the C# source code; Microsoft Visual Studio and the C# command-line compiler. This guide uses the C# command-line compiler.

To use the C# command-line compiler, navigate to the appropriate .NET Framework directory. For example:

C:\> cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319

From this directory, compile the two SQL CLR source code files into an assembly (dll).

SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies

csc /target:library /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

csc /target:library /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

DIR %DLL_PATH%
 Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 12:13 AM 3,584 Math.dll 03/26/2013 12:13 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,610,902,528 bytes free

Grant Permissions for External Access

In order to create an assembly with EXTERNAL_ACCESS (or UNSAFE) permission set, you need extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database (ALTER DATABASE[DevDB]SET TRUSTWORTHY ON); however, this is not a preferred option as it can cause other undesired side effects. Note that if your assembly does not require resources outside of the database (for example, writing to a file), you do not need to assign an EXTERNAL_ACCESS permission set to the assembly in order to execute the SQL CLR in the database. You only need to assign an EXTERNAL_ACCESS permission set to the assembly when accessing resources outside of the database server.

If your assembly will need access to resources outside of the database, use the following method as the preferred alternative to setting the TRUSTWORTHY bit in the database as explained above.

  1. Create a .NET strong name key file by using the sn.exe tool.
    C:\> SET SN_TOOL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\sn.exe"
    
    C:\> SET KEY_PATH="C:\Programming\c#\SQLCLRIntegrationExample\keys"
    
    C:\> %SN_TOOL% -k %KEY_PATH%\extSQLKey.snk
    
    Copyright (c) Microsoft Corporation. All rights reserved.
    
    C:\> dir %KEY_PATH%
     Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\keys 03/26/2013 12:29 AM <DIR> . 03/26/2013 12:29 AM <DIR> .. 03/26/2013 12:29 AM 596 extSQLKey.snk 1 File(s) 596 bytes 2 Dir(s) 51,610,288,128 bytes free
  2. In the master database, create a master key (if one does not already exist).
    USE [Master]
    Go
    
    CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = 'some_secure_password';
    
    Command(s) completed successfully.
  3. Still in master, create an asymmetric key.
    CREATE ASYMMETRIC KEY extSQLKey
    FROM FILE = 'C:\Programming\c#\SQLCLRIntegrationExample\keys\extSQLKey.snk';
    
    Command(s) completed successfully.
  4. Also in the master database, create a login from the asymmetric key.
    CREATE LOGIN extSQLLogin
    FROM ASYMMETRIC KEY extSQLKey;
    
    Command(s) completed successfully.
  5. Give the login just created EXTERNAL ACCESS ASSEMBLY permission.
    GRANT EXTERNAL ACCESS ASSEMBLY to extSQLLogin;
    
    Command(s) completed successfully.
  6. Build your assembly like before but this time sign it with your strong name key.
    cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
    
    SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
    SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies
    SET KEY_PATH=C:\Programming\c#\SQLCLRIntegrationExample\keys
    
    csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs
    
    Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.
    
    csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs
    
    Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.
    
    DIR %DLL_PATH%
     Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 01:00 AM 3,584 Math.dll 03/26/2013 01:00 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,609,612,288 bytes free
  7. The assembly is now ready to be deployed. The assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set.

 

Create Assemblies in SQL Server

With the assemblies built, the next step is to create the assembly in a SQL Server database along with the external procedure and/or function wrappers to run the assembly.

MathAsm

Use [DevDB]
Go

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE FUNCTION Factorial(@x FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME MathAsm.Math.Factorial;
Go

Command(s) completed successfully.

StoredProceduresAsm

Use [DevDB]
Go

CREATE ASSEMBLY StoredProceduresAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\StoredProcedures.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE PROCEDURE GetVersion AS EXTERNAL NAME StoredProceduresAsm.StoredProcedures.GetVersion;
Go

CREATE FUNCTION addTax(@originalAmount FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME StoredProceduresAsm.StoredProcedures.addTax;
Go

Command(s) completed successfully.

Note that the external reference for creating the procedure and wrapper functions is in the form:

[EXTERNAL NAME] assembly_name.class_name.method_name

Run Example Assemblies in SQL Server

With the assemblies and procedures / functions wrappers created in SQL Server, run the following T-SQL to execute the assemblies.

SELECT [DevDB].[dbo].[Factorial](5) AS "Factorial"
Go

Factorial ----------------- 120

EXEC GetVersion
Go

(No column name) ----------------------------- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

SELECT [DevDB].[dbo].[addTax](10.00) AS "Original + Tax"
Go

Original + Tax ----------------------------- 10.86

Drop Assemblies from SQL Server

The following T-SQL can be used to drop the previously created objects in the example Class.

MathAsm

Use [DevDB]
Go

DROP FUNCTION [Factorial];
Go

DROP ASSEMBLY [MathAsm];
Go

Command(s) completed successfully.

StoredProceduresAsm

Use [DevDB]
Go

DROP PROCEDURE [GetVersion];
Go

DROP FUNCTION [addTax];
Go

DROP ASSEMBLY [StoredProceduresAsm];
Go

Command(s) completed successfully.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff’s other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor’s degree in Computer Science and Mathematics.

from:http://www.idevelopment.info/data/SQLServer/DBA_tips/Programming/PROG_20.shtml

Configure SQL Server Express to allow remote tcp/ip connections on port 1433

The following article explains how to allow SQL Server Express to accept remote connections over TCP/IP for port 1433. By default, when SQL Server Express is installed it gerates a random port to listen on. In addition, SQL Server Express only listens for connection on localhost. Using the SQL Server Configuration Manager, you will need to tell SQL Server Express to use port 1433.

 

To allow SQL Server Express to accept remote connections, please follow these steps:

1) Log into your server through Remote Desktop Connection (instructions for connecting to your server through RDC can be found here).

2) Click Start, Programs, Microsoft SQL Server 2005/2008/2012 and select SQL Server Configuration Manager.

 

3) Select SQL Server Network Configuration

 

4) Double click on Protocols for SQLEXPRESS

 

5) Right click TCP/IP and select Properties

6) Scroll down to IPAll make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433.

7) Click OK

8) Make sure that port: 1433 is enable on your VDS firewall (instructions for enabling firewall ports can be found here).

9) Mixed mode authentication must also be enabled for remote connections (instructions for enabling firewall ports can be found here).

10) Make sure that the SQL Browser is enabled and running.

from:http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx

Refer:http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/

sql server 判断数据库,表,列,视图是否存在

1 判断数据库是否存在
if exists (select * from sys.databases where name = ‘数据库名’)
drop database [数据库名]

2 判断表是否存在
if exists (select * from sysobjects where id = object_id(N'[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [表名]

3 判断存储过程是否存在
if exists (select * from sysobjects where id = object_id(N'[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [存储过程名]
4 判断临时表是否存在
if object_id(‘tempdb..#临时表名’) is not null
drop table #临时表名

5 判断视图是否存在

–判断是否存在’MyView52’这个试图
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N’MyView52′)
PRINT ‘存在’
else
PRINT ‘不存在’
6 判断函数是否存在
— 判断要创建的函数名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function [dbo].[函数名]

7 获取用户创建的对象信息

SELECT [name],[id],crdate FROM sysobjects where xtype=’U’
/*
xtype 的表示参数类型,通常包括如下这些
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束(类型是 K)
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束(类型是 K)
V = 视图
X = 扩展存储过程
*/

8 判断列是否存在
if exists(select * from syscolumns where id=object_id(‘表名’) and name=’列名’)
alter table 表名 drop column 列名

9 判断列是否自增列
if columnproperty(object_id(‘table’),’col’,’IsIdentity’)=1
print ‘自增列’
else
print ‘不是自增列’

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(‘表名’) AND is_identity=1

10 判断表中是否存在索引
if exists(select * from sysindexes where id=object_id(‘表名’) and name=’索引名’)
print ‘存在’
else
print ‘不存在’

11 查看数据库中对象

SELECT * FROM sys.sysobjects WHERE name=’对象名’ SELECT * FROM sys.sysobjects WHERE name=’对象名’

Table Value Parameters in SQL Server 2008 and .NET (C#)

Problem

We recently upgraded our database to SQL Server 2008 and I want to update our data access C# code to use Table Value Parameters with our stored procedures. In a previous tip I saw that Table Value Parameters were used with a Data Warehousing example. Can you show me how to implement Table Value Parameters with my .NET Application to insert multiple records using one round-trip?

Solution

Table Value Parameters is a new feature for developers in SQL Server 2008. It allows you to pass read-only table variables into a stored procedure. In the past I have used a comma delimited string or XML to simulate this purpose. I would have to parse out the string into a temp table similar to this example.

Once you have the table parameter passed into the stored procedure you can leverage the Table Value Parameter just like any other table except you cannot modify the parameter as it’s read-only. Below we will apply a common example to bulk insert data using one round trip.


Create table and table type

The following is a sample table that we will use in this example to insert items.

CREATE TABLE [dbo].[Items](
 [ItemID] [int] NOT NULL,
 [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
 [ItemID] ASC
)) ON [PRIMARY]

In order to use table value parameters you have to create a table type. The table type is used to describe the structure of the table value parameter. This is similar to making a strong type.

CREATE TYPE [dbo].[TVP_Items] AS TABLE(
 [ItemID] [int] NOT NULL,
 [Name] [nvarchar](50) NULL
)
GO

Using TVP in T-SQL

Now that you have a table type you have to declare an instance to use it. The following is an quick example that declares an instance of the table type an populates it with some values. Later we will use C# to create a data table that will populate an instance of a table type.

-- Declare instance of TVP_Items Table Type
DECLARE @TVP TVP_Items 
-- Add some sample values into our instance of Table Type.
INSERT INTO @TVP (ItemID, Name) VALUES (1, 'Hat'), (2, 'T-Shirt'), 
(3, 'Football'), (4, 'Jersey')
-- show values that exist in table type instance.
SELECT * FROM @TVP

Using TVP in Stored Procedure

In order to use table value parameters in a stored procedure you have to declare an instance of the table type and specify it as read-only. It is mandatory for the table value parameter to be read-only so you cannot modify the data inside of the table type variable inside the stored procedure.

Below we will insert the data in the table value parameter into the dbo.items table.

CREATE PROCEDURE [dbo].[InsertItemsTVP] @ItemTVP TVP_Items READONLY
AS
BEGIN
 INSERT INTO dbo.Items (ItemID, Name)
 SELECT ItemID, Name
 FROM @ItemTVP
END
GO

Table Value Parameters in .NET (C#)

The following code below generates a data table in C# and it includes four rows. A data table is a common data type used to simulate a table. This data table will be used as our table value parameter and will be used by the stored procedure created above. The data table is not the only type that can be used for table value parameters in C#. The DataReader and list types are also acceptable.

            
DataTable _dt;
// create data table to insert items
_dt = new DataTable("Items");
_dt.Columns.Add("ItemID", typeof(string));
_dt.Columns.Add("Name", typeof(string));
_dt.Rows.Add(4, "SuperBowl 9 Hat");
_dt.Rows.Add(5, "SuperBowl 10 T-Shirt");
_dt.Rows.Add(6, "SuperBowl 13 Towel");
_dt.Rows.Add(7, "SuperBowl 14 Helmet");

Now that we have our data table created we can move on to the data access code. The majority of the code listed below will be the same for the majority of your data access code. The only difference is we will specify that the input type is sql data type as structured and we will pass in our data table to the input parameter. The two lines you need to focus on are underlined in the code section below.

SqlConnection con;
// modify connection string to connect to your database
string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;";
con = new SqlConnection(conStr);
 con.Open();
using (con)
{                
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand("dbo.InsertItemsTVP", con);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ItemTVP", _dt); //Needed TVP tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
sqlCmd.ExecuteNonQuery();
 }
con.Close();

SQL Profiler

When you use this tip you can setup SQL Server Profiler to capture the .NET calls. You will see the following three calls. You should notice that they are very similar to the T-SQL calls above.

SQL Profiler

Next Steps
  • Check out my blogfor more on SQL Server
  • Click hereto download the sample code used in this tip.
  • Check out several linkson Table Value Parameters
  • Click herefor an TVP example for Data Warehouse
  • Click here for an ASP.NET example using Table Value Paramerters

from:http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

 

Introduction to Indexes

Good indexes are the key to good performance in SQL Server and the key to creating good indexes is to understand what indexes are and how SQL Server uses them to evaluate queries.

In this first part of a three part series, I’m going to look at the very basics of what indexes are, what types exist in SQL and how they’re used.

  What is an index?

An index is a structure within SQL that is used to quickly locate specific rows within a table. It can be useful to imaging an index at the back of a textbook when thinking about SQL indexes. They both serve the same purpose – to find specific information quickly.

  General Structure

An index is defined on one or more columns, called key columns. The key columns (also referred to as the index key) can be likened to the terms listed in a book index. They are the values that the index will be used to search for. As with the index found at the back of a text book (see figure 1), the index is sorted by the key columns.

Index sample

Figure 1: Book index.             Image copyright Simple Talk publishing.

If an index is created with more than one key column, it is known as a composite index.

The general structure of an index is that of a balanced tree (b-tree). The index will have a single root page, zero or more intermediate levels and then a leaf level. A page is an 8 kilobyte chunk of the data file, with a header and footer and is identified by a combination of File ID and Page number.

Index Structure

            Figure 2: Index Structure

Note: Commonly the root page is shown at the top of the tree diagram and the leaf pages at the bottom. Think of it as an inverted tree.

In the leaf level, there’s one entry for each row in the index1. The entries in the index are ordered logically2 in the order of the index key.

The non-leaf levels of the index contain one row per page of the level below, referencing the lowest index key value on each page.  If all of those rows fit onto a single page, then that page is considered the root and the index is only two levels deep. If all of those rows will not fit on a single page, then one (or more) intermediate levels are added to the index.

The number of levels in an index is referred to as the depth of the index. This is an important consideration for evaluating the efficiency of the index. The index illustrated in figure 2 has a depth of 3.

(1)   With the exception of SQL 2008’s filtered indexes, an index will have the same number of rows at the leaf level as the table.

(2)   I’m using the phrase ‘logically ordered’ because the index does not necessarily define the physical storage of the rows. The rows are stored in a way that SQL can retrieve them ordered.

  Clustered and nonclustered

There are two main types of indexes in SQL Server, the clustered index and the nonclustered index

Clustered indexes define the logical order of the table. The leaf level of the clustered index has the actual data pages of the table. Because of this there can only be one clustered index per table. A table that does not have a clustered index is referred to as a heap.

Nonclustered indexes are separate from the table. The leaf level of a nonclustered index has a pointer as part of each index row. That pointer is either the clustered index key in the cases where the base table has a clustered index or the RID (Row Identifier) in the cases where the table is a heap. The RID is an 8-byte structure comprised of File ID, Page Number and Slot Index and will uniquely identify a row in the underlying heap. Either way, the each row of a nonclustered index has a reference to the complete data row.

  Index Limits

There are a number of built-in limitations on indexes

Key size

The size of an index key is limited to a maximum of 900 bytes and a maximum of 16 columns. This is definitely a limit, not a goal, as the larger the index key gets, the more pages in the index and the deeper the index tree. As the number of pages and the depth of the tree increases so the index becomes less efficient to use. Larger indexes also use more storage space and result in less efficient use of SQL’s data cache.

Number of indexes

In SQL 2005 and earlier there was a limitation of 250 indexes per table, one clustered and 249 non-clustered. In SQL 2008, with the addition of filtered indexes, that limitation was increased to 1000, one clustered and 999 non-clustered indexes.

Both of these limits are very high and there are few circumstances where a well-designed system should approach that limit.

The reason for this is twofold.

·        As the number of indexes increases so the total size occupied by the table (with all of its indexes) increases. Sure, hard drives are cheap and storage is abundant but increasing the size of a database has other effects, Maintenance operations (backups, restores, consistency checks and index rebuilds) all take longer as the size of a database increases.

·        Indexes have to be kept up to date as data changes and the more indexes there are on a table, the more places the data has to be changed. If there are 10 nonclustered indexes on a table, an insert must be done in 11 places (the table and each of those nonclustered indexes). On databases that are mostly read-only (decision support, data warehouses) that may be acceptable. On databases that have frequent inserts, updates and deletes (OLTP systems), the overhead impose by multiple indexes may not be acceptable

  How SQL uses indexes

If a table does not have index, the only way to find all occurrences of a value within a table is to read the entire table. If a table has an index, it speeds up the locating of values within that index in two ways.

1.      The index is sorted in the order of the key columns. This means that once all the matching values have been found, the remaining portion of the table can be ignored. This is the same as a telephone directory, where once all entries with a particular surname have been found, the rest of the book can be ignored as no further matches are possible

2.      The tree structure of the index allows a divide-and-conquer approach to locating rows, where large portions of the table can be quickly excluded from the search. This is illustrated in Figure 3

There are four basic operations that SQL can do on an index. It can scan the index, it can seek on the index, it can do lookups to the index and it can update the index

  Scans

An index scan is a complete read of all of the leaf pages in the index. When an index scan is done on the clustered index, it’s a table scan in all but name.

When an index scan is done by the query processor, it is always a full read of all of the leaf pages in the index, regardless of whether all of the rows are returned. It is never a partial scan.

A scan does not only involve reading the leaf levels of the index, the higher level pages are also read as part of the index scan.

  Seeks

An index seek is an operation where SQL uses the b-tree structure to locate either a specific value or the beginning of a range of value. For an index seek to be possible, there must be a SARGable3 predicate specified in the query and a matching (or partially matching) index. A matching index is one where the query predicate used a left-based subset of the index columns. This will be examined in much greater detail in a part 3 of this series.

The seek operation is evaluated starting at the root page. Using the rows in the root page, the query processor will locate which page in the next lower level of the index contains the 1st row that is being searched for. It will then read that page. If that is the leaf level of the index, the seek ends there. If it is not the leaf then the query processor again identifies which page in the next lower level contains the specified value. This process continues until the leaf level is reached.

Once the query processor has located the leaf page containing either the specified key value or the beginning of the specified range of key values then it reads along the leaf pages until all rows that match the predicate have been returned. Figure 2 shows how a seek would be done on an index when searching for the value 4.

Index Seek

If the index contains all the columns that the query needs, then the index is said to be covering for that query. If the index does not contain all the columns then SQL will do a lookup to the base table to fetch the other columns in order to process the query.

(3)   SARGable is a made-up word, constructed from the phrase Search ARGument. It refers to a predicate that is of a form that SQL can use for an index seek. For more details see: http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx

  Lookups

Lookups occur when SQL uses an index to locate rows affected by a query but that index does not contain all the columns required to satisfy the query, that is the index is not covering for that query. To fetch the remaining columns, SQL does a lookup to either the clustered index or heap.

A lookup to a clustered index is essentially a single row clustered index seek and it is always a single row seek. So if a lookup is needed for 500 rows, that involves 500 individual clustered index seeks.

  Updates

Anytime that a row is changed, those changes must be made not only in the base table (clustered index or heap) but also in any index that contains the columns that were affected by the change.  This applies to insert, update and delete operations.

  Considerations for creating indexes

I’ll be going into more detail on considerations for indexes in the next two parts, but in general:

  •     Clustered index should be narrow, because the clustering key is part of all nonclustered indexes.
  •     Composite nonclustered indexes are generally more useful than single column indexes, unless all queries against the table filter on one column at a time.
  •     Indexes should be no wider than they have to be. Too many columns wastes space and increases the amount of places that data must be changed when an insert/update/delete occurs.
  •     If an index is unique, specify that it is unique. The optimiser can sometimes use that information to generate more optimal execution plans.
  •     Be careful of creating lots of indexes on frequently modified tables as it can slow down data modifications.

In part 2 of this series I’ll be looking in more detail into clustered indexes. What they are, how they differ from nonclustered indexes and what the considerations are for creating a clustered index.

  The Series

Be sure you read all parts of this series:

from:http://www.sqlservercentral.com/articles/Indexing/68439/