THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » T-SQL » SQL Server   (RSS)
Showing page 4 of 5 (48 total posts)
  • SET IDENTITY_INSERT little bug(?)

    While I was working on a package to incrementally load a dimension in the DWH I’m developing for a customer, I stumbled upon a tricky little bug (I think, I still have to have a response from MS) that had – and will have – a little impact on how I create my packages. I’ve found that, when working on an empty table that has an identity column, if ...
    Posted to Davide Mauri (Weblog) by manowar on September 30, 2009
  • Extracting insert, update, delete rowcounts from T-SQL MERGE

    Just lately I’ve been using T-SQL’s MERGE statement (introduced in SQL Server 2008) and one thing that I needed to do was extract rowcounts for each DML operation (i.e. INSERT, UPDATE, DELETE) conducted by a MERGE. I was surprised to find that while @@ROWCOUNT is supported for MERGE, it only returns the total number of affected rows and there are ...
    Posted to Jamie Thomson (Weblog) by jamiet on August 29, 2009
  • 2008: Declaring and instantiating a value

    Ok, I admit it.  Sometimes the least important things are the most fun.  As I try to get my blog back up and kicking again after a few months of holiday fun coupled with some dreary personal life things (a death in the family and lots of sickness/busyness, mostly,) I felt the need to write about another little time saving feature that ...
    Posted to Louis Davidson (Weblog) by drsql on March 1, 2008
  • 2008: Rebuilding a Heap

    In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy.  You could copy it to a different table, or you could add a clustered index and then drop it. In 2008, this is a far easier thing to do.  They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered ...
    Posted to Louis Davidson (Weblog) by drsql on February 26, 2008
  • Changing the owner of a database

    Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005.  I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my ...
    Posted to Louis Davidson (Weblog) by drsql on December 5, 2007
  • sys.dm_db_index_operational_stats

      This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into ...
    Posted to Louis Davidson (Weblog) by drsql on August 26, 2007
  • sys.dm_exec_query_optimizer_info

    Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query. (reference: http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx ) Type: View Data: Accumulating ...
    Posted to Louis Davidson (Weblog) by drsql on August 6, 2007
  • sys.dm_io_virtual_file_stats

    Excellent dmv that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!)  The file can be any file used in the database, including the ...
    Posted to Louis Davidson (Weblog) by drsql on July 27, 2007
  • sys.dm_db_index_usage_stats

    This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index ...
    Posted to Louis Davidson (Weblog) by drsql on July 22, 2007
  • sys.dm_db_file_space_usage

    Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis. Type: View Data: temporal, reflects the current state of the file usage Columns: database_id – identifies the database (relates ...
    Posted to Louis Davidson (Weblog) by drsql on July 14, 2007
Privacy Statement