Archive for the ‘SQL Server’ Category
-
SQL Server 2008 new datetime types, functions and the conversions between them
27 Apr 2010Datetime Types in SQL Server 2005: Lets start with a quick look at the existing date time types in SQL…
-
SQL Server 2008 R2 Free eBook from Microsoft Press
22 Apr 2010Just came to know about this book. More details on the Microsoft Press Blog. You can download the ebook in…
-
How to get the File Group of a Table or an Index?
31 Jan 2010The file group information of both Index and a Table is stored in the sys.indexes table. You might wonder how the tables file group is stored in sys.indexes metadata table. It is because when ever a clustered index is created on a table in SQL Server the physical data pages of the table will be made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the clustered index file group is the file group of the table. If there is no clustered index on the table then it is represented as a HEAP with index_id equal to ZERO in sys.indexes table. A clustered index is always the index with index_id equal to 1 and a non clustered index will always have index_id greater than 1 in sys.indexes table . Below query gets the file group of the table Employee in HumanResources schema of AdventureWorks database. SELECT d.name AS FileGroup FROM sys.filegroups d JOIN sys.indexes i ON i.data_space_id = d.data_space_id JOIN sys.tables t ON t.object_id = i.object_id WHERE i.index_id<2 -- could be heap or a clustered table AND t.name= 'Employee' AND t.schema_id = schema_id('HumanResources') And below query gets the file group of the index ‘AK_Employee_rowguid’ on Employee table in HumanResources schema of AdventureWorks database. SELECT d.name AS FileGroup FROM sys.filegroups d JOIN sys.indexes i ON i.data_space_id = d.data_space_id JOIN sys.tables t ON t.object_id = i.object_id WHERE i.name = 'AK_Employee_rowguid' AND t.name= 'Employee' AND t.schema_id = schema_id('HumanResources') Please note that schema_id is important otherwise both the queries might return multiple results if you have same table name on different schemas. Hope it helps :) ranjith :)
-
Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF
10 Jan 2010These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored…