Difference Between Delete and Truncate

.Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

. Truncate table is functionally identical to delete statement with no "where clause" both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

. Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

. In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

. If you want to retain the identity counter, use delete statement instead.

. If you want to remove table definition and its data, use the drop table statement.

. You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

SQL Server System Databases

# Master

* Purpose – Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
* Prominent Functionality
o Per instance configurations
o Databases residing on the instance
o Files for each database
o Logins
o Linked\Remote servers
o Endpoints
* Additional Information
o The first database in the SQL Server startup process
o In SQL Server 2005, needs to reside in the same directory as the Resource database

# Resource

* Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
* Prominent Functionality
o System object definition
* Additional Information
o Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
o Prior to SQL Server 2005 the system related data was stored in the master database
o Read-only database that is not accessible via the SQL Server 2005 tool set
o The database ID for the Resource database is 32767
o The Resource database does not have an entry in master.sys.databases

# TempDB

* Purpose – Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
* Prominent Functionality
o Manage temporary objects listed in the purpose above
* Additional Information
o Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state

# Model

* Purpose – Template database for all user defined databases
* Prominent Functionality
o Objects
o Columns
o Users
* Additional Information
o User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
o The database configurations such as the recovery model for the Model database are applied to future user defined databases

# MSDB

* Purpose – Primary database to manage the SQL Server Agent configurations
* Prominent Functionality
o SQL Server Agent Jobs, Operators and Alerts
o DTS Package storage in SQL Server 7.0 and 2000
o SSIS Package storage in SQL Server 2005
* Additional Information
o Provides some of the configurations for the SQL Server Agent service
o For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database

ADO: Execute a sql script using vb.net

http://dotnet.itags.org/dotnet-ado/82398/

HELP LINKS

SQL Server backup utility using VB.Net and SQL-DMO

http://www.c-sharpcorner.com/UploadFile/shabdarghata/sql-server-backup-utility-sql-dmo02042008134007PM/sql-server-backup-utility-sql-dmo.aspx

http://www.c-sharpcorner.com/uploadfile/shivprasadk/1420/default.aspx?login=true&user=ravuripradeep

DDL Triggers in SQL Server 2005

DDL Triggers in SQL Server 2005



We all are familiar with DML (Data Manipulation Language) triggers which can be invoked after any DML operations (used for update, insert and delete commands). We can achieve auditing functionality using DML triggers but this does not help us in auditing if someone has altered the definition of the tables or dropped the tables etc.. We can achieve this by using DDL (Data Definition Language) triggers introduced in SQL Server 2005.

As the name suggests, DDL triggers do not get fired for any DML operations but get fired for CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS DDL statements. Some of the system stored procedures which perform CREATE like operations, also fire the DDL trigger. One exception is sp_rename. Sp_rename procedure does not invoke DDL trigger even though it performs the operation of renaming the object. Most of the time, DDL triggers are used to prevent modification in the database or to audit the DDL changes in the database.

DDL triggers can be created at the database level for a specific database or at the server/instance level. For creating trigger at the database level, we have to use the ‘ON DATABASE’ clause and for creating trigger at the server/instance level we have to use the ‘ON ALL SERVER’ clause. When created at the server level, it keeps track of DDL changes for any database on that specific instance.

Let us create a table first.

CREATE TABLE dbo.AUDIT_EVENT
(
AUDIT_EVENT_ID INT IDENTITY(1,1),
EVENT_TEXT VARCHAR(MAX),
ACTION_LOGIN VARCHAR(100),
ACTION_DATE DATETIME,
CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY(AUDIT_EVENT_ID)
)
GO

In the above table, we will record the information pertaining to any DDL operation. Let us create trigger to record the changes at database level.

CREATE TRIGGER AUDIT_TRIG
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
INSERT INTO dbo.Audit_Event(Event_Text, Action_Login, Action_date)
SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)’), original_login(), getDate()
GO

In the trigger shown above, we are using the EVENTDATA() function to obtain the actual text. We will cover more about EVENTDATA() function in future blog,. We have adopted the idea of using EVENTDATA() function from BOL example. Once trigger is in place, we can start testing it by creating, altering and dropping the table. Let us try to create table first. We will also alter it to add a new column and then we will drop it.

CREATE TABLE TEST(ID INT IDENTITY(1,1))
GO
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)
GO
DROP TABLE TEST
GO

For all of the above statements, once the statement is executed successfully, trigger will get fired. Let us check our audit table now.

SELECT Event_Text,Action_Login, Action_Date FROM AUDIT_EVENT
GO

Here is the result set:

Event_Text Action_login Action_Date

------------------------------------------- ------------- -----------------------

CREATE TABLE TEST(ID INT IDENTITY(1,1)) sa 2007-09-26 13:43:57.177

ALTER TABLE TEST ADD TEST_DESC VARCHAR(30) sa 2007-09-26 13:43:57.223

DROP TABLE TEST sa 2007-09-26 13:43:57.223

From the above result set, we know what command got executed, at what time and by whom. This prevents unwanted surprises during the development phase and in addition, we can keep track of database changes made within the application. Similar to the example shown above, we can also create trigger for CREATE_PROCEDURE, DROP_PROCEDURE event types to audit the procedure changes.

What are ACID Rules of transaction in a database? Atomicity, Consistency, Isolation, Durability

What are ACID Rules of transaction in a database? Atomicity, Consistency, Isolation, Durability

The ACID rules of transaction in any database assure the reliability of data in all transactions in the database.

Atomicity - this rule states that either the complete transaction takes place, or none. Even if a part of a transaction fails to work, the complete transaction will fail.

Consistency - this rule ensures that the database is stable, before and after the transaction, even if a transaction fails.

