Archive for the ‘Database’ Category
Posted by Jugal Shah on May 21, 2012
In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (even you can INSERT/UPDATE/DELETE data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users
See the below example how we can make the view read only.
--creating a sample table
Create table tbl1
(
myID int,
name varchar(10)
)
--inserting data
insert into tbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')
--creating sample view
create view vwtbl1
as
select * from tbl1
--inserting data using view
insert into vwtbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')
--altering view to make it readOnly
alter view vwtbl1
as
select myid,name from tbl1
union all
select 0,0 where 1 =0
INSERT/UPDATE/DELETE will fail with the below errors.
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'vwtbl1' failed because it contains a derived or constant field.
Msg 4426, Level 16, State 1, Line 1
View 'vwtbl1' is not updatable because the definition contains a UNION operator.
Posted in SQL Server, SQL Server 2008, Database, DB Articles, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: RDBMS, T-SQL, View, SQL Code, Query, Read Only | 1 Comment »
Posted by Jugal Shah on April 16, 2012
Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.
Check the below example.
create table employee
(
empID int not null,
SSN int not null,
name varchar(20)
)
ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)
create table EmpDetail
(
empID int,
SSN int,
address varchar(20),
city varchar(20),
pin varchar(20)
)
ALTER TABLE dbo.empDetail
ADD CONSTRAINT FK_Employee
FOREIGN KEY(empID, SSN)
REFERENCES dbo.employee(empID, SSN)
SELECT
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
ccu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
tc.TABLE_NAME IN ('employee','employeeDetail')
Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Compound Key, Foreign Key, SQL, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T-SQL, Technology | Leave a Comment »
Posted by Jugal Shah on April 15, 2012
You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.
Primary key will not allow NULL values.
-- Adding the NON NULL constraint
ALTER TABLE [TableName]
ALTER COLUMN PK_ColumnName int NOT NULL
--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)
If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
--script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)
Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Software Development, SQL, SQL Coding Standards, SQL Constraint and Keys, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T-SQL, Technology | Leave a Comment »
Posted by Jugal Shah on March 4, 2012
One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.
create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go
SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;
Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Collation, http://sqldbguru.blogspot.com/, Jugal Shah, SQLDBPool.com, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on March 2, 2012
Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.
Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”
SA login account and the few policy certificates also come in SQL Login type, I have excluded them.
select *
from sys.server_principals
where name = 'sa' or substring(name, 1, 1) = '#'
select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'
Next step is to execute the output of the above query.
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Drop Login, SQL Login, SQL Server, SQLDBPool, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on February 12, 2012
Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.
Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011
select @@VERSION
--method - 1
select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary
-- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout
select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion
--Method 2
select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: @@MICROSOFTVERSION, How to check SQL Version, Jugal Shah, SQL Script, SQLDBPool, T-SQL | Leave a Comment »
Posted by Jugal Shah on February 12, 2012
A deadlock occurs when two or more processes permanently block each other by each process having a lock on a resource which the other process are trying to lock.
Please execute the below queries as per the mentioned comments to produce a deadlock.
--turning on the traceflag to record deadlock info into error log
dbcc traceon(1204,-1)
dbcc tracestatus(1204)
--creating test database
create database sqlDBPool
--Connecting to SQLDBPool database
use sqldbpool
--table creation
create table tb1 (col1 int)
create table tb2 (col1 int)
--inserting dummy records
insert into tb1 values(1),(2),(3)
insert into tb2 values(1),(2),(3)
--Open first connection to update table explicit transaction
begin transaction
update tb1 set col1 = 5
--Open second connection to update table explicit transaction
use sqlDBPool
begin transaction
update tb2 set col1 = 6
update tb1 set col1 = 6
--Open first connection to update table explicit transaction
update tb2 set col1 = 5
You can see the one of the transaction will fail with the below error message.
Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As we have turned on the deadlock trace flag, you can see the below information in the SQL Server error log.
Starting up database 'sqlDBPool'.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
RID: 9:1:153:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 1:
Owner:0x05684480 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x065F82A8
SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update tb2 set col1 = 5
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
NULL
Node:2
RID: 9:1:155:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 2:
Owner:0x067679A0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x05A8CC38
SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 3
Input Buf: Language Event: begin transaction update tb2 set col1 = 6 update tb1 set col1 = 6
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x065F8280 Mode: U SPID:52 BatchID:0 ECID:0 TaskProxy:(0x0941A354) Value:0x6a943a0 Cost:(0/432)
NULL
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Deadlock, deadlock scenario, Deadlock trace flag, Jugal Shah, Script, SQLDBPool, T-SQL | 2 Comments »
Posted by Jugal Shah on December 20, 2011
Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.
Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.
--created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON PRIMARY
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
LOG ON
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO
--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))
--inserting records
insert into tb1 values('Jugal')
go 10;
--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO
-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'
-- script to attach the database
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO
When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.
File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
New log file 'F:\singleFileDemo_log.LDF' was created.
Once the database is ready execute the DBCC CHECKDB for any error.
18.520469
73.856621
Posted in Database, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Attach Database, Create database, Database Troubleshooting, Delete file using SSMS, Detach Database, Disaster Recovery, Recover Corrupt Database, Restore Database, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on December 2, 2011
You can enable the database for replication using below script.
use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go
If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.
use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go
18.520469
73.856621
Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: DB Restore, Enable/Disable Database for Replication, Jugal Shah, replication, SQL Script, SQLDBPool | Leave a Comment »
Posted by Jugal Shah on November 21, 2011
Problem: Often we would like to check how much size backup file will take on the disk? You can follow below simple steps to estimate the backup file size?
Solution:
Execute the SP_SPACEUSED procedure to get the database size statistics.
USE [sqldbpool]
GO
EXEC sp_spaceused @updateusage = ‘true’
Now you have to subtract the ununsed pages from reserved pages and divided by 1024 to get the output in MB.
In our scenario we have 2592 (KB) – 96 (KB) = 2496/1024 = 2.4374(MB) will be the backup size.
18.520469
73.856621
Posted in Database, SQL Server 2008, SQL Server 2008 R2 | Tagged: Backup, Backup Size, Database Size, DB Size, Jugal Shah, SQLDBPool | Leave a Comment »
Posted by Jugal Shah on June 14, 2011
Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.
Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.
You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.
SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO
To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.
You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608
Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')
18.520469
73.856621
Posted in Database, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Move System Database, Resource Database | Leave a Comment »
Posted by Jugal Shah on June 8, 2011
As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more…
You can use below different techniques to KILL all open sessions against the database.
Technique – I
Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.
DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'
DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''
SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId @@SPId
EXEC(@EXECSQL)
Technique – II
Take the database into Single User Mode and execute all the task needs to perform against the databse.
ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Once you are finish with all the required task make the database accessible to everyone.
ALTER DATABASE [Database Name] SET MULTI_USER
Technique – III
In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.
ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Database Name] SET ONLINE
Technique – IV
Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.
18.520469
73.856621
Posted in Database, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Kiil Connections, Processes, Sessions | 2 Comments »
