Tuesday, September 27, 2011

Using OUTPUT to set up foreign keys' data


So, I was looking at a process that inserts records into table A (one at a time), then uses SCOPE_IDENTITY() from each insert to get the ID and uses that for the insert into table B, in order to provide a cross-reference tabled (foreign key).  

Obviously, there's a better way - OUTPUT, usable since SQL Server 2005.

An example.  We populate “blah”, then use the values from that to insert into “blah2”, with a foreign key ID of the first table.  

CREATE TABLE blah (id INT IDENTITY, logon_name VARCHAR(50))
CREATE TABLE blah2 (id INT IDENTITY, blahID int, dn VARCHAR(200))

INSERT INTO blah
      SELECT TOP 10 name FROM sysusers
INSERT INTO blah
      SELECT TOP 10 name FROM sysusers
DELETE FROM blah
--incrementing to show different ID values.

DECLARE @insertedlist TABLE (id int, the_logonname VARCHAR(50))

INSERT INTO blah
output inserted.id, inserted.logon_name INTO @insertedlist
SELECT TOP 10 name
FROM sysusers ORDER BY createdate

INSERT INTO blah2 (blahid, dn)
SELECT insertedlist.id, sysusers.uid
FROM @insertedlist insertedlist INNER JOIN sysusers
ON insertedlist.the_logonname = sysusers.name

SELECT * FROM blah
SELECT * FROM blah2

Thursday, September 8, 2011

[Free Space] finding what filegroups your data is saved onto.

Using this to tell me where my indexes are, but more importantly - where my data is. What filegroup/file. It produces multiple rows when there are multiple files for a filegroup; need to code a better way. Note that all the WHERE clauses are optional; we couldn't figure out where our data was until we dropped them. 10 gig in a heap table, and 20gb in a service broker table.
SELECT
o.name AS Table_Name ,
i.NAME AS Index_Name ,
CASE i.type
WHEN 0 THEN 'Heap'
WHEN 1 THEN 'C'
WHEN 2 THEN 'NC'
ELSE '?' END AS [Type],
p.rows AS [#Records] ,
a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
a.used_pages * 8 / 1024 AS [Used(mb)] ,
s.user_seeks ,
s.user_scans ,
s.user_lookups,
fg.name,
f.name,
f.physical_name
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
INNER JOIN sys.allocation_units AS a ON ( a.type = 2
AND p.partition_id = a.container_id
)
OR ( ( a.type = 1
OR a.type = 3
)
AND p.hobt_id = a.container_id
)
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID(DB_NAME())
AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
LEFT OUTER JOIN sys.database_files f ON f.data_space_id = a.data_space_id
LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = a.data_space_id
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100
WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
--AND i.TYPE_DESC <> 'HEAP'
AND i.type <> 1 -- clustered index
ORDER BY o.NAME ,
i.name

Thursday, September 1, 2011

[Index] Size, usage, and location of your indexes

Updated my old code, since we were trying to figure out what indexes needed to get moved to the secondary (index) filegroup. You can even filter based off the usage (commented out below)
Standard disclaimer applies. Select the contents of the post, then copy/paste.

Note that there can be dupes - this is by design.  If a table spans multiple files, you'll see multiple rows returned with everything identical except for the filename.

SELECT
        o.name AS Table_Name ,
        i.NAME AS Index_Name ,
--        i.type_desc,
  CASE i.type
   WHEN 0 THEN 'Heap'
   WHEN 1 THEN 'C'
   WHEN 2 THEN 'NC'
   ELSE '?' END AS [Type],
   p.partition_number,
   p.rows AS [#Records] ,
        a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
        a.used_pages * 8 / 1024 AS [Used(mb)] ,
        s.user_seeks ,
        s.user_scans ,
        s.user_lookups,
        fg.name, 
        f.name,
        f.physical_name
FROM    sys.indexes AS i
        INNER JOIN sys.partitions AS p 
   ON i.object_id = p.object_id
            AND i.index_id = p.index_id
        INNER JOIN SYS.OBJECTS O 
   ON I.OBJECT_ID = O.OBJECT_ID
        INNER JOIN sys.allocation_units AS a 
   ON ( a.type = 2
    AND p.partition_id = a.container_id
               )
   OR 
    ( ( a.type = 1 OR a.type = 3)
    AND p.hobt_id = a.container_id)
        INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S 
   ON S.OBJECT_ID = I.OBJECT_ID
   AND I.INDEX_ID = S.INDEX_ID
   AND DATABASE_ID = DB_ID(DB_NAME())
   AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
  LEFT OUTER JOIN sys.database_files f 
   ON f.data_space_id = a.data_space_id
  LEFT OUTER JOIN sys.filegroups fg 
   ON fg.data_space_id = a.data_space_id
WHERE   OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
        AND i.TYPE_DESC <> 'HEAP'
        AND i.type <> 1 -- clustered index
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 
ORDER BY o.NAME ,
        i.name