SQL Tutor

SQL Tutor

In this SQL Tutor Session I will explain by practically as following topics see below:

Ø  Filtered index

Ø  Like operator in  SQL Server

Ø  Except command & Intersect Command in SQL Server 2005

Ø  Status of SQL Server Services in SQL 2012

Ø  Map Login with users

Filtered index

Filtered index

Filtered index is a feature added in SQL Server 2008 and serves a great purpose for some of the requirements
As a general we all know that index improves search performance if INDEX is created properly
But some cases where we font requires data from entire column but only some filtered values
Even for this we have to create index for data entire column.
Whenever we query this column it has to read index for all values  column.



But if we want only certain value from column based on some logic then is there any other option available?
In this case it would be better if we create index only filtered value of column so it will put less overhead on the
server in terms of maintenance as well as server will have to check less amount of data while reading from index

Lets create first normal index than we will create filtered index and check this.

We want to check for orderqty > 10 for all out requirements.

CREATE INDEX idx_SalesOrderDetail_OrderQty

    ON Sales.SalesOrderDetail(OrderQty)

CREATE INDEX idx_SalesOrderDetail_OrderQty_Filtered

    ON Sales.SalesOrderDetail(OrderQty)

WHERE OrderQty > 10

Lets see space used by this 2 indexes

We can see that space used by filtered index much lesser compare to non filtered index

EXEC Sp_msindexspace 'Sales.SalesOrderDetail'

 

Ok , how to find index is filtered ?

SELECT *

FROM   sys.indexes

WHERE  object_id = Object_id('Sales.SalesOrderDetail')

       AND has_filter = 1

For unique index




One more use of filtered index is for creating unique index by filtering not unique values

Let see an example

CREATE TABLE test

    (

         id    INT IDENTITY(1, 1),

         data  VARCHAR(100),

         value INT

    )

INSERT INTO test

VALUES      ('a',

             1),

            (NULL,

             1),

            (NULL,

             2),

            ('b',

             2),

            ('c',

             3)

SELECT *

FROM   test 

Now I want to create unique index on data column but it has multiple null values  

It will fail 

CREATE UNIQUE INDEX idx_test_data_nonfiltered

    ON test(data)

 

We will use filtered index here to filter null values

CREATE UNIQUE INDEX idx_test_data_filtered

    ON test(data) 

where data is not null

Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.


CREATE TABLE user_id_list (

user_id VARCHAR(256))


/*If you are using SQL Server 2008*/

INSERT INTO user_id_list

VALUES ('amish'),

('amish123'),

('amish.shah'),

('123456'),

('123amish')


/*If you are using SQL Server 2005*/

INSERT INTO user_id_list

SELECT 'amish'

UNION ALL

SELECT 'amish123'

UNION ALL

SELECT 'amish.shah'

UNION ALL

SELECT '123456'

UNION ALL

SELECT '123amish'


-- To get ID which has only alphabet in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z]%'


-- To get ID which has only alphabet and '.' in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,.]%'


-- To get ID which has only alphabet and numbers in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,0-9]%'

Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this


CREATE TABLE #temp (

id INT,

data VARCHAR(100),

code INT)


INSERT INTO #temp

VALUES (1,'a',1)


INSERT INTO #temp

VALUES (1,'b',2)


INSERT INTO #temp

VALUES (2,'a',1)


INSERT INTO #temp

VALUES (2,'b',2)


CREATE TABLE #temp1 (

id INT,

data VARCHAR(100),

code INT)


INSERT INTO #temp1

VALUES (1,'a',1)


INSERT INTO #temp1

VALUES (1,'b',2)


INSERT INTO #temp1

VALUES (3,'a',1)


INSERT INTO #temp1

VALUES (3,'b',2)


Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns



SELECT *

FROM #temp t

WHERE NOT EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

EXCEPT

SELECT *

FROM #temp1

INTERSECT COMMAND in SQL (As Same as above EXCEPT Command)

Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns



SELECT *

FROM #temp t

WHERE EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

INTERSECT

SELECT *

FROM #temp1

Easy way to get status of SQL Server Services in SQL 2012

SQL 2012 has some new DMVs.
One of them is sys.dm_server_services
It gives us details about the SQL Server services (SQL Serve,SQL Server Agent, Full Text)

SELECT *

FROM   sys.dm_server_services

 

Map Login with users

When we restore backups to new server then users in the database are not mapped with their login in the new server
Its because sid is not matching.In this case we will are used to with sp_change_users_login
But this procedure will be deprectated in future version . So instead of this we should use alter user


alter user username
with
= new username



So for mapping a user test with testlogi  we will use


ALTER USER test WITH login = testlogin