Search

Saturday, April 30, 2011

SQL 2008 R2: Temp Database Configuration

Temp databases are very important to some applications, as they are used as a scratch or buffer space. Other applications may not use them hardly at all, so it really depends on your environment.

One rule of thumb I use for VMware environments is one TempDB for every vCPU presented to your SQL server. Since many virtualized SQL environments will have multiple processors, you want multipleTempDB files for SQL to use.

Using SQL studio, you can run the script below to automatically add a second TempDB file, and also expand the default TempDB. Of course, adjust the size and growth parameters to fit your situation.

If you want 8GB of TempDB space and have two CPUs, then change the existing TempDB to 4GB and create a second that is also 4GB. You want the TempDBs all of equal size since SQL weights their usage based on their size.

-----
USE master;
GO
ALTER DATABASE tempdb
ADD FILE
(NAME = tempdev2,
FILENAME = 'T:\Microsoft SQL Server\MSSQL\Data\tempdb2.mdf',
SIZE = 2048MB,
FILEGROWTH = 512MB);

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 2048MB,
FILEGROWTH = 512MB);

ALTER DATABASE tempdb
MODIFY FILE

(NAME = templog,
SIZE = 512MB,
FILEGROWTH = 128MB);
GO

Different Options for Query Results in SQL Server Management Studio

Here are some of the different option settings in SSMS when dealing with result sets.

Change Default Output

SQL Server Management Studio currently supports query execution results to be displayed in three different ways: Results to Grid, Results to Text and Results to File.
By default SQL Server Management Studio is configured to display query results in Grid format.  If you want to change the default to some other option follow these steps.
1. In SQL Server Management Studio, under Tools menu, click Options as shown in the snippet below.




2. In the Options dialog box, expand Query Results, expand SQL Server and then select General as shown in the snippet below. In the right side panel choose one of the three options shown below in the Default Destination for results drop down list and click OK to save the changes as shown in the snippet below. The changes will go into effect once you open a New Query window.




Query Result Options for Results to Text

In this format you have the option to display the results in a different tab as well as setting different output options.
In the Options dialog box, expand Query Results, expand SQL Server and then select Results to Text tab as shown in the snippet below. In the right side panel first select the checkbox for Display results in a separate tab and then select the checkbox for Switch to results tab after the query executes and then click OK. If you would like to also display the column name in the result set then choose the option Include column headers in the result set as shown in the snippet below.





The options you can use in the Text format are:
  1. Output format: - In the above snippet you could see that by default the output is displayed as columns aligned. Some of the other options that are available are Comma Delimited, Tab Delimited, Space Delimited andCustom Delimited. If you choose the Custom Delimited option in the Output format drop down list then you need to specify a character of your choice for the delimiter in the Custom delimiter text box.
  2. Include column headers when copying or saving the results: - If this option is selected, then whenever the results are copied to clipboard or it is saved to a file the column headers are also copied along with the results.
  3. Include the query in the result set: - The text of the query is displayed as part of query output under the messages tab.
  4. Scroll as results are received: - If this option is selected, then the display focuses on the most recently returned records at the end of the results set.
  5. Right align numeric values: - If this option is selected, then numeric values will be aligned to the right of the column.
  6. Discard results after query executes: - If this option is selected, then the query results are not displayed in the reviewing pane.
  7. Display results in a separate tab: - If this option is selected, then the result set after query execution will be displayed in a new tab instead of at the bottom of the query window.
    1. Switch to results tab after the query executes: - If this option is selected, then after the query execution the screen focus will be set to the results tab.
  8. Maximum number of characters displayed in each column: - This sets the maximum characters to display for any one column.  So if you have a column that is 500 characters this will only show the first 256 if you keep the default setting.
Here is a sample output for the Text format:




Query Result Options for Results to Grid

In this format you also have the option to display the results in a different tab as well as setting different output options.
In the Options dialog box, expand Query Results, expand SQL Server and then select Results to Grid tab as shown in the snippet below. In the right side panel first select the checkboxes for Display results in a separate tab and then select the checkbox for Switch to results tab after the query executes and then click OK to save the changes.





