Archive

Posts Tagged ‘SELECT’

SQL Server – Moving table with LOB data to different file group

October 19, 2011 3 comments

Last week, i posted about moving tables to different file groups by recreating indexes. However, this method does not *work* when there is LOB data involved.

When we recreate indexes on a table containing LOB data, the LOB data pages are not moved to destination file group. This can be demonstrated with below example:

USE [master]

GO

 

/* Create a database with 2 file groups */

CREATE DATABASE [TestDB]

ON PRIMARY

(     NAME = N'TestDB',

      FILENAME = N'C:\Database\Data\TestDB.mdf' ),

FILEGROUP [SecondaryFG]

(     NAME = N'TestDB_2',

      FILENAME = N'C:\Database\Data\TestDB_2.ndf' )

LOG ON

(     NAME = N'TestDB_log',

      FILENAME = N'C:\Database\Log\TestDB_log.ldf' )

GO

 

/* Create a table on [PRIMARY] file group */

CREATE TABLE T2

(     [ID] INT IDENTITY(1,1),

      [Name] VARCHAR(MAX),

      [Status] SMALLINT

)

GO

 

/* store LOB data out of row */

EXEC sp_tableoption 'dbo.T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(     [ID] ASC

) GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(     [Status] ASC

) GO

 

INSERT INTO T2([Name], [Status])

VALUES ('LOB data', 1)

GO

 

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

DBCC IND('TestDB', 'T2', 1)

GO

 

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      PRIMARY

T2     U      PRIMARY

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …  iam_chain_type

1       158         NULL   NULL        2105058535  …  In-row data

1       157         1      158         2105058535  …  In-row data

1       156         NULL   NULL        2105058535  …  LOB data

1       155         1      156         2105058535  …  LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From the result set we can see that:

1. Both indexes are on [PRIMARY] file group.

2. All allocated pages are from file with file_id = 1

3. File with file_id = 1 is in [PRIMARY] file group.

Now let’s try to move the table to [SecondaryFG] by rebuilding indexes:

USE [TestDB]

GO

 

/* Re-create indexes on [SecondaryFG] */

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(

      [ID] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(

      [Status] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

/* Indexes have moved to [SecondaryFG] */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* But not LOB pages,

PageFID = FileID of file from which the page is allocated */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 1 is from [PRIMARY] file group

File ID 3 is from [SecondaryFG] file group */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    … iam_chain_type

3       9           NULL   NULL        2105058535  … In-row data

3       8           3      9           2105058535  … In-row data

1       156         NULL   NULL        2105058535  … LOB data

1       155         1      156         2105058535  … LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result set we can see that:

1. Rebuilding indexes have moved them to [SecondaryFG] file group.

2. In-row data pages are now allocated from File with file_id = 3

3. LOB data pages have not moved, and are still in file with file_id = 1

4. File with file_id = 1 is in [PRIMARY] file group, and File with file_id = 3 is in
[SecondaryFG] file group.

So how do we move the LOB data pages? You need to drop and recreate the table if you need to move LOB data. The general steps to follow are:

1. Create a new table with same structure on destination file group i.e. [SecondaryFG]

2. Import rows from old table

3. Delete old table

4. Rename new table

For example:

USE [TestDB]

GO

 

/* Create a new table on new file group */

CREATE TABLE Tmp_T2

(

      [ID]        INT    IDENTITY(1,1),

      [Name]      VARCHAR(MAX),

      [Status]    SMALLINT

) ON [SecondaryFG]

GO

 

EXEC sp_tableoption 'dbo.Tmp_T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_Tmp_T2_ID] ON [dbo].[Tmp_T2]

(

      [ID] ASC

) ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_Tmp_T2_Status] ON [dbo].[Tmp_T2]

(

      [Status] ASC

) ON [SecondaryFG]

GO

 

/* Import data from old table */

SET IDENTITY_INSERT Tmp_T2 ON

INSERT INTO Tmp_T2([ID], [Name], [Status])

SELECT * FROM T2

SET IDENTITY_INSERT Tmp_T2 OFF

GO

 

/* Delete old table */

DROP TABLE T2

 

/* Rename the temporary table */

EXEC sp_rename 'dbo.Tmp_T2', 'T2'

GO

 

/* Rename indexes, not necessary but useful when they have been used in hints. */

EXEC sp_rename    N'dbo.T2.IX_CL_Tmp_T2_ID',

                  N'IX_CL_T2_ID',

                  N'INDEX'

GO

 

EXEC sp_rename    N'dbo.T2.IX_NCL_Tmp_T2_Status',

                  N'IX_NCL_T2_Status',

                  N'INDEX'

GO

This script implements the steps described above:

1. It creates a new table/indexes dbo.Tmp_T2 on [SecondaryFG] with the same structure
as dbo.T2

2. Imports all rows from dbo.T2 into dbo.Tmp_T2

3. Deletes dbo.T2

4. Renames dbo.Tmp_T2 to dbo.T2. It also renames the indexes as required.

You can check the page allocations using the queries we used earlier:

USE [TestDB]

GO

 

/* Indexes are on [SecondaryFG] .. OK. */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* LOB Pages are on File ID = 3 */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 3 is in [SecondaryFG] .. OK. */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …    iam_chain_type

3       11          NULL   NULL        2121058592  …    In-row data

3       10          3      11          2121058592  …    In-row data

3       9           NULL   NULL        2121058592  …    LOB data

3       8           3      9           2121058592  …    LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result we can see that all indexes and LOB data pages have moved to file with file_id = 3, which is in [SecondaryFG].

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: SQLServer

Accessing Registry using XPs (contd..) – TSQL

May 10, 2011 17 comments

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues and xp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer]

