jump to navigation

Tables with no clustered indexes or no indexes at all in SQL Server databases November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “If there is a way to find using T-SQL tables in a database that have no clustered indexes or no indexes at all”

I have answered that this is possible and I provided with some scripts.

There a lot of tasks that we cannot perform through the GUI of SSMS.So we have to search through the object metadata to get our answers.We can query the catalog views and get all the information we want

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' +t.NAME AS
[tables without clustered index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables without clustered index]

If you execute this bit of code , you will see all the tables in the AdventureWorksLT2012 database that have not go a clustered index.

As you can see I query the sys.tables view.You can use this script to query your own database for tables without a clustered index.

If I want to find all the tables in the AdventureWorksLT2012 database or any other database for tables that have not indexes you can execute the following script.


SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' + t.NAME AS
[tables_without_an_index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables_without_an_index]

Hope it helps!!!

Looking into SQL Server page types November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist.

I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes.

In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes.
If you make the calculations,16 extents make up 1 mbyte.

Extents are divided into 2 categories,

  • Uniform – 8 pages belong to the same object e.g table
  • Mixed  – 8 pages do not belong to the same object

The first 96 bytes of the page are also reserved for the page header.
Page header includes information like

  • Page number
  • Page type
  • Free space per page

The greatest size in bytes I can have in a page is 8060 bytes
So the maximum record length I can have in a table is 8060 bytes

If I have a table that its record length is 100 bytes, that means I can have 80 records per page.
If I have 200 records I need 3 pages. Each record belongs to only one page. You cannot  have half record in one page and another half in another.

Ι was asked to provide an example where we will see the various SQL Server page types and what happens when the row size is larger than 8060 bytes.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

CREATE TABLE largetable
( col1 VARCHAR(2000),
 col2 VARCHAR(8000),
 col3 VARCHAR(max)

 );

INSERT INTO largetable VALUES
(
REPLICATE('SQL Server 2012 will provide Mission Critical
Confidence with greater uptime blazing-fast performance and enhanced
security features for mission critical workloads',12), REPLICATE('SQL Server 2012', 533),
REPLICATE('SQL Server indexes', 1000))



SELECT * FROM largetable
-- Shows types of pages available inside SQL Server

SELECT OBJECT_NAME (object_id) AS tablename, rows,
 type_desc AS page_types_SQL
FROM sys.partitions part
INNER JOIN sys.allocation_units alloc
ON part.partition_id = alloc.container_id
WHERE object_id = object_id('largetable');

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called largetable in the tempdb database. As you can see the record length is much larger than 8060 bytes.
  • Insert some values into the table. I insert thousands of rows using the REPLICATE function.
  • Finally I execute a SELECT JOIN statement from the sys.partitions and sys.allocation_units table.

The results are:

tablename    rows               page_types_SQL
largetable       1                            IN_ROW_DATA
largetable       1                            LOB_DATA
largetable       1                            ROW_OVERFLOW_DATA

As you can see I have one row of data.Each of these types of pages is called an Allocation Unit.

IN_ROW_DATA refers to the situation where we have the whole row size staying within the 8,060-byte limit.

LOB_DATA refers to LOB data type columns, in this case  the NVARCHAR(MAX) column and its values.

ROW_OVERFLOW_DATA refers to the data that could not be stored to single page for a single row and that is why it is using an overflow page.

Hope it helps!!!

Significance of order of columns in an index November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “Does the order of the column in an index matter?”

Let’s give some basic definitions first and make some analogies.

A single column index is straightforward. Think of it as an index in the back of the book.
Let’s say you want to learn about “DBCC FREEPROCCACHE” . You look for that command in the index of the book. The index does not have the information. It has a pointer to the page where the command is described.You turn to that page and read about it. This is a good analogy for a single column,non-clustered index

In SQL Server you can also create an index that contains more than one column.This is known as the composite index. A good analogy for a composite index is the telephone book.
A telephone book lists everyone who has publicly available a telephone number in an area.It is organised not by one column but by two:last name and first name.To look up someone in the telephone book , we first navigate to the last name and then the first name. To find John Smith you first locate Smiths and then John. Composite indexes contain more than 1 column and can reference up to 16 columns from a single table or view.

Back to our question.I have answered this question with the demo below.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL

)
--create a clustered index on Customer_ID and
--a non-clustered composite index on the Last_Name and First_Name

CREATE CLUSTERED INDEX ix_Customer_ID ON Customers(Customer_ID)

CREATE NONCLUSTERED INDEX ix_Customer_Name ON Customers(Last_Name,First_Name)
--when we issue a query to SQL Server that retrieves data from the Customers table, the
--SQL Server query optimiser will consider the various retrieval methods at its disposal
--and select one it deems most appropriate

-- insert test row

INSERT INTO customerS VALUES('Smith','John','js@smith.com')

--use SQL Data Generator to create sample data 10000 rows

SELECT * FROM customers

-- DBCC DROPCLEANBUFFERS removes all buffers from the buffer pool
-- DBCC freeproccache removes all entries from the procedure cache

DBCC dropcleanbuffers
DBCC freeproccache

-- when we run this query and see the execution plan
-- we have an index seek on the nonclustered index
-- to locate the rows selected by the query
-- we also have a key lookup to find the values for the email
-- to retrieve the non-indexed columns
SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
 AND First_Name = 'John'

-- what happens in this case?
--where we have the WHERE statement in different order than the index order?
-- it will use the same execution plan

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'John'
 AND Last_Name = 'Smith'

-- and what happens in this case?
--where we use only last name?
--the same plan is used

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
-- what happens in this case?
-- when we use only first_name
--the index cannot be used
-- an index cannot be used to seek rows of data when the first column
-- of the index is not specified in the WHERE clause