The options you can use in the Grid format are:
  1. Include the query in the result set: - The text of the query is displayed as part of query output under the messages tab.
  2. Include column headers when copying or saving the results: - If this option is selected, then whenever the results are copied to the clipboard or it is saved to a file the column headers are also copied along with the results.
  3. Quote strings containing list separators when saving .csv results: - This will enclose strings with double quotes if the string includes a comma.
  4. Discard results after execution: - If this option is selected, then the query results are not displayed in the reviewing pane.
  5. Display results in a separate tab: - If this option is selected, then the result set after query execution will be displayed in a new tab instead of at the bottom of the query window.
    1. Switch to results tab after the query executes: - If this option is selected, then after the query execution the screen focus will be set to the results tab.
  6. Maximum Characters Retrieved:
    1. Non XML data: - This is the maximum number of characters to retrieve for one column.
    2. XML data: - This is the maximum amount of data to retrieve for an XML data type.
Here is a sample output for the Grid format:



Friday, April 29, 2011

SQL Server 2008 - New Datatypes

Major enhancement in this front is new Date and Time datatypes and related functions.

Here Overview of the new Date and time datatypes

(a) Date : Date only which can store date raging from 0001-01-01 through 9999-12-31 ie. January 1, 1 A.D. through December 31, 9999 A.D. The storage size is 3 byte and Date datatype gives One day accuracy.

(b) Datetime2 : Its an extension to existing Datetime datatype and datetime2 can have larger range and accuracy. Existing Datetime range limitation is January 1, 1753, through December 31, 9999. Datetime2 provides range from 0001-01-01 through 9999-12-31. The storage size is 8 byte for both datetime2 and datetime. For all new developments its recommended to use Datetime2 because is more portable and it gives more second precision.

(c) Time : Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. This provides more accuracy in terms of time than existing datetime datatype. Time datatype ranges 00:00:00.0000000 through 23:59:59.9999999 where as existing datetime data type only gives 00:00:00 through 23:59:59.997. It also provides a feature in which user can specify the precision they wanted from 0 to 99 nanoseconds. The storage size is 5 bytes, fixed, is the default with the default of 100ns fractional second precision.
SELECT CAST(getdate() AS time) AS 'time' – Default
Result : 09:43:13.6230000
SELECT CAST(getdate() AS time(2)) AS 'time' – User specific 2 percision
Result : 09:43:57.69
In the above example, the figure within brackets (ie 2) specify what precision we want. By default (if we not specify) its 7.

(d) datetimeoffset : Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. datetimeoffset have three parts. Date part , time part and timeoffset part. Datetimeoffset ranges from 0001-01-01 through 9999-12-31 and it takes 10 byte for storage.

(e) Hierarchyid : I have not tried this yet, for now I just wanted to mention that there is a new system datatype called Hierarchyid in SQL Server 2008. Books online has very detailed section on this, I need to check that

Conclusion

These date and time datatypes are definitly mosuse wanted datatypes for long time. For any new development purpose use these new datatypes available. I need to explore more with these datatypes. Books online has very detailed documentation with examples which needs to be referred.

Thursday, April 28, 2011

Check disk Space though SQL Server

I had used WMI script to do this, Copy the vbs script and save it to a location as disksp.vbs and use this location in the sql script to get the result.

VBS Script


Const HARD_DISK = 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")


Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")


For Each objDisk in colDisks
Wscript.Echo objDisk.DeviceID & " " & mid((objDisk.size)/1048576,1,10) & " " & mid((objDisk.Freespace)/1048576,1,10)
Next

