View Dror Gluska's profileIn 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 =...
Thursday, June 24, 2010
Wednesday, June 9, 2010
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...
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,...
Subscribe to:
Posts (Atom)