SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.


Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

  • geography
  • geometry
  • image
  • ntext
  • text
  • timestamp
  • user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax error
-- However, including an existing SPARSE column in a computed column is allowed
-- Cleanup

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Restore Error: Specified cast is not valid (SqlManagerUI)

As I always say, Errors are a great source of learning and I am fortunate to have you as my readers. Because one of my blog reader reported an interesting issue which is worth blogging. This is his initial email:

Hi Pinal,
Your blogs have been very helpful to me to find solution of almost any SQL problem so far. This time, I am writing directly to see if you have time and provide some help to me.
I have a backup from database in SQL Server 2008 R2. When I want to restore this backup to SQL Server, I get this error: “Error: Specified cast is not valid. (SqlManagerUI)” How to I resolve this error?

We then exchanged various mails with screenshots, query, output and with his patience, we were able to find the cause of the issue. Here we go to what happened behind the scenes:

Here were the steps to restore the database:

cast error 01 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

As soon as OK was clicked, this was the error.

cast error 02 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

If we click on red cross icon at left bottom, we would see below

cast error 03 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Here is the partial text of the message.

Specified cast is not valid. (SqlManagerUI)
Program Location:
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

As we can see above that SSMS is trying to populate the grid with the details about the backup. When I put profiler on my machine, it was running Restore Headeronly command on the selected file. So, I asked to run the command manually. You can refer my earlier blog for this SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

When we ran the command, we found below

cast error 04 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Above could be because of two reasons (which I found)

  1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
  2. Backup media is corrupted.

Solution of 1st one is easy – restore it on same or higher version. 2nd one is a difficult situation because you need to look for another good/restorable backup. In my case, it was first situation so we were good.

Another possible reason of “Specified cast is not valid” would be password protected backups. In such backups, headeronly would return “*** PASSWORD PROTECTED ***” in the first column as shown below.

cast error 05 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

So, next time you ever see any UI error, find the T-SQL statement by using profiler and run that directly to see “real” error message.

Have you ever come across situation where UI was giving some misleading error? Please comment and share your knowledge.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #022 – How to Get Started with Big Data?

Big data is one of the most popular subject in recent time and everybody wants to get started on this subject. During recent interviews there are plenty of the questions with related to Big Data. Here is the most popular question which I receive on this subject.

Question: How to get started with Big Data?

Answer: Earlier last year I wrote timeless series on the subject Big Data. Here is the link to the entire series.

 bigdataimages Interview Question of the Week #022   How to Get Started with Big Data?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error Msg 511 using SQL Server Sparse columns

When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.

The mail stated they are getting:

Msg 511, Level 16, State 1, Line 6
Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.

Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.

When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:

--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse

msg511 sparse 01 SQL SERVER   Error Msg 511 using SQL Server Sparse columns

A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.

So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:

  • Create a new table for the operation
  • Insert into new table using the old table
  • Delete the old table and rename the new table

If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using 20 Logical Processors Based on SQL Server Licensing

It is second to human nature to utilize every bit of juice for what we pay. You buy something and you would like to get the best out of it right? If you start thinking from an organizations point of view, they think they should buy something which satisfies their needs. That is the only reason why organizations do capacity planning for their applications. Hardware resources are not cheap and it is difficult to over budget on them. Below is a real world query that had come to me.

One of my friend contacted me and told that he is using task manager and seeing that out of 64 processor only 20 processors are shown as utilized for a SQL Server machine. Whenever someone contacts me for any issue, I always want to know their environments details and ERRORLOG gives me a fair idea about it. If you don’t know where ERRORLOG is then you may want to read my earlier blog.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

So, he sent me an ERRORLOG and I found below message over there.

SQL Server detected 8 sockets with 8 cores per socket and 8 logical processors per socket, 64 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

This explains the behavior which he is seeing but he also wanted to fix it. I went ahead and looked in the ERRORLOG’a again and found below on the top.

2015-05-13 11:00:54.72 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

This is an enterprise edition of SQL so I was expecting it to use all processors and full power of the machine. I searched licensing guide and found that from SQL Server 2012 onwards, there are two enterprise licenses – Core Based and SERVER/CAL Based. As per my research, CAL based license limits SQL Enterprise to use only 20 physical processors. If hyper-threading is enabled then it would be 40 logical processors.

We have identified that this behavior was correct as per license they have purchased. Since this was their most critical server, they have purchased core based license. They came back to me and asked – “How can we change the installed SQL Server to use Core Based license?”

Answer was simple – perform the edition upgrade using new media Here are the steps:

  • Launch the setup.exe from your setup media or network location.
  • Select Maintenance on the left pane of the setup dialog.
  • Select Edition Upgrade on the right pane.
  • After the standard pre-requisite check you will get a Product Key Page. You need to enter your new product key.

Once complete, the Errorlog showed below.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

If you are using SQL 2012 onwards and having big machine, I would like you to check the ERROLROG and check if you are running into same issue. Else you can also run


Edition 01 SQL SERVER   Using 20 Logical Processors Based on SQL Server Licensing

