From time to time I get asked about checkDB, and there are many solutions out there, but I have one that I generally use that is very simple and does the job. The script below created a stored procedure in the DBHealthHistory database that can be used to check as many databases as you can get through in a specific …
Updating SQL Server statistics may not be as obvious as it may sound. Imagine this scenario. I invite you over for dinner and a game of Scrabble. After dinner, I start to do the dishes, while you set up the game. Rather than just doing the dishes that are dirty from our dinner, I decide to take all the dishes …
Tables, and indexes are organized in SQL Server into 8K chunks called pages. If you have rows that are 100 bytes each, you can fit about 80 of those rows into a given page. If you update one of those rows to have more data that contains a variable length field like VARCHAR, NVARCHAR, and others, that will cause the …
First lets take a look at two queries using CURSORS, the first one will use the FORWARD_ONLY type cursor, and the second will use the FAST_FORWARD type cursor. These two types sound very similar, but perform quite differently. Now for the FAST_FORWARD CURSOR example. Notice only one line has changed, that’s the line that says “SET @PeoplePhoneCursor = CURSOR FAST_FORWARD …
Simple Cursor Example : FORWARD_ONLY vs FAST FORWARD Read more »
In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL …
T-SQL: A Listing Databases Example Using a Cursor Read more »
Being day 22 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC OUTPUTBUFFER. I missed a few days on the DBCC Commands due to attending SQL Saturday in Redmond, and the release of my book on Amazon.com. I am now back on track to finish out the rest of the month with more DBCC commands. Description: …
For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman. Cursors are a feature of SQL Server that allow users to iterate through a result set one row at a time. This can be useful in situations where a user needs to perform a set of operations on each row of a …
SQL Server 2012 adds a new stored procedure called sp_describe_first_result_set. This new procedure returns metadata for the result set returned from a query. The metadata is information about what the results will look like. sp_describe_first_result_set is an alternative to sp_columns, and appears to perform much better than sp_columns. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL …
T-SQL 2012 Procedure sp_describe_first_result_set Read more »