SQL Script
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##Temp')
DROP TABLE ##Temp
CREATE TABLE ##Temp(diskspace VARCHAR(200))
INSERT ##Temp
EXEC master.dbo.xp_cmdshell 'cscript C:\disksp.vbs' 
SET ROWCOUNT 3
DELETE ##Temp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##Temp2')
DROP TABLE ##Temp2
CREATE TABLE ##Temp2(Driveletter VARCHAR(12),TotalDiskSpace_in_MB DECIMAL(18,4), Freespace_in_MB DECIMAL(18,4))
INSERT INTO ##Temp2 SELECT SUBSTRING(diskspace,1,3) , CONVERT(Decimal,SUBSTRING(diskspace,4,10)),
CONVERT(Decimal,SUBSTRING(diskspace,15,10)) FROM ##Temp WHERE diskspace IS NOT NULL
SELECT * FROM ##Temp2


Driveletter TotalDiskSpace_in_MB Freespace_in_MB
C:                         39998.0000                                 6526.0000
D:                         55004.0000                                 26942.0000
E:                         57624.0000                                 26569.0000



Tuesday, April 26, 2011

Hiding System Objects in Object Explorer in SSMS


You may have noticed that once you are connected to a SQL Server Instance using SQL Server Management Studio, in the Databases node of Object Explorer you can see system objects such as the system databases as shown in the snippet below.


You can hide system objects in Object Explorer by following the below mentioned steps:
1. In SQL Server Management Studio, under Tools menu, click Options as shown in the snippet below.


2. In the Options dialog box, expand Environment and then select the General tab as shown in the snippet below. Select Hide system objects in Object Explorer and then click OK.


3. In the Microsoft SQL Server Management Studio dialog box, click OK to acknowledge that the changes will come into effect once you restart SQL Server Management Studio.


4. Next, go ahead and close SQL Server Management Studio. When you reopen SQL Server Management Studio once you are connected to SQL Server Instance you will not see the System Objects in Object Explorer in SQL Server Management Studio as shown in the snippet below.

FILESTREAM Data Type in SQL Server 2008


It has been a challenge to maintain files, documents along with the records in the database and gradually increasing the need of digitizing the data leads to the need for a more manageable system. Once a photographer asked me for a system that can manage all his customer data and the associated video clips, photos, etc. that go with each customer. Also he needed to maintain data for his associates and wanted a system that would be very efficient system to maintain and, more importantly, would allow the data to be easily backed up.
Much of the data that is created by the photographer above is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its customer record which is structured data. Due to this separation it can lead to data management complexities in areas such as transactional consistency and database backups. Transactional consistency means if record gets updated, all parts of the record are updated. Backup for files and the database need to be done separately OR some external application has to manage the backup of both the storage systems. Well you might think if we can use the data type BOLB of SQL SERVER which allows us to store data upto 2 GB. But the problem with this is that file streaming becomes slow and performance of the database can be affected very badly.
SQL Server 2008 introduces a new data type: FILESTREAM. FILESTREAM allows large binary data (Documents, images, videos etc) to be stored directly in the Windows file system. This binary data remains an integral part of the database and maintains transactional consistency. FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files that can be accessed using an NTFS streaming API. Using the NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup.
What is FILESTREAM?
FILESTREAM is a new datatype in SQL SERVER 2008. To use FILESTREAM, a database needs to contain a FILESTREAM filegroup and a table which contains a varbinary(max) column with the FILESTREAM attribute set. This causes the Database Engine to store all data for that column in the file system, but not in the database file. A FILESTREAM filegroup is a special folder that contains file system directories known as data containers. These data containers are the interface between Database Engine storage and file system storage through which files in these data containers are maintained by Database Engine.
What FILESTREAM does?
By creating a FILESTREAM filegroup and setting a FILESTREAM attribute on the column of a table, a data container is created which will take care of DML statements.
FILESTREAM will use Windows API for streaming the files so that files can be accessed faster. Also instead of using SQL SERVER cache it will use Windows cache for caching the files accessed.
When you use FILESTREAM storage, consider the following:
  • When a table contains a FILESTREAM column, each row must have a unique row ID.
  • FILESTREAM data containers cannot be nested.
  • When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.
  • FILESTREAM filegroups can be on compressed volumes.
