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 (Index Scan) in the execution plan.
Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.
What is the Maximum Size per Database for SQL Server Express?
Maximum database size of 10 GB per database in SQL Server 2014, SQL Server 2012, and 2008 R2 Express (4 GB for SQL Server 2008 Express and earlier; compared to 2 GB in the former MSDE).
What is Aggregate Functions?
Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY for filtering query using aggregate values.
The following functions are aggregate functions.
AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read more here )
What is Use of @@ SPID in SQL Server?
A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed. (Read more here)
What is the Difference between Index Seek vs. Index Scan?
An index scan means that SQL Server reads all the rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all the rows of the index are examined instead of the table directly. This is sometimes compared to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.
An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows. (Read more here)
What is the Maximum Size per Database for SQL Server Express?
SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if the database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.
How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon every time as it is difficult to reach a conclusion when there are many columns and many rows.
It is easy to measure how much data is retrieved from server to client side. The SQL Server Management Studio has feature that can measure client statistics. (Read more here)
What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here)
How to Create Primary Key with Specific Name while Creating a Table?
CREATE TABLE [dbo].[TestTable]([ID] [int] IDENTITY(1,1)NOTNULL,[FirstName] [varchar](100)NULL,CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED([ID] ASC))GO
What is T-SQL Script to Take Database Offline – Take Database Online?
-- Take the Database OfflineALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATEGO-- Take the Database OnlineALTER DATABASE [myDB] SET ONLINE
GO
How to Enable/Disable Indexes?
--Disable IndexALTER INDEX [IndexName] ON TableName DISABLE
GO--Enable IndexALTER INDEX [IndexName] ON TableName REBUILD
GO
Can we Insert Data if Clustered Index is Disabled?
No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).
(Read more here)
How to Recompile Stored Procedure at Run Time?
We can Recompile Stored Procedure in two ways.
Option 1:
CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)WITH RECOMPILEAS
SELECT*FROM dbo.tblPersonWHERE Age <= @MinAge AND Age >= @MaxAgeGO
Option 2:
EXEC dbo.PersonAge65, 70 WITHRECOMPILE
We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.
This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. (Read more here)
Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
There is no performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table).(Read more here)
What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
INSERT TOP (N) INTO Table is faster than Using Top with INSERT but when we use INSERT TOP (N) INTO Table, the ORDER BY clause is totally ignored. (Read more here)
Does the Order of Columns in UPDATE statements Matter?
No, the order of columns in UPDATE statement does not matter for results updated.
Both the below options produce the same results.
Option 1:
UPDATE TableNameSET Col1 ='Value', Col2 ='Value2'
Option 2:
UPDATE TableNameSET Col2 ='Value2', Col1 ='Value'
How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
Client Statistics is very important. Many a time, people relate queries execution plan with query cost. This is not a good comparison. Both are different parameters, and they are not always related. It is possible that the query cost of any statement is less, but the amount of the data returned is considerably large, which is causing any query to run slow. How do we know if any query is retrieving a large amount data or very little data? In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon all the time as it is difficult to reach a conclusion when there are many columns and many rows.
To measure how much data is retrieved from server to client side is very simple. SQL Server Management Studio has feature that can measure client statistics. There are three different ways to enable client statistics.
Method 1
Press SHIFT+ALT+S together.
Method 2
Go to Menu >> Query >> Include Client Statistics
Method 3
Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:
Method 1:
INSERT INTO TABLE …
SELECT TOP (N) Cols…
FROM Table1
Method 2:
INSERT TOP(N) INTO TABLE …
SELECT Cols…
FROM Table1
Today we will go over the second method which in fact is the enhancement in TOP along with INSERT. It is very interesting to also observe difference between both the methods. Let us play with one real example and we understand what exactly is happening in either of the case.
USE tempdb
GO-- Create TableIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TestValue') AND type IN (N'U'))DROP TABLE TestValue
GOCREATE TABLE TestValue(ID INT)INSERT INTO TestValue (ID)SELECT 1UNION ALLSELECT 2UNION ALLSELECT 3UNION ALLSELECT 4UNION ALLSELECT 5
GO-- Select Data from TableSELECT *FROM TestValue
GO-- Create Two Table where Data will be InsertedIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue') AND type IN (N'U'))DROP TABLE InsertTestValue
GOCREATE TABLE InsertTestValue (ID INT)IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue1') AND type IN (N'U'))DROP TABLE InsertTestValue1
GOCREATE TABLE InsertTestValue1 (ID INT)GO-- Option 1: Top with SelectINSERT INTO InsertTestValue (ID)SELECT TOP (2) IDFROM TestValueORDER BY ID DESCGO-- Option 2: Top with InsertINSERT TOP (2) INTO InsertTestValue1 (ID)SELECT IDFROM TestValueORDER BY ID DESCGO-- Check the DataSELECT *FROM InsertTestValue
GOSELECT *FROM InsertTestValue1
GO-- Clean upDROP TABLE InsertTestValueDROP TABLE InsertTestValue1DROP TABLE TestValue
GO
Now let us check the result of above SELECT statements.
It is very interesting to see when Option 2 is used, ORDER BY is absolutely ignored and data is inserted in any order.
In future articles, we will talk about performance for these queries. What are your thoughts on this feature? Have you used INSERT TOP(N) in your application?
SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation
Many times I have seen the issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for the existence of rows in the table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have a different execution plan when used to find the existence of rows.
Let us see 4 tests I ran and note their result is same and their Execution Plan take the same amount of resources (50% and 50%)
You can click on the images to see larger images.
Test 1: Whole Table SELECT
1
2
3
4
5
6
7
8
9
10
11
12
|
USE AdventureWorks
GO
IF EXISTS(
SELECT 1
FROM Production.Product)
SELECT 'SELECT 1'
GO
IF EXISTS(
SELECT *
FROM Production.Product)
SELECT 'SELECT *'
GO
|
Test 2: Condition WHERE on Indexed Column
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
USE AdventureWorks
GO
IF EXISTS(
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT 1'
GO
IF EXISTS(
SELECT *
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT *'
GO
|
Test 3: Using Aggregate function COUNT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
USE AdventureWorks
GO
IF (
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT *'
GO
|
Test 4: Using COUNT with search on non-indexed Column
|
USE AdventureWorks
GO
IF (
SELECT COUNT(1)
FROM Production.Product
WHERE SafetyStockLevel = '800') &gt; 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE SafetyStockLevel = '800') &gt; 1
SELECT 'SELECT *'
GO
|
Note: I have used single quotes around 800, which is numeric and I am aware of that fact. Above queries are for testing purpose only.
I have been using SELECT 1 instead of SELECT * when checking existence of rows. I would like to see what my readers have an opinion about this. Please have your opinion and make your comment here.
