In a previous tip we looked at how to determine which indexes were being used for a SQL Server 2005 database. Databases have two primary storage needs; data pages and index pages. Understanding and viewing the actual data in your tables is pretty st
I need to encrypt data in my SQL Server 2005 database and currently have a Database Master Key and Certificate installed. Can I encrypt using only a certificate? The answer is yes. Symmetric and asymmetric keys are not required for the encryption/decryp
Lean about the FREETEXT command versus the CONTAINS command for full-text queries in SQL Server.
I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table bas
We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent
Blunders, mistakes, absent mindedness, being over extended - you name, it happens. Whether it happens to you or someone on your team, the repercussions can be severe. Over most people's careers you have seen issues to a varying degree of severity. To m
Wouldn't it be great if some of the great DBA management queries you have found on MSSQLTips were available in a Reporting Services portal so that you could run them from anywhere? Imagine how convenient that would be. You could even share them with other
When bulk loading data into SQL Server, in most cases you want to have this process run as fast as possible. Depending on the options you select and how indexes are setup on the table, the time to load the data could differ quite drastically. In this ti
Check out this tip to automatically create scripts to disable, enable, drop and recreate SQL Server Foreign Keys.
Execute a stored procedure from SSMS and pass in parameter values using the built-in form that SSMS creates.
In this tip we look at how to build a CLR function in SQL Server to delete old backup files.
One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 200
With the new rules from the National Institute of Standards and Technology on the Daylight Savings Time calculations, a new set of logic is need to determine if daylight savings time is in effect. Since the new rules have recently been applied to our ser
It is common in many SQL Server database management scripts to rename databases, detach and attach databases, change the database status, etc. With all of the changes related to deprecated SQL Server features, are any of these commands (sp_attach_db, sp
In your recent tip (WARNING: SQL Server Deprecated Features) you have outlined a number of deprecated features. The listing with the mapping to the new code set is beneficial. Unfortunately I have an enterprise SQL Server environment and I need to be ab
You know the old expression, Be careful what you wish for, because it might come true! That goes the minute your Reporting Services team grows from one person to any size larger. How do you manage the code? What if you are working on a multi-developer t
Working with strings in SQL Server is not as simple as you would hope for a lot of things that you need to do with text data. It is sometimes simpler to do a lot of these things outside of SQL Server, but if you have time and patience to write T-SQL code
In this tip we look at how to save an execution plan for future review as well as using the full screen mode to see more of a query plan at one time.
This tip covers SQL Server 2008 MERGE and how to use it.
When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of the
Learn how to consume SQL Server 2008 output directly from the OUTPUT command.
When troubleshooting a potential SQL Server performance problem, it is difficult to know if the code is problematic without being able to review all of the code. You could ask the Developer for the code you suspect, run Profiler to capture code, leverage
When re-building a table’s clustered index using a DROP INDEX command followed by a CREATE INDEX command, if a table has non-clustered indexes then all of non-clustered indexes on the table may be automatically rebuilt twice by the SQL Server engine. Onc
Your data warehouse is rock solid. You have multiple sets of fantastic reports. Departments utilize their own designated reporting areas, and you've increased productivity and value (ROI on the BI program) by focusing operations on critical areas by offer