Index Suggestion by Query Execution Plan – SQL Server 2008

I hope many of us we have start to use SQL Server 2oo8. SQL Server 2oo8 comes with many silent features. We all are familiar of execution plan of a query. We are getting visual display of query execution and cost of each action. Its very useful to understand how query will be executed.

Now let us see how its more improved in SQL Server 2oo8

In SQL Server 2oo8 it also suggest missing index and shows how much it will benefit after creating index.

Lets look at Example


CREATE TABLE testtable (

id INT,

id1 INT,

data VARCHAR(100))

DECLARE @id INT

SET @id = 1

WHILE @id <= 10000

BEGIN

INSERT INTO testtable

VALUES (@id,@id * 2,'data' + Convert(VARCHAR(10),@id))

SET @id = @id + 1

END

SELECT data,

Count(* )

FROM testtable

WHERE id IN (10,34,2452,234,

234,2344,2345,653,

345)

AND data LIKE '%4%'

GROUP BY data


Now press CTRL + L

Here it shows suggested index in green and also shows how much it will benefit us after creating query.Now press left click in menu there is option to create script for index.

After clicking on it, it will show us script of suggested index

How to get tablesize- SQL Server

Many times we need to get Size of tables and rows for each table for maintenance purpose.
Here I have given a script which will give
size information for each table using sp_spaceused procedure.


CREATE TABLE #tablesize_temp (

name VARCHAR(512),

ROWS INT,

reserved VARCHAR(51),

data VARCHAR(51),

index_size VARCHAR(51),

unused VARCHAR(51))

CREATE TABLE #tablesize (

name VARCHAR(512),

ROWS INT,

reserved BIGINT,

data BIGINT,

index_size BIGINT,

unused BIGINT)

INSERT INTO #tablesize_temp

EXEC Sp_msforeachtable

'exec sp_spaceused ''?'''

INSERT INTO #tablesize

SELECT name,

ROWS,

Replace(reserved,'kb',''),

Replace(data,'kb',''),

Replace(index_size,'kb',''),

Replace(unused,'kb','')

FROM #tablesize_temp

SELECT *

FROM #tablesize

ORDER BY ROWS DESC


Merge 2- SQL Server 2008

I have given basic details about merge command in my first article

Merge SQL Server 2008

Now we will go some more advance in it. As we know we can do all update /insert / delete operation on a single table using merge command in a single query. Now using output we can store all this details. Also SQL Server 2008 supports new command $action which will give us information about operation done on the table.
Here we will store inserted / updated / deleted data in a separate table using output clause. Also we will store action related to that data.

DECLARE @t1 TABLE(

id INT PRIMARY KEY,

code VARCHAR(10)

)

DECLARE @t2 TABLE(

id INT PRIMARY KEY,

code VARCHAR(10)

)

DECLARE @t1changed TABLE(

ACTION VARCHAR(51),

deleted_id INT,

deleted_code VARCHAR(10),

inserted_id INT,

inserted_code VARCHAR(10)

)

INSERT INTO @t1

VALUES (1,'a1'),

(2,'a2'),

(3,'a3'),

(4,'a4'),

(5,'a5'),

(6,'a6')

INSERT INTO @t2

VALUES (1,'a1'),

(3,'a3'),

(5,'b5'),

(7,'b7')

INSERT INTO @t1changed

(ACTION,

deleted_id,

deleted_code,

inserted_id,

inserted_code)

SELECT ACTION,

deleted_id,

deleted_code,

inserted_id,

inserted_name

FROM

(

MERGE @t1 as t1

USING @t2 as t2

ON t1.id = t2.id

WHEN MATCHED AND t1.code<> t2.code THEN

UPDATE SET t1.code = t2.code

WHEN NOT MATCHED BY TARGET THEN

INSERT VALUES(t2.id, t2.code)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, deleted.id , deleted.code,inserted.id,inserted.code

)AS T(action, deleted_id, deleted_code,inserted_id,inserted_name)

SELECT * FROM @t1changed


Merge

This is new command in SQL Server 2008. It was not availabled initially in SQL Server 2008 but later on introduce in SQL Server 2008. It provides support for update and insert and delete on target table in same query and its a cool benefit for progammers.

CREATE TABLE test (
id INT,
code VARCHAR(100))

INSERT INTO test
VALUES (1,’a1′)
INSERT INTO test
VALUES (2,’a2′)

SELECT *
FROM test

CREATE TABLE newtest (
id INT,
code VARCHAR(100))

INSERT INTO newtest
VALUES (1,’b1′)
INSERT INTO newtest
VALUES (3,’a3′)

Now we want to insert data from newtest to test table.
When id is matching we will update data
When id is not matching we will insert that data

Using Merge statement

SELECT *
FROM test

SELECT *
FROM newtest

MERGE test t
USING newtest n
ON t.id = n.id
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, code)
VALUES (n.id, n.code)
WHEN MATCHED THEN
UPDATE SET
id = n.id,
code = n.code;

Rules for WHEN MATCHED

• One statement must be update and second must be delete.

• If first one will fail only then second will execute

• Both will be processed in order.

Rules for WHEN NOT MATCHED BY TARGET

• TARGET is default option
• We can specify WHEN NOT MATCHED ALSO.
• It says that there are some rows in Source table which are not in Target table
• This statement can be only used for INSERT.
• This statement can be used only once

Rules for WHEN NOT MATCHED BY SOURCE

• It says there are rows in Target which are not in Source table.
• We can specify it for two times one for UPDATE and one for DELETE.
• If first will fail only then second will execute.

How to get last sales date/sales amount for each product -Analysis Service

How to get last date/last sales amount for each product when product was sold.

We are looking at a sample which will show last sales date and slaes amount for each product. Similarly we can get first date when product was sold using this function. We can use LastNonEmpty function but its not available except developers edition.

We will use GENERATE function which will run for each product and will get last date when product was sold for each product.

We will use AdventureWorks Sample cube for this query.

select [Measures].[Internet Sales Amount] on 0,
generate
([Product].[Product Categories].[Product],
tail(nonempty({[Product].[Product Categories].currentmember*[Date].[Calendar].[Date].members},[Measures].[Internet Sales Amount]),1) )on 1
from
[Adventure Works]
;

To get first date when product was sold

select [Measures].[Internet Sales Amount] on 0,
generate
([Product].[Product Categories].[Product],
head(nonempty({[Product].[Product Categories].currentmember*[Date].[Calendar].[Date].members},[Measures].[Internet Sales Amount]),1) )on 1
from
[Adventure Works]
;