One of the common task that I have come across is the need to convert a list of results into a comma separated list. There are many ways to use this, one if which is in reports. Download the AsCSV.sql sample here. Often times I have told developers, here is how you do it, and if you Google on it …
Converting part of a result set to a comma separated list Read more »
A restore plan of course is far more important than the backup plan. When things go wrong and you need to restore a database backup, that is not the time to be learning how to do a restore, or to find out if you were backing up the right data. My suggestion if your backups are important (why wouldn’t the …
SQL 2012 is being referred to by Microsoft as code name Denali.
I needed a way to list of of the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database. CREATE TABLE ##allindexes ( databse_id INTEGER, table_id INTEGER, index_id INTEGER, table_name VARCHAR(1024) ); EXECUTE Sp_msforeachdb …
Finding the names of all indexes on a SQL Server Read more »
The question comes up when looking at an execution plan of whats the difference between an index seek and an index scan. What it really comes down to is speed, for tables with a large number of rows, and thus indexes with a large number of rows, an index seek may take significantly less time than an index scan. …
Default Values and Computed Columns In SQL Server 2008, I was presented with the following error when attempting to create a table that has a default value that references other columns in the same table. Msg 128, Level 15, State 1, Line 6 The name “Subtotal” is not permitted in this context. Valid expressions are constants, constant expressions, and (in …
MSG 128, Level 15, State 1 – not permitted in this context Read more »
Just the same query that I posted months ago, but this time it is wrapped in a stored proc. I use this one often enough that it is easier to just call the stored proc. Download the FindOneTimeUseQueries source code. TSQL example: CREATE PROCEDURE [dbo].[Findonetimeusequeries] AS BEGIN SET nocount ON; SELECT TOP 100 refcounts, usecounts, size_in_bytes /1024 AS sizeinkb, TEXT FROM sys.dm_exec_cached_plans CROSS APPLY sys.Dm_exec_sql_text(plan_handle) WHERE cacheobjtype = ‘Compiled Plan’ AND objtype = ‘Adhoc’ AND usecounts = 1 AND TEXT NOT LIKE ‘FETCH API_CURSOR%’ ORDER BY size_in_bytes DESC END This posting …
There is a newer version of this posting, click here. Part of my regular database maintenance or tuning routine is to look for the queries with cached plans that are taking up the most space, and have only been used once. The query below is a handy query to track down the queries that may be in need of paramaterization. …
Attached is the slide deck for a short presentation on indexing that was created to educate software developers on the basics of indexing. Outline: What is an Index Types of Indexes Non Clustered Indexes Clustered Indexes When To Avoid A Clustered Index Covered Indexes Index Usage Terminology Summary Additional Topics Download Indexing_Overview.pdf Here is the text outline of the presentation. …
There was no change to the compatibility levels between SQL Server 2008 and SQL Server 2008 R2. SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server. 60 = SQL Server 6.0 65 = SQL Server 6.5 70 …