Tuesday, May 13, 2008

Self Join - Sql server

Self-joins : A self-join is a query in which a table is joined  to itself.  Self-joins are used to compare values in a column with other values in the same column in the same table

Example:  In this example i am tring to insert data into Employee table with emp id, manager id. Manager id referes same tables emplid. Finally by using innerjoin i wanted to get data with manager name rather than manager id.

1. Create table
 CREATE TABLE dbo.Employee

(

empId int NOT NULL IDENTITY (1, 1),

empName nvarchar(50) NOT NULL,

managerId int NULL

) ON [PRIMARY]

2. Insert Data into Table
1,Madhu,NULL
2,Bala,1
3,Krishna,1
4,Ram,1

3. SQL Query - Self Join

select a.empId, a.empName as Employee , b.empName as ManagerName

from Employee a, Employee b where b.empId=a.managerId


4. Output
2,Bala,Madhu
3,Krishna,Madhu
4,Ram,Madhu

Posted by at 06:11:33 | Permalink | No Comments »

Thursday, April 10, 2008

Maintain SQL Server 2005 DB changes in log table

Maintaining SQL Server DDL command changes in log file is most important and now it becomes very easy in SQL Server 2005 by using EVENTDATA () function.
 
EVENTDATA() is a function is provided by SQL Server 2005, It provides Information about an event that fires a DDL trigger is captured by using the EVENTDATA function. This function returns an xml value. The XML schema includes information about the following:
 ·         The time of the event.
·         The System Process ID (SPID) of the connection when the trigger executed.
·         The type of event that fired the trigger.

Steps for implementation of storing DDL log changes in Table

1.      Create table to store DDL Transactions


   CREATE TABLE [dbo].[DDLLog](
   [logId] [int] IDENTITY(1,1) NOT NULL,
   [logData] [xml] NOT NULL,
   CONSTRAINT [PK_DDLLog] PRIMARY KEY CLUSTERED
    ([logId] ASC)) ON [PRIMARY]

2.      Create Trigger to fetch DDL commands info and store in DDLLog table  
   CREATE TRIGGER [Trig_DDLLog]
   ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
    AS
   INSERT INTO dbo.DDLLog(logData)
   SELECT EVENTDATA()
   GO
   ENABLE TRIGGER [Trig_DDLLog] ON DATABASE

3.      Create a test table to check 

   create table testTable (id int, description varchar(100))

4.      Watch DDLLog table for log entry …it is available in XML format like below.

<EVENT_INSTANCE>
<EventType>CREATE_TABLEEventType>
      <PostTime>2008-04-10T11:48:08.463PostTime>
      <SPID>53SPID>
      <ServerName>BSIAP021ServerName>
      <LoginName>saLoginName>
      <UserName>dboUserName>
      <DatabaseName>AdventureWorksDatabaseName>
      <SchemaName>dboSchemaName>
      <ObjectName>testTableObjectName>
      <ObjectType>TABLEObjectType>
      <TSQLCommand>
            <SetOptions ANSI_NULLS=“ON” ANSI_NULL_DEFAULT=“ON”
            ANSI_PADDING=“ON” QUOTED_IDENTIFIER=“ON” ENCRYPTED=“FALSE”
            />
<CommandText>create table testTable (id int, description varchar(100))
            CommandText>
      TSQLCommand>
EVENT_INSTANCE>

References: http://technet.microsoft.com/en-us/library/ms187909.aspx

Now it is so simple to maintain SQL Server DDL changes log 

 

*** END ***

Posted by at 05:04:38 | Permalink | No Comments »

Wednesday, April 9, 2008

Introducing SQL Server 2005 “Schema” and usage

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.


SQL Server 2005 has given us the ability to create schemas independent of the user, and by this we achieve separation of schemas and users. This gives us some considerable advantages:

  1. The first benefit is the ability to subdivide your database into logic of interest, rather as the Erwin modeling tool lets you create subject areas.
  2. You can provide better security at Schema level.
  3. Dropping database users is greatly simplified; dropping a database user does not require the renaming of objects contained by that user’s schema. Thus it is no longer necessary to revise and test applications that refer explicitly to schema-contained objects after dropping the user that created them
  4. Multiple users can share a single default schema for uniform name resolution.
  5. Shared default schemas allow developers to store shared objects in a schema created specifically for a specific application, rather than in the DBO schema
  6. Permissions on schemas and schema-contained objects can be managed with a higher degree of granularity than in earlier releases.
  7. Fully qualified name of object in SQL Server 2000 and earlier looks like
     [Server].[DatabaseName].[OwnerName].[ObjectName]
  8. SQL Server 2005’s fully qualified name for object looks like below
     [Server].[DatabaseName].[Schema].[ObjectName]
  9. You can able to transfer Schema ownership

Creating schema
CREATE SCHEMA Marketing
GO

Adding table object

CREATE TABLE Marketing.Leads

(
    LeadId INT,
    LeadDescription NVARCHAR(50)
)
GO


Accessing table object

SELECT * FROM Marketing.Leads
GO

After you have created schemas, your database looks like below



*** END ***

Posted by at 16:00:00 | Permalink | No Comments »

Wednesday, January 9, 2008

Maintain your DDL commands log & Restrict DDL commands in SQL Server 2005

Maintain your DDL commands log :

1. Create Audit Schema( which ever schema name you like it)
CREATE SCHEMA Audit

GO

2. Create table to hold DDL command log data
CREATE TABLE [Audit].[DDLLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[logData] [xml] NOT NULL,
CONSTRAINT [PK_DDLLog] PRIMARY KEY CLUSTERED
([id] ASC)) ON [PRIMARY]
GO

3. Create trigger to store DDL command in table when any DDL command executed in DB
CREATE TRIGGER [Trig_DDLLog]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
 AS
INSERT INTO [Audit].[DDLLog](logData)
SELECT EVENTDATA()
GO

4. Enable Trigger
ENABLE TRIGGER [Trig_DDLLog] ON DATABASE

Thats all. Now on if you use any DDL command on DB, it maintains log in Audit.DDLLog table.
____________________________________________________________

Restrict DDL commands :

Below Trigger restrict user from Droping tables and Altering tables. If you wanted to do that you must disable trigger first before you proceed to Alter or Drop table.

CREATE TRIGGER Trig_RestrictDropAlterTable

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT ‘You must disable Trigger “Trig_RestrictDropAlterTable” to drop or alter tables!’

ROLLBACK


To Disable Trigger
DISABLE TRIGGER [Trig_RestrictDropAlterTable ] ON DATABASE

To provide next level of security from Droping and Altering tables, you can create schema, and give permissions to only one main user on newly created schema. Create this trigger under that schema. So others don’t have permission to disable trigger. So others can’t be able to modify DB structures. Only main users will disable trigger and do nessasary changes in structures.

*** END ***
Posted by at 16:00:00 | Permalink | No Comments »