Isolation - this rule states that when a process is going on in a transaction, the data remains in isolation of other entities in the database.

Durability - this rule states that when a transaction completes successfully, it remains in stable state and is persisted in the database.

What is the difference between Host_Name() and ServerProperty('MachineName') Functions ?

The Host_Name() and ServerProperty('MachineName') Function will return the System Name(Machine Name).

But the difference is ...

Host_Name() will return the Client Machine name.

ServerProperty('MachineName') will return Server Machine name.

ie: CLIENT SQL Server tools only installed on "Computer1" (SQL Server Management Studio) - Connectivity tools only

SERVER SQL Server tools installed on "Computer2" (Databases, SQL Server Services, SQL Server Management Studio, Profiler,...Etc.,)

Now You are the "Client machine (Computer1)", You want to access the Database from the "Server Machine (Computer2)".


Client Machine (Computer1):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer1, Computer2

Server Machine (Computer2):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer2, Computer2

Back up a single table in SQL Server

Right-click database > tasks > generate sql scripts, then select the table and on the scripting options page, select advanced, choose "schema and data" in types of data to script.


I think this option is available only in SQL Server 2008. I dont think SQL Server 2005 or earlier versions have this feature.

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202



Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest


http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Isolation Levels in the Database Engine

The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:

*

Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
*

Read committed (Database Engine default level)
*

Repeatable read
*

Serializable (the highest level, where transactions are completely isolated from one another)

SELECT top 10 records

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10

Sybase

SET rowcount 10
SELECT column FROM table

Firebird

SELECT FIRST 10 column
FROM table

Different Types of VIEW

There are two different types of VIEW

* System View
o Information Schema View
o Catalog View
o Dynamic Management View(DMV)
* User Define View
o Simple View
o Complex View


In SQL Server all system view are divided into different schema. These are used for the security container of SQL Server database. We can categorized system view in following way,

* Information Schema View
* Catalog View
* Dynamic Management View (DMV)

Now all above category are itself a huge topic, So I will not going to Details of It. Lets have a look into the over view of those view type

Information View

These are the one of the most important system grouped view. There are twenty different schema views in this group. This are used for displaying most physical information of a database, such as table, columns. Naming Conation of this type of views are INFORMATION_SCHEMA.[View Name] . From the System View Image we can get the few name of Information Schema View.

Lets see it with one Example,

I have create on Database named, ViewDemo. It having on table called EmpInfo and below diagram showing you the design of the table,
Now, if we want to know the details information columns of table Empinfo using View we have to run the following query,



SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='EmpInfo'



Similarly we can use all other Schema View to read Database information.

Catalog View

This type of view are introduced in SQL Server 2005. catalog view are categorized in different group also. These are used to show the database self describing information.

As for example,
Collapse

select * from sys.tables



Dynamic Management View

This is newly introduced in SQL Server 2005.This Views gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMV

1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database

As for example if we want to check the all SQL Server connection, we have to use following query,
Collapse

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

http://www.codeproject.com/KB/database/View.aspx

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/viewsinsqlserver200510132009103348AM/viewsinsqlserver2005.aspx

FIND nth RECORD USING CURSOR

declare sc cursor static for select * from system1.dbo.sys_params

declare @n int
open sc
set @n=25
while (@n<=29)
begin
fetch absolute @n from sc

set @n=@n +1
end
close sc
deallocate sc

Find Nth maximum,minimum value in SQL Server

By Guest Authors on 11 April 2004 | 14 Comments | Tags: SELECT

This aritlce is written by Hariharan Velayuthan. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."

[Note: This article assumes that the reader is familiar with the T-SQL, joins and queries]

I have taken utmost effort to make this article easy to understand, but incase you are not clear with the concept, please raise up your concern and I’ll be more than happy to attend your doubts. All the examples discussed in this article uses Employee table. If you do not have this table, please use the following script to create it.

Use Pubs
Go

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go

Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go

A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)

How does this query work?

The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:

Select * from Employee where salary = (7500)

Returns:

Eid Name Salary
5 steven 7500



find nth maximum salary

Select * From Employee E1 Where
(n-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

find nth minimum salary

Select * From Employee E1 Where
((select Count(Distinct(Salary)) from Employee) -n) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

delete duplicate records using Identitiy

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)

Duplicate delete all rows in table

with CTE(COL1,COL2,DUPLICATECOUNT
AS(
select COL1,COL2,ROW_NUMBER() OVER(partition by COL1,COL2 order by COl1)as DUPLICATECOUNT from TABELNAME
)
DELETE from CTE WHERE DUPLICATECOUNT >1

Can you insert into views that are based on joins?

it is possible, provided that your insert statement has a column list that makes it clear there's only one table involved.

You cannot insert into multiple tables with a single insert on a view.

How To Found Current Identity No

this is any tabel we get currnt identity
synatax: select ident_current('TABELNAME')
select ident_current('system1k.dbo.menu')


and SELECT @@IDENTITY;
this is used only when ever user insert rows using query then uwrite blow which is the identity then u use this statement

ex:INSERT INTO menu values( 'ggdg','dgytdytd','true','true',6,'tyu')
SELECT @@IDENTITY;

then display the current IDENTITY value

USE TempDB
GO
CREATE TABLE tst
( a int identity(1,1), s varchar(10))
GO
USE TempDB

CREATE TABLE tst2
( a int identity(1000,1), s varchar(10))
USE TempDB
GO
CREATE TRIGGER dbo.trgTst
ON tst
AFTER INSERT
AS INSERT tst2 SELECT inserted.s FROM inserted
GO
INSERT tst VALUES('a')
SELECT
@@IDENTITY AS [@@IDENTITY],
SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
GO
DROP TABLE tst2
DROP TABLE tst