I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I did
SQL Server allows you to create additional filegroups to spread storage of data and indexes to different disks to improve I/O performance. The issue is that when creating new data storage objects we are required to explicitly point to the new filegroup.
Due to a recent rebuild of the master database for a SQL Server instance, I now need to restore the msdb and model databases. In this tip we walk through the process that you need to follow to restore the model and msdb databases successfully.
Computed columns are easy to setup for simple scenarios, but in this tip we look at how to get more creative when using computed columns.
In this tip we look at the different options when using SQL Server foreign keys.
I noticed a new feature that was first introduced in SQL Server 2005, which is the Emergency state for a database. It looks that it may facilitate dealing with a suspect database. In this tip we look at how to use the Emergency state for a suspect datab
I have configured a few SQL Server agent jobs for different purposes on my database server. Some jobs are required to run at the same time. In other words I am required to configure the same schedule for multiple SQL Server agent jobs. In this tip we look
I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to genera
In a previous tip, the tip reviewed several steps for a better and improved environment for scheduled jobs. I am totally convinced with the recommendations and want to implement these for my scheduled jobs. To make this easier, I want to create a templat
In a previous tip we discussed how to register servers in SQL Server Management Studio. Now that I have my registered servers setup the way I like, I need to setup other computers the same way that are used to manage my SQL Servers. Instead of having to
I have to manage a number of SQL Server instances across the network. To access the servers I generally use IP addresses along with a custom port number if one is configured. I could use the server name, but the server names are getting longer and also l
I have read the tip about securing SQL Server backups with a password. It is great option and I am planning to implement it for my production servers on SQL Server 2005 and SQL Server 2008. I am currenlty using a backup plan configured through maintenance
I have changed the password of one of my SQL Server logins used for SQL Server Maintenance Plans. Everything was okay until a SQL Server Agent Job responsible for database backups failed. The SQL Server Agent Job was created by the Maintenance Plan Wizar
A new service, SQL Server Browser, was introduced with SQL Server 2005 and is also used with SQL Server 2008. In this tip I will cover what this is and how to use this service to improve your SQL Server environment.
Learn how to configure the Windows Firewall to make sure SQL Server can work correctly and the necessary ports are configured correctly.
Databases whose architecture or data is not required to be updated should be considered to be set as READ ONLY databases. For one of my archival databases, I am looking to make it READ ONLY and would like to know what steps I should take. In this tip I g
In this tip I cover which tool is best for altering tables in SQL Server with minimum impact to your system.
SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assi
Almost every DBA has to manage more than one instance of SQL Server. Chances increase to run a script on the wrong instance. In this tip, I will show an option in SSMS 2008 to easily identify each instance.
Performing DML operations through tested application are always safe and efficient. This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certai
Learn how to maximize the space for queries and results in SQL Server Management Studio with these settings and keyboard shortcuts.
I am required to restrict the access to some users to view the definition of specific SQL Server database objects. I am aware of the WITH ENCRYPTION clause that is used to encrypt the code for objects like views, stored procedures and functions. It sort o
Trying to visual a database model is sometimes quite difficult. When the model is small it is pretty easy to have an idea what tables reference other tables. But at the data model gets larger it is often difficult to see exactly how the tables relate.
I have created some views to provide limited data access for an application. Several of these views are composed of more than one table and there are problems while performing insert, delete, or update operations on multi-base table views. I am required t