this returns:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

 

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

 

C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL

 

If the key path cannot be translated to instance-specific path, it will read from the specified key path.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Inserting to a View – INSTEAD OF TRIGGER – SQL Server

May 2, 2011 15 comments

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.

However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.

You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

For example, let’s consider following view definition:
— © 2011 – Vishal (http://SqlAndMe.com)
CREATE TABLE [dbo].[Table1]
(      [ID] [INT] NULL,
       [Name] [VARCHAR](20) NULL
)

CREATE TABLE [dbo].[Table2]
(      [ID1] [INT] NULL,
       [Name1] [VARCHAR](20) NULL
)

CREATE View [dbo].[View1]
AS

SELECTTable1.ID, Table1.Name, Table2.Name1
FROM   Table1
INNER JOIN    Table2
              ON Table2.ID1 = Table1.ID

Now, if you try to insert to [View1], you will run into above error. To enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:

— © 2011 – Vishal (http://SqlAndMe.com)
CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Table1
SELECT I.ID, I.Name
FROM INSERTED I

INSERT INTO Table2
SELECT I.ID, I.Name1
FROM INSERTED I
END

That’s all folks. Now, you can execute INSERT statement against the view and it will INSERT the data to the base tables.

INSERT INTO View1 Values (1,'Gandalf','The Grey')

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Verify the base tables:
image

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

TSQL – Common Table Expressions (CTE) – Part 4 – Multiple References

April 19, 2011 Leave a comment

A CTE can be referenced multiple time in a single statement, this can be useful in a self-join scenario. age old employee manager example…. consider following data,

image

Now to list employees with their respective managers we need to do a self-join

SELECT      EMP1.EmpID, EMP1.EmpName, EMP1.ManagerID,
            EMP2.EmpName AS [ManagerName]
FROM        dbo.Employees EMP1
LEFT JOIN   dbo.Employees EMP2 ON EMP1.ManagerID = EMP2.EmpID

We can also achieve this by using a CTE,

;WITH EmpCTE AS
(
      SELECT      EmpID, EmpName, ManagerID
      FROM        dbo.Employees
)     SELECT      CTE1.EmpID, CTE1.EmpName, CTE1.ManagerID,
                  CTE2.EmpName AS [ManagerName]
      FROM        EmpCTE CTE1
      LEFT JOIN   EmpCTE CTE2 ON CTE1.ManagerID = CTE2.EmpID

Note that we are referencing the same CTE multiple times in the statement. and the output will be as follows:

image

Now, if you try to use a derived table in this scenario – well, you can’t do it…

SELECT      dTable.EmpID, dTable.EmpName, dTable.ManagerID,
            dTable1.EmpName AS [ManagerName]
FROM        (SELECT     EmpID, EmpName, ManagerID
            FROM  dbo.Employees)AS dTable
INNER JOIN  dTable dTable1 ON dTable.ManagerID = dTable.EmpID

if you try to execute above statement, you will get an error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dTable'.

You can download the scripts here.

Check out all five parts:

TSQL – Common Table Expressions (CTE) – Part 0 – Introduction
TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

TSQL – Common Table Expressions (CTE) – Part 2 – Simplifying a Query

TSQL – Common Table Expressions (CTE) – Part 3 – GROUP BY calculated column

TSQL – Common Table Expressions (CTE) – Part 4 – Multiple References

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

TSQL – Common Table Expressions (CTE) – Part 3 – GROUP BY calculated column

April 16, 2011 2 comments

There are other ways too which can be used to GROUP BY a calculated column such as using a derived table.

Consider, the below data which needs to be categorized based on the Cost Range.

 

USE AdventureWorks2008R2
SELECT StandardCost, ProductID
FROM Production.Product

 

image

 

The data can be categorized using a simple query, which divides the Standard Cost columns into required ranges:

SELECT CASE
WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
WHEN StandardCost >=      1500          THEN 'Very High'
END AS [Cost Range], COUNT(ProductID)
FROM Production.Product
GROUP BY 1

 

but, if you try to execute the above query you will receive an error:

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

What is this error? well, in simple terms we are trying to group by before we retrieve all data. How to avoid this error? There are two ways.

1. Use a CTE:

;WITH scTable([Cost Range], ProductID)
AS
(
    SELECT CASE
    WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
    WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
    WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
    WHEN StandardCost >=      1500          THEN 'Very High'
    END AS [Cost Range], ProductID
    FROM Production.Product
)

 

we are not using GROUP BY inside the CTE here, this will simply retrieve the data, and after that we can us use GROUP BY:

 

SELECT [Cost Range], COUNT(ProductID) AS ProductsInRange
FROM scTable
GROUP BY [Cost Range]

 

and, here’s the required output:

 

image

2. Using derived table: Same output can be achieved using a derived table:

SELECT dTable.[Cost Range], COUNT(dTable.[ProductID]) AS ProductsInRange
FROM
(
    SELECT CASE
    WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
    WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
    WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
    WHEN StandardCost >=      1500          THEN 'Very High'
    END AS [Cost Range], ProductID
    FROM Production.Product
) AS dTable
  GROUP BY dTable.[Cost Range]

 

image

 

And, the execution plans generated will also be identical. so either can be used in such a scenario. image

You can download the sample scripts used in this example here.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

TSQL – Common Table Expressions (CTE) – Part 2 – Simplifying a Query

April 15, 2011 Leave a comment

Another use of CTE can be to simply a query which joins a lot of tables. we can divide he number of joins between the CTE and the query. Hence, making it more readable and easier to understand.

For this example, I have create five different tables for Product Name, Category, Description, Model and Price. each table contains a primary key Product ID.

Sample data is used as below:

image image image image image

Now, to return all columns in a single Result set we can write a query:

SELECT      P.ProductID, P.ProductName, PC.Category,
            PD.Description, PM.Model, PP.Price
FROM        dbo.Product P
INNER JOIN  dbo.ProductCategory PC
            ON PC.ProductID = P.ProductID
INNER JOIN  dbo.ProductDescription PD
            ON PD.ProductID = P.ProductID
INNER JOIN  dbo.ProductModel PM
            ON PM.ProductID = P.ProductID
INNER JOIN  dbo.ProductPrice PP
            ON PP.ProductID = P.ProductID

This works perfectly, the only problem here is number of JOINS required. And as the number of tables increase it would get more messier!

A CTE can be put to use to steal some joins from the query. for our example, we can perform two JOINs inside a CTE, and two JOINs in the outer query. Thus, making it more readable.

It can be converted to:

;WITH ProductDetails(ProductID, Category, Description, Model)
AS
( 
      — JOINs used in CTE = 2
      SELECT PC.ProductID, PC.Category, PD.Description, PM.Model
      FROM dbo.ProductCategory PC
      INNER JOIN dbo.ProductDescription PD ON PD.ProductID = PC.ProductID
      INNER JOIN dbo.ProductModel PM ON PM.ProductID = PC.ProductID
)
— JOINS used in outer query = 2
      SELECT PD.ProductID, P.ProductName, PD.Category, PD.Description, 
      PD.Model, PP.Price
      FROM ProductDetails PD
      INNER JOIN dbo.ProductPrice PP ON PP.ProductID = PD.ProductID
      INNER JOIN dbo.Product P ON P.ProductID = PD.ProductID

looks good, doesn’t it?

You can download the scripts used in this example here.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

April 14, 2011 2 comments

Let’s take a look at how CTE can be used to create a recursive query:

In a typical employee – supervisor scenario, A supervisor is an employee and an employee can be a supervisor of another employee(s).

This is also known as a self-referencing. For example:

image

The above table contains a list of Employee IDs and Employee Names. It also contains a column named ManagerID for Employee’s Manager. The Manager here is also an employee.

To generate a hierarchical list of all employees:
1.
we first need to retrieve root employee, i.e. top level employee,
2.
after the root level is retrieved, we need to find all child nodes for that root level,
3.
perform step 2 until all levels are retrieved.

This can be achieved using a Recursive Common Table Expression, which consists of two parts:
1. Retrieve root level – this is known as Anchor in a Recursive CTE
2.
retrieve all children of root level – this is known as Recursive Part in a Recursive CTE.

These process is performed recursively until all level are retrieved.

The Anchor part will be written as follows: (Retrieving root level)

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
FROM dbo.Employees Emp
WHERE ManagerID IS NULL

And, the recursive part is written as: (Retrieving all children of root), “EmployeeHierarchy” will be the CTE itself, hence making it recursive:

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
FROM dbo.[Employees] Emp
INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID

After combining both parts the full query will be:

WITH EmployeeHierarchy(ManagerID, EmpID, EmpName, EmpLevel)
AS
( 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
      FROM dbo.Employees Emp
      WHERE ManagerID IS NULL 
 
      UNION ALL 
 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
      FROM dbo.Employees Emp
      INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID
)     SELECT ManagerID, EmpID, EmpName, EmpLevel
      FROM EmployeeHierarchy
      ORDER BY ManagerID

The output of above query:

image

Download the sample scripts used in this example here.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe