I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a “Restoring” state. I had no disk space issues– the database simply didn’t come out of the “Restoring” state. I ran the following query against the … Read more
Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are … Read more
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In … Read more
Just to clear up… or sum up… nchar and nvarchar can store Unicode characters. char and varchar cannot store Unicode characters. char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don’t use up all that space. varchar and nvarchar are variable-length which will only use … Read more
Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table. This is a quick run through to generate the INSERT statements for all of the data in your … Read more
What you’re looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not… In SQL Server 2008, you can define a … Read more
I cannot reproduce the error. Perhaps I’m not understanding the problem. The following works fine for me in SQL Server 2005, with the extra “foo” column appearing in the second select result: IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL DROP TABLE #Results GO CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT ) GO select company, … Read more
I agree with Cade Roux. This article should get you on the right track: Indexes in SQL Server 2005/2008 – Best Practices, Part 1 Indexes in SQL Server 2005/2008 – Part 2 – Internals One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. … Read more
Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist). Instead, for a permanent table you can use IF OBJECT_ID(‘dbo.Scores’, ‘U’) IS NOT NULL DROP TABLE dbo.Scores; … Read more
Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back. When you write the record back you filter the update on the version to make sure … Read more