INSERT INTO customers VALUES ('kantzelis','nikos','nikolaosk@hotmail.com')

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'nikos'

 SET STATISTICS IO on

 DBCC dropcleanbuffers
DBCC freeproccache

 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

-- now lets drop the index to see what happens

DROP INDEX ix_Customer_Name ON customers

--and rerun the query
 --we see a huge increase in logical reads without the index
 --we have in this case, in the absence of the index a clustered index scan
 --which means that each row of the table had to be read
 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I create a clustered index on the table ix_Customer_ID on Customer_ID
  • I insert a test row to the table
  • Then I use a third party generator tool to create 10.000 records
  • Then I just use a simple statement to make sure all the rows were inserted
  • Then I remove all buffers from the buffer pool and all entries from the procedure cache
  • Then (I activate the actual execution plan) and execute the Select  query in line 43.
  • We have an index seek on the non clustered index and a key lookup to get the values for the email column.
  • Then I execute the Select statement in lines 54. The order of the columns in the WHERE clause are different than the index order.
  • Still the same execution plan is used
  • Then I execute the Select statement in lines 65.  We have just the last_name column in the  WHERE clause.The same plan is used
  • Then in line 76 I insert a new value in the table
  • Then I execute another Select statement (lines 78) where just the first_name column is in the WHERE clause.In this case the index cannot be used. An index cannot be used to seek rows of data when the first column of the index is not specified in the WHERE clause.
  • Then in line 84 I set statistics IO on so i can investigate the logical reads when having or not the index.
  • In line 89 I run a Select query again and make a note of the logical reads , which is 4 in my case.
  • Then I drop the index and rerun the query (line 103). This time the logical reads are 77. So you can see that the performance of the query without the index has been dramatically decreased.

Hope it helps!!!

SQL Server SET options and index creation November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation.

I will look into the SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and how their values can affect index creation.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL,
Fullname AS first_name + '' + last_name
)

SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS OFF;
--create a clustered index on Customer_ID and
--a non-clustered composite index on the fullname

--index failed
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

-successful index creation
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I have a computed column in Fullname in the table Customers
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to OFF
  • Then I create a non clustered index on the computed column. It failed with this error “CREATE INDEX failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to ON
  • Then I create a non clustered index on the computed column again. This time works just fine.

Hope it helps!!!

Looking into the MERGE T-SQL statement November 16, 2013

Posted by fofo in Sql Server, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked to provide a demo about the MERGE statement.

I will provide you with a demo in this post trying to explain more about the MERGE T-SQL statement and its use.

This statement was introduced back to SQL Server 2008.We can use a MERGE statement to modify data in a target table based on data in a source table.The statement joins the target to the source by using a column common to both tables, such as a primary key.

You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.

You need to have SELECT permissions on the source and INSERT,UPDATE,DELETE permissions on the target.

In this example I will create two tables, one source and one target table. I will use the WHEN MATCHED THEN clause to update rows in the target table that match rows in the source table.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE master
GO

CREATE DATABASE mergedata
GO

USE mergedata
GO

IF OBJECT_ID ('CarsStock', 'U') IS NOT NULL

DROP TABLE dbo.CarsStock;

 -- this is our target table

CREATE TABLE dbo.CarsStock

(

CarID INT NOT NULL PRIMARY KEY,

CarModel NVARCHAR(100) NOT NULL,

Quantity INT NOT NULL

CONSTRAINT Qt_Df_1 DEFAULT 0

);
IF OBJECT_ID ('CarsOrders', 'U') IS NOT NULL

DROP TABLE dbo.CarsOrders;

--this is the souce table.

CREATE TABLE dbo.CarsOrders

(

CarID INT NOT NULL PRIMARY KEY,

CarModel NVARCHAR(100) NOT NULL,

Quantity INT NOT NULL

CONSTRAINT Qt_Df_2 DEFAULT 0

);

INSERT CarsStock VALUES

(1, 'BMW Cabrio', 12),

(2, 'Ford Focus', 13),

(3, 'LexusLS460', 2),

(5, 'KIA Preggio', 1),

(6, 'Citroen Picasso', 1),

(8, 'Ford escape', 4);

INSERT CarsOrders VALUES

(1, 'BMW Cabrio', 4),

(3, 'LexusLS460', 1),

(4, 'Citroen Picasso', 4),

(5, 'KIA Preggio', 5),
 (7, 'KIA optima', 8);

 --Implementing the WHEN MATCHED Clause

 --The first MERGE clause we’ll look at is
 -- WHEN MATCHED. You should use this clause
 -- when you want to update or delete rows
 -- in the target table that match rows in the
 -- source table

 MERGE CarsStock cs

USING CarsOrders co

ON cs.CarID = co.CarID

WHEN MATCHED THEN

UPDATE

SET cs.Quantity = cs.Quantity + co.Quantity;

SELECT * FROM CarsStock;

Let  me explain what I am doing in this bit of code

  • I make the master database the current database
  • I create a new database mergedata
  • I create a table called CarsStock
  • I create a table called CarsOrders
  • I insert values in the CarsStock table – target table
  • I insert values in the CarsOrders table -source table
  • Then I use the WHEN MATCHED THEN clause with the MERGE statement because I want to update rows(quantity column) in the target table that match rows in the source table
  • Then I select the contents of the CarsStock table and see the values updated (Quantity column) where the values of the CarID is the same in the two tables- joined column values are the same.

I will let you investigate the

  • WHEN NOT MATCHED [BY TARGET] clause
  • WHEN NOT MATCHED BY SOURCE clause

Hope it helps!!!

Follow

Get every new post delivered to your Inbox.

Join 1,792 other followers