Database Simplified

Bad Performance Of View In SQL Server

2012-06-10T20:44:00.002-07:00

In this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script If(OBJECT_ID('PerfTest1') is Not null)Drop Table PerfTest1 Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30)) Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')Go 300 If(OBJECT_ID('PerfTest2')

Restore Database From Snapshot

2012-06-03T20:48:00.000-07:00

This is another way of restoring database to a time when snapshot was taken but it has certain restrictions lets see what are those ? Database should have only one snapshot. Cannot restore files removed after taking snapshot. Transaction log backup chain is broken So make sure you take full backup before restore. Restore From snapshot will drop all full text catalogs. You must understand that

Import Text File To SQL Server

2012-05-29T21:21:00.000-07:00

This blogs describes how flat files or text files can be imported to SQL Server with defining field information in a format file (xml file). Let’s see how ? Download Sample Text File So You can download the text file from the given link or if you have your own flat/text file with you then copy file and put it into the folder. For me its “D:\dbsimplified” Now You must be knowing fields(FieldName)

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

2011-12-25T20:28:00.002-08:00

Collation conflicts are encountered when u try cross database query or cross server query or joining tables, because joining tables might have different collation settings. You must handle these query with specifying appropriate collation while joining to tables having different collation data. Let Replicate the issue with following example. Create following tables with script given below. CREATE

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

2011-12-25T19:05:00.001-08:00

Inserting explicit value for identity column in a table can not be done directly as identity column is bound to rule of incrementing them with specified number. If you try to insert value explicitly you encounter following error message. Msg 8101, Level 16, State 1, Line 1An explicit value for the identity column in table 'TableName' can only be specified when a column list is used and

Nested Transaction In SQL Server

2011-12-05T07:53:00.001-08:00