How to use FILESTREAM
Step 1) Enabling FILESTREAM datatype
Before using FILESTREAM we need to enable it as FILESTREAM is by default disabled in SQL SERVER 2008. Enabling the instance for FILESTREAM is done by using the system store procedure “sp_FILESTREAM_configure”. The syntax is given as below:

 USE MASTER 
GO
 EXEC sp_FILESTREAM_configure @enable_level = 3
There are various enable levels:
0 – Disable FILESTREAM
1 – Allow T-SQL only to access files
2 – Allow T-SQL as well File system access Locally
3 – Allow T-SQL as well File system access Locally as well as remotely
OR
Same thing can be done by setting the property of FILESTREAM
i.e. Configurable level = Transact-SQL and file system

Fig 1: Configure FILESTREAM
STEP 2) Creating File Group
Now let us create a filegroup. As discussed earlier Filegroup is like a folder which acts as an interface between Windows file system and SQL server.
 USE MASTER
 GO
 CREATE DATABASE TEST_DB ON PRIMARY
 ( NAME = TEST_DB_data,
 FILENAME = N’C:\ TEST_DB_data.mdf’),
 FILEGROUP FG_1
 ( NAME = TEST_DB_REGULAR,
 FILENAME = N’C:\ TEST_DB_data_1.ndf’),
 FILEGROUP FG_2 CONTAINS FILESTREAM
 ( NAME = FS_FILESTREAM,
 FILENAME = N’C:\TEST_FS’)
 LOG ON
 ( NAME = FS_LOG,
 FILENAME = N’C:\TEST_FS_log.ldf’);
 GO
The statement below means that a FileGroup of type FILESTREAM will be created i.e. a data container named “TEST_FS” is created, which will act as an interface between Database Engine and Windows file system. The Database Engine can manage the files through this folder. It is necessary to specify the CONTAINS FILEGROUP clause for least one filegroup.
FILEGROUP FG_2 CONTAINS FILESTREAM
Note:
• The only difference in the statement above compared to a normal CREATE DATABASE statement is the filegroup creation for FILESTREAM objects.
• There should not be any folder by the name TEST_FS as it will be created by SQL SERVER and permission will be granted. If the database is deleted then SQL SERVER will delete the related files and folders.
• Please note that if you try to create this database by specifying a path for the FILESTREAM files that is not on NTFS, you will get the error message: “The path specified by ‘d:\TEST_FS’ cannot be used for FILESTREAM files because it is not on NTFS.”
Below is the figure that shows the folder that is created after execution of above DDL statement

Fig 2: Special Filegroup folder created.
For the FILESTREAM filegroup, the FILENAME refers to the path and not to the actual file name. It creates that particular folder - from the example above, it created the C:\TEST_FS folder on the filesystem. And that folder now contains a FILESTREAM.hdr file and also a folder $FSLOG folder.
Important Note: The FILESTREAM.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.
Adding FILESTREAM filegroup to existing database
If you already have a database, you can add a FILESTREAM filegroup to it using ALTER DATABASE command.
 ALTER DATABASE [TEST_DB]
          ADD FILEGROUP FG_2 CONTAINS FILESTREAM;
 ALTER DATABASE [TEST_DB]
          ADD FILE
                   (NAME = FS_FILESTREAM,FILENAME = N' C:\TEST_FS’
          ) TO FILEGROUP FG_2;
Step 3) Creating a Table
Once the database is ready we need a table having a column of Varbinary(max) with FILESTREAM attribute where the data will be stored. Let us create a table and add data into it.
 USE TEST_DB
 GO
 CREATE TABLE FILETABLE
 (
 ID     INT IDENTITY,
 GUID    UNIQUEIDENTIFIER ROWGUIDCOL NOTNULL UNIQUE,
 DATA     VARBINARY(MAX) FILESTREAM
 );
