Category Archives : Sql Server DEV

Sql Server DEV

A script to find/drop all orphaned users in SQL Server Databases

Scenario Proposal One of my many day to day duties includes administering a database (actually many databases) for a Human Resources application. This application uses SQL logins for data access. This is a database I inherited so I had no input on how things were setup or administered. In the past the application administrator used […]

Location of full text temporary folder in SQL Server

Scenario The process of creating, building, and maintaining full-text search capabilities is quite a daunting task. During the installation of Full-Text Search for SQL Server 2000, one of the folders gets installed in a not-so-conspicuous location that could potentially be troublesome as the server gets busier. To get a good start on building and maintaining […]

Sql Interview question #8 : What is collation?

Sql Server Interview question #8 What is collation? Solution Collation refers to a set of rules that determines how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.   Thanks for reading this […]

How can I clear the SQL Server query cache?

Scenario When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem.  To determine the best overall solution, each option is tested and the results are recorded.  As lessons are learned options may be combine for a better end result […]

How to drop and recreate SQL Server foreign keys?

Scenario You may have been in a scenario where you needed to quickly generate a script to drop and then subsequently re-create all of the foreign keys in a database (or in a specific schema, or matching a specific naming scheme). In some situations you can simply disable and re-enable the constraints, which isn’t all […]

Automatic database restore using DateTime functions in SQL Server

Scenario I have to restore a production database to a development database on a weekly basis in order to refresh the environment. Currently, I’m doing this process manually. Is there any way to script this out so it can be automated?  Check out this tip to learn more.   Solution If you take full backups […]

Synonyms in SQL Server 2005 (benefits and limitations)

Scenario I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application code. Moving them was demanding and also a very complicated process. I wanted […]