SQL Identifying Foreign Keys

In the past I've got a few legacy systems to maintain and improve, some of them with good documentation, some of them with non-existing documentation some even my own which were left alone for too long before I was able to document them.


This great article, written by Greg Robidoux has been an invaluable help in determining relationships in SQL, thank you Greg!


I've removed a few fields and left with this:

SELECT --PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       --PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)), 
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME), 
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME), 
       --FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       --FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)), 
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME), 
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME), 
       -- Force the column to be non-nullable (see SQL BU 325751) 
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)), 
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)), 
       PK_NAME = CONVERT(SYSNAME,I.NAME), 
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE 
FROM   SYS.ALL_OBJECTS O1, 
       SYS.ALL_OBJECTS O2, 
       SYS.ALL_COLUMNS C1, 
       SYS.ALL_COLUMNS C2, 
       SYS.FOREIGN_KEYS F 
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K 
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID) 
       INNER JOIN SYS.INDEXES I 
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID 
             AND F.KEY_INDEX_ID = I.INDEX_ID) 
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID 
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID 
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID 
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID 
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
order by PKTABLE_NAME, FKTABLE_NAME

Read More

Tags:

Dror Gluska June 24, 2010  No comment

SQL Query Hint With (NOLOCK)

SQL is an atomic transactional database by default, which means that each change either finishes completely or doesn't change anything.


This forces SQL server to use locks to perform its tasks, but what if we don't have to have the most updated data? or we don't care about records that are being modified right now and we're willing to accept some errors?


Example? 
Some statistics, it might change right now, we might not even care if lets say a user logged in once today or not at all, perhaps a refresh is being done every 5 minutes to update the UI and if the user won't show up on the screen now it will show up later?


But what am I saying?


If for every write to the statistics the table is being locked, but you need to get the current statistics without being delayed every update, what should you do? well, you can ask SQL to ignore the locks and give you whatever it has right now, consistent or not, you don't care as long as its close enough and fast.


For that we have nolock query hint.

select ... from statistics with (nolock)

Read More

Tags:

Dror Gluska June 09, 2010  No comment

SQL Query Hint WITH (NOEXPAND)

If you've reached to the point that you optimize your queries with indexed views, you probably know what you're doing or at least willing to try and understand execution plans.


Indexed views are great (as long as you don't abuse them), they can be an aggregate which is always ready to be used, you can index the columns of the view and get overall huge performance boost.

create view dbo.vwView with schemabinding
as
  select ...

But, if you don't use SQL Enterprise edition, the query optimizer needs to be told explicitly to use them, otherwise it will treat the indexed views like any other views, get the underlying query and use it instead. which defeats the purpose of having an indexed view.


So, what do we do? add with (noexpand) to queries that might benefit from the indexed view's indexes, note that you shouldn't use it blindly, look at the execution plans for both options and base your decision on that.

select ... from dbo.vwView with (noexpand)

Read More

Tags:

Dror Gluska June 09, 2010  No comment