Archive

Posts Tagged ‘object_id’

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

19.10.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:DatabaseDataTestDB.mdf' ),

FILEGROUP [SecondaryFG]

(     NAME = N'TestDB_2',

      FILENAME = N'C:DatabaseDataTestDB_2.ndf' )

LOG ON

(     NAME = N'TestDB_log',

      FILENAME = N'C:DatabaseLogTestDB_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

Using Catalog Views – sys.views

15.06.2011 No comments

sys.views returns a row for each view in the database, sys.views only returns rows for which the user owns or has been granted permission for. Below are some of the columns returned by sys.views.

1. Name – Give name of the view,

2. Schema_id – ID of schema to which the view belongs,

3. Type – 'V' for View,

4. Type_Desc – Textual description of the type,

5. Create_Date – Creation date/time of the view,

6. Modify_Date – Last modification date/time for the view,

7. With_Check_Option – Specifies whether the VIEW is created by specifying WITH CHECK OPTION.

For example,

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

 

USE AdventureWorks2008R2

 

SELECT      Name, Schema_ID, Type_Desc,

            Create_Date, Modify_Date, With_Check_Option

FROM        sys.views

Partial Result Set:

Name                       Schema_ID     Type_Desc     Create_Date                 Modify_Date                 With_Check_Option

————————-  ———–   ———–   ————————-  ————————-      ————————

vAdditionalContactInfo     6             VIEW          2010-12-04 12:29:05.110    2010-12-04 12:29:05.110       0

vEmployee                  5             VIEW          2010-12-04 12:29:05.127    2010-12-04 12:29:05.127       0

vEmployeeDepartment        5             VIEW          2010-12-04 12:29:05.130    2010-12-04 12:29:05.130       0

vIndividualCustomer        9             VIEW          2010-12-04 12:29:05.133    2010-12-04 12:29:05.133       0

vPersonDemographics        9             VIEW          2010-12-04 12:29:05.150    2010-12-04 12:29:05.150       0

vJobCandidate              5             VIEW          2010-12-04 12:29:05.170    2010-12-04 12:29:05.170       0

This view does not return the VIEW definition. To retrieve the VIEW definition, you can user OBJECT_DEFINITION function. This function takes one argument – Object ID, which can be retrieved from sys.views or by using OBJECT_ID function:

SELECT      OBJECT_DEFINITION(OBJECT_ID(N'HumanResources.vEmployee'))

            AS 'Object Definition'

Result Set:

Object Definition

—————————————————-

CREATE VIEW [HumanResources].[vEmployee]

AS

SELECT

e.[BusinessEntityID]

,p.[Title]

,p.[FirstName]

,p.[MiddleName]

,p.[LastName]

,p.[Suffix]

,e.[JobTitle] 

,pp.[PhoneNumber]

,pnt.[Name] AS [PhoneNumberType]

,ea.[EmailAddress]

,p.[EmailPromo

 

(1 row(s) affected)

Hope This Helps!

Vishal

Using Catalog Views – sys.foreign_keys

14.06.2011 1 comment

sys.foreign_keys contains a list of all FOREIGN KEYS in the database. It contains one row per FOREIGN KEY. Below is a list of some columns returned by this view:

1. Name – FOREIGN KEY name,

2. Schema_ID – Schema ID of the parent object,

3. Parent_Object_ID – ID of the object to which the FK is linked.

4. Type – 'F' = FOREIGN KEY,

5. Create Date – Date constraint was created,

6. Modify_Date – Last Modified date for constraint,

7. Referenced_Object_ID – well, referenced object’s ID,

8. Delete_Referential_Action – ON DELETE action, this contains one of the following: 0 = No action, 1 = Cascade, 2 = Set null, 3 = Set default,

9. Update_Referential_Action – ON DELETE action, this contains one of the following: 0 = No action, 1 = Cascade, 2 = Set null, 3 = Set default,

10. Delete/Update_Referential_Action_Desc – Textual description of the referential action

 

sys.foreign_keys can be used as below:

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

 

USE AdventureWorks2008R2

 

SELECT      Name, Create_Date, Modify_Date,

            OBJECT_NAME(referenced_object_id) AS 'referenced object',

            delete_referential_action_desc,

            update_referential_action_desc

FROM        sys.foreign_keys

WHERE       parent_object_id = OBJECT_ID(N'HumanResources.Employee')

Result Set:

image

Hope This Helps!

Vishal