The table definition needs a ROWGUIDCOL column - this is required by FILESTREAM. The actual data is stored in the 3rd column DATA. Any data manipulation in this column will update the file stored in the Windows system.
 INSERT INTO FILETABLE (GUID, DATA) VALUES (NEWID(),NULL);
 INSERT INTO FILETABLE (GUID, DATA) VALUES (NEWID(),CAST(‘TEST DATA’ AS VARBINARY(MAX)));
Note: File will not be created for the data value NULL.
Execute select query on the table - FILETABLE, and you get the following output.
ID GUID DATA
———– ——————————————————————— ————————–
1 78909DBF-7B26-4CA9-A840-4D45930F7523 NULL
2 0B0F5833-1997-4C9C-A9A7-F2536D68CFED 0×4D592044554D4D592054455354
As you can see on the file system, additional folders have been created under TEST_FS folder. The filename will be the GUID id. For eg. If you see the second record, the filename will be 0B0F5833-1997-4C9C-A9A7-F2536D68CFED and in the DATA column the contents of file are stored.
When to use FILESTREAM?
When applications need to store large files i.e. larger than 1 MB and also don’t want to affect database performance in reading the data, the use of FILESTREAM will provide a better solution. Also one can use this for developing applications that use a middle tier for application logic.
For smaller files, still one can safely store in columns with datatype varbinary(max) BLOBs in the database which would provide better streaming performance for small files.
Advantages
FILESTREAM enables the database to store un-structured (files, documents, images, videos etc) data on the file systems and still use the SQL SERVER Engine.
It uses Windows API’s for streaming the files.
When manipulating files, instead of using the SQL server cache, it uses Windows system cache.
SQL Server backup and recovery models support these files also along with the database. Only a single backup command is issued to back up the database and the FILESTREAM data.
All insert, update, delete, search queries will also work for this unstructured data.
FILESTREAM data is secured by granting permissions at the table or column level, similar to the manner in which any other data is secured. Only if you have permissions to the FILESTREAM column in a table, you can access its associated files.
In SQL Server, FILESTREAM data is secured just like other data is secured: by granting permissions at the table or column levels. If a user has permission to the FILESTREAM column in a table, the user can open the associated files.
Disadvantages
FILESTREAM does not currently support in-place updates. Therefore an update to a column with the FILESTREAM attribute is implemented by creating a new zero-byte file, which then has the entire new data value written to it. When the update is committed, the file pointer is then changed to point to the new file, leaving the old file to be deleted at garbage collection time. This happens at a checkpoint for simple recovery, and at a backup or log backup.
Limitations
Database mirroring cannot be configured on databases with FILESTEAM data.
Database snapshots are not supported for FILESTEAM data.
Native encryption is not possible by SQL SERVER for FILESTREAM data.
Conclusion
This article has explained about the new FILESTREAM datatype of SQL SERVER 2008 which provides easy way to maintain unstructured data along with the structured data as it uses Windows API to store files and manages the data into SQL SERVER database. As explained in the article FILESTREAM is easy to understand and implement in applications.

Monday, April 25, 2011

Choosing an Encryption Algorithm


Encryption is one of several defenses-in-depth that are available to the administrator who wants to secure an instance of SQL Server.


Encryption algorithms define data transformations that cannot be easily reversed by unauthorized users. SQL Server allows administrators and developers to choose from among several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.


No single algorithm is ideal for all situations, and guidance on the merits of each is beyond the scope of SQL Server Books Online. However, the following general principles apply:
  • Strong encryption generally consumes more CPU resources than weak encryption.
  • Long keys generally yield stronger encryption than short keys.
  • Asymmetric encryption is stronger than symmetric encryption using the same key length, but it is relatively slow.
  • Block ciphers with long keys are stronger than stream ciphers.
  • Long, complex passwords are stronger than short passwords.
  • If you are encrypting lots of data, you should encrypt the data using a symmetric key, and encrypt the symmetric key with an asymmetric key.
  • Encrypted data cannot be compressed, but compressed data can be encrypted. If you use compression, you should compress data before encrypting it.


    For more information about encryption algorithms and encryption technology, see Key Security Concepts in the .NET Framework Developer's Guide on MSDN.



    Clarification regarding DES algorithms:
    • DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. The DESX algorithm is not provided. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    • Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key.

    • Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.

      Ref: MSDN

