Find All Queries Run Against a Table
-
Posted on June 26, 2009 by Derek Dieter
Dynamic management view to find all queries that have been run against a table since the last reboot.
Continue reading ...SQL Server 2005 vs SQL Server 2008
-
Posted on June 25, 2009 by Derek Dieter
Outlines some new features of SQL Server 2008 and provides the reasons to upgrade instead of 2005.
Continue reading ...SQL Server 2008 Minimally Logged Inserts
-
Posted on June 14, 2009 by Derek Dieter
SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the requirements below are met. Trace flag 610 must be on Database recovery model must be bulk-logged or Simple Inserted data must be ordered by the clustered index To turn on the trace flag for your current session: [cc lang=”sql”] DBCC TRACEON (610) INSERT INTO dbo.MyTable SELECT * FROM ORDER BY 1 DBCC TRACEOFF (610) [/cc] This new change differs dramatically from the previous requirements for minimal logging. Previously there could be no clustered index and a table lock had to be acquired on the target table. For more information, visit: Minimal Logging Changes – MSDN Blog
Continue reading ...Custom Pagination with Dynamic ORDER BY
-
Posted on June 10, 2009 by Derek Dieter
SQL Server Denali has a new feature allowing pagination using the order by clause. A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the old fashioned database paging solution. This paging solution initially grabs a subset of a table and counts the records. It then stores ordered results based on the parameter passed into the common table expression. Additional parameters are the number of rows the caller wants on each page and the page number the caller is currently retrieving. [cc lang=”sql”] CREATE PROCEDURE dbo.GetEmployees ( @SortColumn VARCHAR(50) = null, @iRows INT = 10, @iPageNum INT = 1 ) AS BEGIN SET NOCOUNT ON DECLARE @RecordCount int DECLARE @iNbrPages int SET @RecordCount = 0 SET @iNbrPages = 0 SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.DateHired INTO #Employees FROM HR.Employees emp WHERE emp.IsTerminated = 1 SELECT @iNbrPages = CEILING(COUNT(1)/(@iRows*1.0)), @RecordCount = COUNT(1) FROM #Employees BEGIN ;WITH PagingCTE ( Row_ID, EmployeeID, FirstName, LastName, DateHired ) AS ( SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortColumn = ‘EmployeeID’ THEN emp.EmployeeID END ASC, CASE WHEN @SortColumn = ‘FirstName’ THEN emp.FirstName END ASC, CASE WHEN @SortColumn = ‘LastName’ THEN emp.LastName END ASC, CASE WHEN @SortColumn = ‘DateHired’ THEN emp.DateHired END ASC ) AS [Row_ID], emp.EmployeeID, emp.FirstName, emp.LastName, emp.DateHired FROM #Employees emp ) SELECT emp.EmployeeID, emp.FirstName, emp.LastName, […]
Continue reading ...Find Dependent Objects
-
Posted on June 9, 2009 by Derek Dieter
One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies. The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL. [cc lang=”sql”] USE MYDatabase GO DECLARE @TableName varchar(100) SET @TableName = ‘mytable’ SELECT SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id) ,SourceObject = OBJECT_NAME(sed.referencing_id) ,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME()) ,ReferencedSchema = ISNULL(sre.referenced_schema_name, OBJECT_SCHEMA_NAME(sed.referencing_id)) ,ReferencedObject = sre.referenced_entity_name ,ReferencedColumnID = sre.referenced_minor_id ,ReferencedColumn = sre.referenced_minor_name FROM sys.sql_expression_dependencies sed CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ‘.’ + OBJECT_NAME(sed.referencing_id), ‘OBJECT’) sre WHERE sed.referenced_entity_name = @TableName AND sre.referenced_entity_name = @TableName [/cc]
Continue reading ...