This is mostly observed when there is an upgrade from earlier version of SQL Server because you might not be aware of that. Check and comment if this blog has helped you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082

Kathi SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about easy and amazing functions of SQL Server. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

The first set of T-SQL window functions have been available for 10 years, but many SQL Server professionals are not quite sure what window functions are. I am frequently asked by people I meet at conferences and customer sites “What are window functions?” I always answer with another question, “Do you use ROW_NUMBER?” Almost every time, the answer is “Yes. I didn’t know that was a window function.”

T-SQL window functions are different than other T-SQL functions because they operate over a set, or window, of rows. Notice that I am not capitalizing “window.” They have nothing to do with the Windows operating system. They are part of the ANSI SQL 2003 standard.

T-SQL window functions give you the ability to perform a calculation over the rows that are the result of the FROM, WHERE, GROUP BY and HAVING clauses. You can do things like include a column from a different row, calculate running totals, and perform summary calculations without losing details or grouping.

My favorite T-SQL window function is called LAG. LAG, introduced with SQL Server 2012, lets you bring in a column from a previous row. This is useful for calculating year over year performance, for example.

Here is the syntax of the LAG function:

LAG(<expression>[,offset][,default]) OVER([PARTITION BY <expression>] ORDER BY <expression>)

You must provide the column name that you wish to bring forward, and you must provide an ORDER BY expression. The ORDER BY is used to determine just which row is the previous row. By default, the value returned is from the previous row. By specifying an offset, you can go back any number of rows. You can also specify a default value to replace any NULLs that are returned.

Run the following script to create and populate a table for testing LAG.

SalesAmt MONEY
CONSTRAINT PK_Sales PRIMARY KEY(SalesYear, SalesQtr));
INSERT INTO #Sales( SalesYear, SalesQtr, SalesAmt )
VALUES (2009,1,1400),(2009,2,2200),(2009,3,2800),(2009,4,1000),

The following query uses the LAG function with an offset value of four to return the sales from the same quarter of the previous year.

SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearQtrSales
FROM #Sales;

notes 82 1 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 1: The partial results of using LAG

To calculate year-over-year sales, you subtract the previous year’s sales from the current sales and divide by the previous sales. To make the calculation a bit simpler, I’ll add the query to a CTE and perform the calculation in the outer query.

WITH Sales AS (
SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearSales
FROM #Sales)
SELECT SalesYear, SalesQtr, SalesAmt, Sales.PrevYearSales,
FORMAT((SalesAmt - PrevYearSales)/PrevYearSales,'P') AS YOY
FROM Sales;

notes 82 2 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 2: Using LAG for year-over-year calculation

notes 82 3 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082The LAG function is really easy to use, and it performs great. If you would like to learn more about LAG and all of the other T-SQL window functions, be sure to check out my new book: Expert T-SQL Window Functions in SQL Server.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Using Power of Web to Query Databases / Sources With Skyvia

Web technologies are moving at a pace that we can hardly keep up in pace. More and more of investments are going on in the space of web that even database developers like me find it interesting. In the same context, recently I was exploring on Skyvia (SQL SERVER – Integrate Your Data with Skyvia – Cloud ETL Solution) from ETL to how one might query and was surprised to see how some of these can be made useful.

This blog stems from a simple idea that was in my mind for a long time and I was struggling for a suitable solution till I saw what was available here. Think that you are a DBA and are on a vacation to a far-away land. You didn’t take your laptop along because you wanted to really enjoy your vacation times. Everything looks normal till you get an SMS from your management that something is wrong on one of your servers and they need 10-15 mins of your time to quickly resolve. Now with no laptop and all the cool things that you would have normally installed on your machine, how can you help your management with a few query support? Is it even possible? Assume you get to an internet kiosk and you want to do some amount of troubleshooting. How will you do? Well, there are a number of solutions.

One of the many available including the use of Skyvia to do query on your servers once you have done the simple step setup. In this example, I have used an SQL Server 2014 version available on a Cloud provider so that we can see what is going wrong.

Let us start by connecting to Skyvia and setting up our connections.

 Using Power of Web to Query Databases / Sources With Skyvia

In my example, I am going to use the “SQL Server” source and will give all the credentials. Once done and tested, we must see this in our connections tab.

 Using Power of Web to Query Databases / Sources With Skyvia

Once the connection setting seem to be in place, we are all set to run our queries on the backend. We can then head to the Query tab as shown below:

 Using Power of Web to Query Databases / Sources With Skyvia

At this stage, I just linked the Query window to my connection that we created in our previous step. This started to list our all the tables that were available on my server. This was really a cool moment because I was able to work on my database in less than 5 mins via the web.

 Using Power of Web to Query Databases / Sources With Skyvia

Obviously based on the permissions that I used to connect, the tables and objects are displayed. If there are 100’s of tables to work, then we can surely use the search to find the objects.

The next stage or table is our awesome query window. It is here that we can type in any SQL-92 query that needs to be fired against our database. Since I was wanting to know what was happening in my SQL Server system and what operators were functioning of few long running query, I used a DMV from SQL Server 2014 to check if it works. For my surprise, I was not disappointed.

 Using Power of Web to Query Databases / Sources With Skyvia

This is the query result output based on the query that I was running in the backend.

node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS FLOAT)/SUM(estimate_row_count)
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