Filtered Index in SQL Server 2008

If you see the Create Index Syntax in SQL Server 2008 books online, you can see a new option called “ [ WHERE ]” which stands for Filtered Index feature.

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Suppose you have a table which stored the history (not so relevant to OLTP application ) data also. The query on that table is mostly on the current data and you want to index only on current data for eg. DateofPurchace > ‘1-1-2007’ kind. Another example would be , you have a product catalog and you want to show only the active products to the customer.

Eg. 
CREATE NONCLUSTERED INDEX NCI_OrderDetailsFilteredIndex
ON OrderDetail (OrderDetailID, DateofPurchase)
WHERE DateofPurchase >’1-1-2007’

Filtered Index Feature Support
In general, the Database Engine and tools provide the same support for filtered indexes that they provide for nonclustered full-table indexes, considering filtered indexes as a special type of nonclustered indexes. The following list provides notes about tools and features that fully support, do not support, or have restricted support for filtered indexes.
• ALTER INDEX supports filtered indexes. To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.
• The missing indexes feature does not suggest filtered indexes.
• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice.
• Online index operations support filtered indexes.
• Table hints support filtered indexes, but have some restrictions that do not apply to non-filtered indexes. These are explained in the following section.

Saturday, April 23, 2011

Find Unindexed Foreign Keys

This script will find foreign keys (on referencing table) that are not indexed.  It looks for exact definition of index matching columns and order. It will also give you the Create Index Script.



SELECT  DISTINCT      /* remove dups caused by composite constraints */
        DB_NAME()                                   AS "database_name",
        OBJECT_NAME(foreign_keys.parent_object_id)  AS "table_name",
        foreign_keys."name"                         AS "fk_name"
        , 'CREATE NONCLUSTERED INDEX IX_' + CONVERT(NVARCHAR, OBJECT_NAME(foreign_keys.parent_object_id)) + '_' + CONVERT(NVARCHAR, O.NAME) + ' ON ' + CONVERT(NVARCHAR, 
        OBJECT_NAME(foreign_keys.parent_object_id)) + ' (' + CONVERT(NVARCHAR, O.NAME) + ' ASC)' AS [Index_Script] 
FROM    sys.foreign_keys                            AS foreign_keys
JOIN sys.foreign_key_columns                     AS foreign_key_columns
  ON foreign_keys."object_id" = foreign_key_columns.constraint_object_id
INNER JOIN Sys.Columns AS O ON O.Column_ID = foreign_key_columns.Parent_Column_ID AND O.Object_ID = foreign_key_columns.Parent_Object_ID 
WHERE NOT EXISTS (
SELECT 'An index with same columns and column order'
FROM    sys.indexes                                 AS indexes
JOIN sys.index_columns AS index_columns
 ON indexes."object_id" = index_columns."object_id"
WHERE foreign_keys.parent_object_id = indexes."object_id"
 AND indexes.index_id = index_columns.index_id
 AND foreign_key_columns.constraint_column_id = index_columns.key_ordinal
 AND foreign_key_columns.parent_column_id = index_columns.column_id
 AND   OBJECTPROPERTYEX(indexes."object_id",'IsMSShipped') = 0
 AND   indexes.is_hypothetical = 0
)


 AND    foreign_keys.is_ms_shipped = 0 ORDER BY OBJECT_NAME(foreign_keys.parent_object_id);

SQL Server 2008 – Table Locking enhancement

In SQL Server 2008 ALTER TABLE statement, you can find a new option called SET LOCK_ESCALATION. This is one of the enhancement in Locking in SQL Server 2008. This option can have three value, Auto,Table, Disable

FROM BOL
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Specifies the allowed methods of lock escalation for a table.