SQL Server facilitate us to define nested explicit transaction. We may need to define nested transaction in many situation, so before you define or use nested transaction you must be aware of behavior of nested transaction. Create tables from following script to perform following tests.Create Table Parent_Table(Id int Identity(1,1),DataValue Varchar(20))Go Create Table Child_Table(Id Int Identity

How To Move TEMPDB In SQL Server

2011-12-02T23:34:00.001-08:00

In this blog post we will see how can we move location of tempdb database files in sql server. Moving of database may be required for many reasons like for security purpose or space constraints or etc. As tempdb is system database it cannot be detached or attached and cannot be restored like user databases. You need to be more alert when you are planning to move tempdb database files. Let’s see

Memory Utilization In SQL Server (Database Level)

2011-11-26T08:37:00.001-08:00

Its common question for DBAs to find memory consumption by each database. With SQL Server 2000 its was quite difficult to find but with SQL Server 2005 + we have DMV (Dynamic Management Views) “sys.dm_os_buffer_descriptors” which provides memory utilization by each database or table in database. So let’s use the DMV to find current memory utilization by each database. Before we start this

Move Database In SQL Server With Minimum DownTime

2011-11-24T07:46:00.001-08:00

When working with very large database and there is need to move database from one server to another, then downtime is always an issue in such cases. There are several ways to move database from one location to another. Here I am describing an efficient way to move database from one location to another location. Following option only works if database recovery modal is Full or Bulk Logged. Let’s

Database Backup At Multiple Location Using Mirror In SQL Server

2011-11-10T08:46:00.001-08:00

SQL Server 2005+ At times we come across issue where we need multiple copies of database at different places. Many people end up taking single backup of database and making required copy of backup and moving them to required location using some cmd or script. But now this can be achieved using MIRROR with backup command. Mirror option allows you to create maximum 4 copies of database backup at a

How To Get Comma Separated Value In SQL Server.

2011-11-09T08:40:00.001-08:00

In the earlier post we have seen use of xml path to get comma separated result. This post describes how to get comma separated value from a result set or a table data. Create Table With Following Script. Create Table CSData(Id Int Identity(1,1),DepartmentName Varchar(40))Go Insert Into CSData(DepartmentName) Values('IT'),('FINANCE'),('MEDICAL'),('TRANSPORT'),('TALENT TRANSFORMATION'),('IMG')Go

The media family on device is incorrectly formed. SQL Server cannot process this media family

2011-11-09T08:08:00.001-08:00

This blog post deals with the problem of restoring SQL Server Database from higher version to lower version. We generally encounter error following error. Error while restoring database from SQL server 2008 to SQL server 2005 Msg 3241, Level 16, State 7, Line 1 The media family on device ‘’ is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State

How to find list of stored procedure with content info in SQL Server

2011-11-07T07:17:00.001-08:00

This post describes the how can you find list of stored procedure with specific text content or how to answer following question. List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement. List All Stored Procedure, Function, Views, Triggers which uses temporary table. List All Stored Procedure, Function, Views, Triggers which uses a particular table.

Find Tables With Column Name in SQL Server Databases

2011-11-07T06:36:00.001-08:00

This post describes how one can find list of all the tables where a column with a particular name exists in a databases. Many people come across this issue and searches for query. So here is the query with example. I am using following query to find out all the table having name “Status” in master database.Use masterGoSelect B.name as TableName, A.name As ColumnName,C.name as DataTypefrom

Is Data Always Sorted On Primary Key (Clustered Index) In SQL Server ?

2011-11-02T20:23:00.001-07:00

Whenever we create a primary key on a table and we generally get data sorted on primary key defined. but that’s not always the case. Let’s examine it. Create Table and Populate Data With Following ScriptIf Exists(Select * From sys.Objects where object_id=object_id('Records'))Drop Table Recordsgo CREATE TABLE [dbo].[Records]( [Id] [int] IDENTITY(1,1) Primary Key NOT NULL, [EmpName] [varchar

Policy In SQL Server

2011-10-29T10:04:00.001-07:00

SQL Server 2008 + Policy based management introduced in sql server 2008 is really a nice feature. Where DBA can manage certain activity by enforcing policy based on specified conditions. You can enforce policy at database level. How To Create Policy ? The following step by step shows creation of policy for a stored procedure name standard. Create a condition for policy Create A Condition

Create SNAPSHOT In SQL Server

2011-10-28T23:25:00.001-07:00

SQL SERVER 2005 + (Only Enterprise Edition) SNAPSHOTS are read-only views of actual database of a particular time and are really useful for sharing load for reporting purpose. You can have multiple snapshot against a single database. Working of SNAPHOT ? Suppose you have created SNAPHOT of database at 9:00 AM. You will see that (.ss) snapshot file size is very small initially and as you modify

Password Protected Backup In SQL Server

2011-10-28T22:19:00.001-07:00

Password protected database backup : Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore and see details from backup file using RESTOREFILELIST or HEADERONLY command. How to take password protected database backup? Create A DatabaseIf DB_ID('DBS_Test') Is null Create Database DBS_TestGo

Get Information From Backup File In SQL Server

2011-10-28T22:13:00.001-07:00

This post describes how can we get information from backup file without restoring it. information about file groups, data and log files and Database information like server name and user name, internal database version, backup size and etc. Backup A Database Backup Database CDB To Disk='D:\CDB_Full.Bak' With MediaName='CDB_FullBackup'Go Now we have a backup file located at D:\CDB_full.Bak, With

Configuring Database Mail In SQL Server Step By Step

2011-10-26T22:45:00.001-07:00

To Configure Database Mail you must create database mail account and profile.Following are the steps to configure Database Mail Account And Profile. Once Account and Profile has been created you must configure database mail using sp_configure stored procedure exec sp_configure 'show advanced options',1Go Reconfigurego sp_configure 'Database Mail XPs',1go Reconfigurego Now the database

Create User In All The SQL Servers

2011-10-26T21:33:00.001-07:00

I have found many people questioning on MSDN forum “How to run a query or create a user in all sql server instances” and the answer is using Central Management Server (Registered Servers). see following example of creating a user in all the sql server instances. I have following three instances of SQL where we need to create a user using registered servers. Goto Menu : View—>Registered Servers

ISNULL VS COALESCE, Nested ISNULL

2011-10-19T22:28:00.001-07:00

There is a myth bound to COALESCE function that it is just a replacement of ISNULL. Many candidate in interview also agree to this statement that COALESCE is just replacement of ISNULL but most of them were not aware of advantage of using COALESCE in place of ISNULL. So Lets See ISNULL VS COALESCE Test 1 : Similar Functionality Of ISNULL and COALESCE Use of ISNULL Declare @Id1 IntDeclare @Id2

Query To Find SQL Server Service Account

2011-10-17T09:22:00.001-07:00

SQL Server Services Account are stored in registry keys. You can read those key using built-in stored procedure “xp_instance_regread”. These account information are stored under following keys HKEY_LOCAL_MACHINE --> SYSTEM --> CurrentControlSet --> services –> InstanceName Instance Name: It is MSSQLServer for Default Instance. and if you have named instance then it is MSSQL$instancename. For

Change Windows Authentication To Mixed Mode In SQL Server

2011-10-17T08:31:00.001-07:00

This post describes changing Window Authentication mode to Mixed Authentication mode in SQL Server. When you install SQL Server by default window authentication is installed If you do not choose mixed authentication at the time of SQL Server installation. However these modes can be changed even after installation of SQL Server. Let’s See How We Can Change Window Authentication of Mixed Mode. Note

Server Side Trace In SQL Server

2011-10-13T12:03:00.001-07:00

Running SQL Server Profiler trace are considered to be very costly operation. We generally minimize the load of Profiler trace by filtering the columns and limiting the event in trace. Alternatively we have “Server Side Trace” The Server site trace provides most effective way to trace then SQL Profiler. Server side trace runs on Server so no network congestion takes place. Server side trace runs