Sales data in my production database has increased significantly. I am planning to archive this data apart from the production database, so this can still be used for reporting purposes. I am conscious about performance issues while inserting new data to
I know that moving the log file of a production SQL Server database to separate physical drive is a best practice to optimize the I/O performance. Recently, we have added a physical drive to our SQL Server. Based on this change, I am planning on moving t
I have implemented a backup plan for my SQL Server databases. I take a daily full backup supported with hourly differential backups. A restore plan is documented based on this backup plan along with the location and time of the backups mentioned in the pl
Our development team created a new module for a production web portal. This new module includes new tables, foreign keys, indexes, lookup tables, associated data, views, stored procedures, user defined functions, etc. which were created in an existing dat
Almost every DBA configures SQL Agent Jobs for automation of repetitive tasks. This valuable facility works in the background in SQL Server and saves a lot of effort and manual rework. You may have a number of jobs scheduled and often there is the need to
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to
As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and selec
Almost every SQL Server object that is created may need to be dropped at some time. Especially when you are developing you create a bunch of temporary objects that you probably do not want to keep in the database long term. Most SQL Server users drop on
In my recent article Using Computed Columns in SQL Server with Persisted Values I discussed how to create computed columns to improve performance in specific scenarios. In order to achieve maximum performance through computed columns one very important a
SCHEMA BINDING is commonly used with SQL Server objects like views and User Defined Functions (UDF). The main benefit of SCHEMA BINDING is to avoid any accidental drop or change of an object that is referenced by other objects. A User Defined Function (UD
In this article we look at SQL Server computed columns with persisted values and how to set this up along with some examples.
In this tip we look at a simple query you can use to get a list of parameters for all stored procedures and functions in a database.
The cache management mechanism plays an important role in performance of any system. Like any reliable DBMS, SQL Server enjoys a sophisticated cache management system for optimized performance without the need for any user intervention. There are ways to
One of the issues I often face is the need to find views that are already established for certain tables. This maybe for other developers, end users or even for myself. I could search the system tables to find this or explore each view, but are there oth
SQL Server provides numerous system data types to store dates, character based data, numeric data, etc. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in
While creating T-SQL scripts with lengthy T-SQL lines you have one of two options; either use the horizontal scroll bar or create line breaks at various points to make the line readable without having to use the scroll bar. Scrolling or having to break t
Reducing the amount of storage that SQL Server uses is not as high of a priority as it used to be, but when tables grow to millions of rows a few bytes per row can have significant effect. For very large databases the storage issue gets more critical for
Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on t
Database backups hold primary importance among daily DBA tasks. This task is typically automated through maintenance plans, scheduled SQL Server Agent Jobs or third party tools. With the importance of backups it is necessary to regularly analyze the perf
After attaching or restoring a SQL Server database, orphaned users may emerge. Read this tip for help fixing them.
On my new job 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 cod