To know more about sys.dm_exec_query_profiles you can read from MSDN. I also talk about this DMV in my course at Pluralsight – SQL Server 2014 Administration New Features. The course is exhaustive in understanding some of these new capabilities of SQL 2014.

Coming back to our query, based on the output I was able to find out what was the query that was running behind the scene. As you can see this is a Dummy query without any filters or restriction on the columns selected. Hence we are seeing a number of Clustered Index Scan in our query output via the web.

SELECT      *
FROM DimProduct
INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN FactInternetSales
ON DimProduct.ProductKey = FactInternetSales.ProductKey

Once the query was identified, you can start looking at what is firing up the query and take corrective actions. Any normal SQL syntax can be fired like this via the web. It is simple and quite powerful to say the least.

The results from Skyvia is powerful that you can also use them to export to CSV format for later offline consumption. As part of simple testing, I also fired few DDL statements and was surprised to have almost everything I do via SQL Server Management Script window here via the web, anytime and anywhere I like.

And another thing I’d like to mention is that Skyvia Query is not just for SQL Server and other databases. Skyvia Query supports SQL for cloud CRMs, such as Salesforce, Dynamics CRM, etc, and allows using SELECT, INSERT, UPDATE and  DELETE statements against them. This enables admins, knowing SQL, work with cloud data in the same way as with SQL Server data.

Currently Skyvia is free for use. You can find more information on how to use their online SQL editor on the following page: Online SQL Query Editor for Cloud and Relational Data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120

# do a check to ensure that collection duration is greater than
# or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"

# loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -ComputerName $ServerName -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},

    Get-Counter -ComputerName $ServerName -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds

# Group by the drive and Calculate the average for each drive we have
# round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {
            [int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}
        } |
    Sort-Object InstanceName

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

PowerShell DiskLatency 01 SQL SERVER   Identify Disk Latency on SQL Server Box Using PowerShell

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns

Sometimes the best way to learn is by taking some of the capabilities to the extreme. This blog post is one of those explorations which I would like to share. A very long time ago I wrote a blog on this feature which was introduced in SQL Server 2008 called “Sparse Columns”. You can read below blogs to read about the fundamentals of SPARSE columns:

One of the thing which people have misunderstood is the number of sparse column we can have with a table. As per documentation we can go beyond 1024 column limit by using sparse columns. One of my blog reader has done some test and said that the statement is not true. Here is the script which we was using:

@SN = 1
WHILE (@SN <= 1030)
SET @Query = 'ALTER TABLE SomeTable ADD ' + @CLN + ' nvarchar(100) SPARSE'
SET @SN = @SN + 1
EXEC (@query)

Above script is having a logic where it would create a table called SomeTable as below


And then it would keep adding columns using dynamic SQL


It fails with below error

Msg 1702, Level 16, State 2, Line 1
CREATE TABLE failed because column ‘C1024′ in table ‘SomeTable’ exceeds the maximum of 1024 columns.

As per https://msdn.microsoft.com/en-us/library/ms143432.aspx (Maximum Capacity Specifications for SQL Server) we should not have received the error because our table is a wide table.

Columns per non-wide table 1024
Columns per wide table 30,000

Here is the answer from Qingsong Yao (Microsoft)

In order to create more than 1024 columns, you have to have a columnset column defined in the table. Once the table has columnset defined, the select * will hide all sparse columns, and only see the column set. Meanwhile, people can still select individual sparse columns in their query.

So, the test done by my blog reader was having little problem. He forgot to define columnset column. Here is the sample of columnset with sparse column.


Let me give you a quick example.

USE tempdb
CREATE TABLE MySparseTable (
,Height DECIMAL(10, 2) Sparse
,Width DECIMAL(10, 2) Sparse
,Length DECIMAL(10, 2) Sparse
,Color VARCHAR(10) Sparse
,Volume DECIMAL(10, 2) Sparse
INSERT INTO MySparseTable (i,Height,Width)
VALUES (1,11.9,12.0);
INSERT INTO MySparseTable (i,Height,Length)
VALUES (2,2.8,9.3);
INSERT INTO MySparseTable (i,Color)
VALUES (3,'Red')
FROM MySparseTable
FROM [MySparseTable]
/* Clean up time
drop table MySparseTable

Here is the output.

Sparse 01 SQL SERVER   CREATE TABLE Failed Because Column C1024 in Table myTable Exceeds the Maximum of 1024 Columns

Have you ever use this feature in your environments? What has been your experience in using and what scenarios have you been using these capabilities? Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

Question: What is the difference between Index Seek and Index Scan?

Answer: Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan  in the execution plan.

Here are few other related articles on this subject which you may find useful:

Reference: Pinal Dave (http://blog.sqlauthority.com)