AUTO 
This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.
• If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.
• If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

TABLE 
Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

DISABLE 
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

Note : If you use partitions then After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table. For more information

Friday, April 22, 2011

SQL Server 2008 - Sparse colum

One of the major enhancement in database engine of SQL Server 2008 is Sparse column. It improve data retrival and reduce the storage cost. It also can be used with Filtered Index to improve the performance.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

How to Create sparse column. 

Simple , just mention sparse keyword in table creation or alter statement.

CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)

Note : There are many limitations as far as the implementation is concerned. like the column has to be null, cannot be included in Clustered index, merge replication,compression etc. Please refer the site mentioned above or BOL.

Learning SQL Server 2008


Congratulations to you for taking the initiative to learn about SQL Server 2008, while trying to balance both your current projects and future needs.  From all that we have seen with SQL Server 2008 based on our own research, our recent web cast and sessions at SQL PASS, SQL Server 2008 is offering more functionality with improved performance at the same price point as SQL Server 2005, so this should appeal to a variety of levels in your organization.  So let's jump into pulling together a plan to learn SQL Server 2008:
Plan to Learn SQL Server 2008
  • Define learning priorities - Depending on your role in the organization, it could take you down a few different paths.  Although to me the first priority would be to work through the upgrade and validate that the application is going to work properly, your goals maybe related to Reporting Services or Analysis Services.  So, setup your goals and work through them on a weekly basis.
  • Resources - A number of resources (tips, articles, web casts, white papers, books, conferences, training sessions, etc.) are currently available on SQL Server 2008, so take advantage of the work the trail blazers have already done.  Use this as a starting point and then start to see what it takes to meet your learning priorities.
  • Environment - Be sure to set aside a physical or virtual environment that you can use to work through the installation, upgrade, testing and learning.  Talk to your team members to see if you can get an old machine to use for the next few months.  This is a must have.  If you cannot get a place to work with SQL Server 2008, the learning process will be tough.
  • Time - Set aside time on a weekly basis.  If you are stretched thin on a number of projects be sure to set aside time on your calendar.  Make it a busy time on your calendar and if you can get away from your desk so you do not have any visitors, you will probably have a better learning experience.
  • Download the Community Technology Previews (CTP) - Besides the current CTP, two more are expected to be released based on information from the recent SQL PASS summit, so be on the lookout for them.
  • Upgrade and new feature set - Validate that the current application works properly before starting to learn about the new feature set.  Once you have become familiar with these items, think about where these features could benefit your current applications and/or your current operational needs.  With a basic understanding, start pulling together a plan on how to implement these technologies and show the value over the current techniques.  Also consider these new features as a new opportunity from an IT and business perspective.
  • Divide, conquer and share - If your organization uses the entire SQL Server platform, working through all of the details can become time consuming for a single person, so figure out a way to split up the platform and equally share the knowledge between the team members.  Something like a lunch and learn could be a good forum.
  • Daily Tasks and Coding Techniques - Pull out your scripts and make sure they will work as expected in SQL Server 2008.  Click through the GUI and find every button that may turn into a little tricks for you.  It is better to do it now when it is a sandbox then ask yourself what some button does when the SQL Server instance is in production.
  • Code Conversion - Check out some of the new code features and see if you can improve your coding with all that SQL Server 2008 has to offer.
  • Implement new features - Once you have the day to day tasks down, then start working through new features that make sense to your business, applications and users.  Just try one out at a time and see how things go.
  • User Groups - I know many of the local user groups are having sessions on SQL Server 2008.  If you have not attended a recent session, then consider a SQL Server 2008 topic.  If you have learned a great deal about SQL Server 2008, then consider delivering a presentation to help the rest of the local community.
  • Microsoft Release Event - I can remember a few of the Microsoft events related to SQL Server and other developer technologies.  They have all been informative and valuable to my learning.  You might run into some marketing, but all and all, I have gained many valuable nuggets at the events.  So be on the lookout for them, register and clear the time out on your calendar.