Monday, November 30, 2009

Normalization and DeNormalization,fulltext definition functions,find index, non index tables in sql server


Normalization


In Relational database design,the process of organizing data to minimize redundancy is called Normalization.

DeNormalization

It is the process of optimize the performance of a database by adding redundant data.

Different Normalization Forms

1 NF

Eliminate Repeating groups

2NF

Eliminate Redundant Data

3NF

Eliminate Columns which are not dependent on key column

BCNF

Eliminate non-trivial dependencies in candidate key attribute then separate it

4NF

Isolate independent multiple relationship

5NF

Isolate semantically related multiple relationship(Separate Many-Many Relationship)

ONF

A Model is limited as express object role model

DKNF

A model free from all modification anomalies


Trick to find all index tables in a database

SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=1


Trick to find all non-index tables in a database


SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=0


Fulltext definition functions with example


SELECT EMP_NAME FROM EMPLOYEE , CONTAINSTABLE(EMPLOYEE,EMP_NAME,'CHANDR') AS TabName


SELECT EMP_NAME FROM EMPLOYEE WHERE FREETEXT(EMP_NAME,'CHAN')

SELECT EMP_NAME FROM EMPLOYEE WHERE CONTAINS(EMP_NAME,'CHAN')

SELECT RANK()OVER(ORDER BY Description),Description FROM EMPLOYEE

How to find current version of the sql server

SELECT @@VERSION

Tuesday, November 24, 2009

Remove duplicate records from table in sql server


Query shows how to select duplicate records with SL No.


SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE



Query for remove duplicate records from table


WITH REMDUPREC
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE
)

DELETE FROM REMDUPREC WHERE SLNO>1



delete from MyTable
where uniqueField not in
(select min(uniqueField) from MyTable T2
where T2.dupField=MyTable.dupField)

Wednesday, November 11, 2009

OPENQUERY AND OPENROWSET IN SQL SERVER


OPENQUERY: Used to execute the query passed on the linked server.

Syntax: OPENQUERY (Linked_server_identifier, query).

It can also be refernced from a FROM clause of selecte query.

e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);


OPENROWSET: Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

Sunday, November 8, 2009

Advantages and disadvantages of views in sql server


Advantages of view


1. Data Security

2. Simplicity


3. No Space


4. Hide data and columns from base tables


5. You can easily use a view to create new information out of existing data,
without changing the (legacy) database structure

6. Complex queries can be stored in the from of view


Disadvantages of view

1. Querying from view takes more time than directly querying from the table

2. I
t will have to do the query a new, every time it is opened

3. when table is not there view will not work or view become inactive

4. View can be create only if there is SELECT Permission on its base table

5. views can not derived the data from temp tables

6.Query defining view can not include ORDER BY,COMPUTE,INTO Keyword

7.You can not define full text index definitions on views

Thursday, October 29, 2009

INSERT Record into TABLE with DEFAULT VALUES and other SQL tricks..!



If you want INSERT record INTO TABLE with DEFAULT VALUES without specifying all the values, then here is the trick...


SYNTAX:

INSERT INTO TABLENAME DEFAULT VALUES

EX:

INSERT INTO EMPLOYEE DEFAULT VALUES

-It will insert all default values specified for the columns in a table.

OTHER EX:

INSERT INTO TABLENAME SELECT 'VALUE1','VALUE2',...AND SO ON

-This will insert values into table without having specify all column names of table,but here in select statement you have specify all values or default values.


Solution to insert or update record using views,when view holding two joined tables.

Ex:
-CREATE VIEW

CREATE VIEW EUView
AS
SELECT coalesce(EMPLOYEE.ID,[USER].UID) TABLENAMEID,EMPLOYEE.NAME,[USER].UNAME
FROM EMPLOYEE FULL OUTER JOIN [USER] ON EMPLOYEE.ID=[USER].UID


-CREATE A TRIGGER ON VIEW

CREATE TRIGGER EUTRIGGER
ON EUVIEW
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO EMPLOYEE(ID,NAME)SELECT TABLENAMEID,NAME FROM INSERTED
INSERT INTO [USER](UID,UNAME)SELECT TABLENAMEID,UNAME FROM INSERTED

END


-FOLLOWING SHOWS INSERT A RECORD

INSERT INTO EUVIEW SELECT 3,'Test2','Chandu'



TRICK TO FIND DUPLICATE RECORD IN A TABLE


SELECT EMP_NAME,COUNT(EMP_NAME) FROM EMPLOYEE
GROUP BY EMP_NAME
HAVING COUNT(EMP_NAME)>1


TRIGGER FIRING ORDER IN SQL Server

Following shows creation of triggers and setting order of trigger firing.

set nocount on
go
create table dbo.customer (customerid int identity primary key)
go

Create trigger 1

create trigger dbo.tr1 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 1'
go

Create trigger 2

create trigger dbo.tr2 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 2'
go

Sample insert statement

insert into dbo.customer default values
go


exec sp_settriggerorder @triggername = 'tr1',
@order = 'first',
@stmttype = 'insert',
@namespace = null

exec sp_settriggerorder @triggername = 'tr2',
@order = 'last',
@stmttype = 'insert',
@namespace = null
go





TRICK TO FIND nth MAX SALARY

1.

SELECT top 1 * FROM (SELECT TOP 3 * FROM SALARY ORDER BY SAL_MODE DESC)A ORDER BY SAL_MODE

2.

SELECT SAL_ID ,SAL_MODE FROM (SELECT row_number() over(order by sal_mode DESC) 'RN', SALARY.SAL_MODE,SALARY.SAL_ID FROM SALARY )A
WHERE A.RN=1 ORDER BY SAL_MODE



Wednesday, October 28, 2009

Languages used in SQL Server

The Data Definition Language (DDL) includes,

CREATE TABLE - creates new database table

ALTER TABLE - alters or changes the database table

DROP TABLE - deletes the database table

CREATE INDEX - creates an index or used as a search key

DROP INDEX - deletes an index



The Data Manipulation Language (DML) includes,

SELECT - extracts data from the database

UPDATE - updates data in the database

DELETE - deletes data from the database

INSERT INTO - inserts new data into the database



The Data Control Language (DCL) includes,

GRANT – gives access privileges to users for database

REVOKE – withdraws access privileges to users for database



The Transaction Control (TCL) includes,

COMMIT – saves the work done

ROLLBACK - restore the database to original since the last COMMIT

Locking in Microsoft SQL Server

Introduction

In this article, I want to tell you about SQL Server 7.0/2000 Transaction Isolation Levels, what kinds of Transaction Isolation Levels exist, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about deadlocks, and about how you can view locks by using the sp_lock stored procedure.

Transaction Isolation Levels

There are four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE


  • Microsoft SQL Server supports all of these Transaction Isolation Levels and can separate REPEATABLE READ and SERIALIZABLE.

    Let me to describe each isolation level.

    READ UNCOMMITTED

    When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

    READ COMMITTED

    This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.

    REPEATABLE READ

    When it's used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

    This is the definition of nonrepeatable read from SQL Server Books Online:

    nonrepeatable read
    When a transaction reads the same row more than one time, and between the
    two (or more) reads, a separate transaction modifies that row. Because the
    row was modified between reads within the same transaction, each read
    produces different values, which introduces inconsistency.

    SERIALIZABLE

    Most restrictive isolation level. When it's used, the phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction will be completed.

    This is the definition of phantom from SQL Server Books Online:

    phantom
    Phantom behavior occurs when a transaction attempts to select a row that
    does not exist and a second transaction inserts the row before the first
    transaction finishes. If the row is inserted, the row appears as a phantom
    to the first transaction, inconsistently appearing and disappearing.
    You can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement.
    This is the syntax from SQL Server Books Online:

    SET TRANSACTION ISOLATION LEVEL
    {
    READ COMMITTED
    | READ UNCOMMITTED
    | REPEATABLE READ
    | SERIALIZABLE
    }
    You can use the DBCC USEROPTIONS statement to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
    DBCC USEROPTIONS
    GO

    Lock types

    There are three main types of locks that SQL Server 7.0/2000 uses:

  • Shared locks
  • Update locks
  • Exclusive locks


  • Shared locks are used for operations that do not change or update data, such as a SELECT statement.

    Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.

    Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

    Shared locks are compatible with other Shared locks or Update locks.

    Update locks are compatible with Shared locks only.

    Exclusive locks are not compatible with other lock types.

    Let me to describe it on the real example. There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

    Process1 : SELECT
    Process2 : SELECT
    Process3 : UPDATE
    Process4 : SELECT

    Process1 sets the Shared lock on the page, because there are no another locks on this page.
    Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
    Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.
    Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.
    After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.

    Locking optimizer hints

    SQL Server 7.0/2000 supports the following Locking optimizer hints:

  • NOLOCK
  • HOLDLOCK
  • UPDLOCK
  • TABLOCK
  • PAGLOCK
  • TABLOCKX
  • READCOMMITTED
  • READUNCOMMITTED
  • REPEATABLEREAD
  • SERIALIZABLE
  • READPAST
  • ROWLOCK


  • NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

    HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

    UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

    TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

    PAGLOCK is used by default. Directs SQL Server to use shared page locks.

    TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

    READCOMMITTED
    Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

    READUNCOMMITTED
    Equivalent to NOLOCK.

    REPEATABLEREAD
    Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

    SERIALIZABLE
    Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

    READPAST
    Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
    You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

    ROWLOCK
    Use row-level locks rather than use the coarser-grained page- and table-level locks.

    You can specify one of these locking options in a SELECT statement.
    This is the example:

    SELECT au_fname FROM pubs..authors (holdlock)

    Deadlocks

    Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.

    You can decide which connection will be the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.

    So, in a multiuser situation, your application should check the error 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.

    Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

    View locks (sp_lock)

    Sometimes you need a reference to information about locks. Microsoft recommends using the sp_lock system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database, about locked table ID, about locked page and about type of locking (locktype column).

    This is the example of using the sp_lock system stored procedure:

    spid   locktype                            table_id    page        dbname
    ------ ----------------------------------- ----------- ----------- ---------------
    11 Sh_intent 688005482 0 master
    11 Ex_extent 0 336 tempdb
    The information, returned by sp_lock system stored procedure needs in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.

    Check the link below if you need to get user name, host name, database name, index name object name and object owner instead of their ID numbers:

    Tuesday, October 27, 2009

    Arrays in VB.NET

    Arrays

    Arrays are programming constructs that store data and allow us to access them by numeric index or subscript. Arrays helps us create shorter and simpler code in many situations. Arrays in Visual Basic .NET inherit from the Array class in the System namespace. All arrays in VB as zero based, meaning, the index of the first element is zero and they are numbered sequentially. You must specify the number of array elements by indicating the upper bound of the array. The upper bound is the numder that specifies the index of the last element of the array. Arrays are declared using Dim, ReDim, Static, Private, Public and Protected keywords. An array can have one dimension (liinear arrays) or more than one (multidimensional arrays). The dimensionality of an array refers to the number of subscripts used to identify an individual element. In Visual Basic we can specify up to 32 dimensions. Arrays do not have fixed size in Visual Basic.

    The following code demonstrates arrays.Imports System.Console
    Module Module1

    Sub Main()
    Dim sport(5) As String
    'declaring an array
    sport(0) = "Soccer"
    sport(1) = "Cricket"
    sport(2) = "Rugby"
    sport(3) = "Aussie Rules"
    sport(4) = "BasketBall"
    sport(5) = "Hockey"
    'storing values in the array
    WriteLine("Name of the Sport in the third location" & " " & sport(2))
    'displaying value from array
    End Sub

    End Module



    Understanding the Code

    The above code declared a sport array of type string like this: Dim sport(5) as String. This sport array has 6 elements starting from sport(0) to sport(5). The first element of an array is always referred by zero index. The image below displays output from above code.


    You can also declare an array without specifying the number of elements on one line, you must provide values for each element when initializing the array. The following lines demonstrate that:

    Dim Test() as Integer
    'declaring a Test array
    Test=New Integer(){1,3,5,7,9,}

    Reinitializing Arrays

    We can change the size of an array after creating them. The ReDim statement assigns a completely new array object to the specified array variable. You use ReDim statement to change the number of elements in an array. The following lines of code demonstrate that. This code reinitializes the Test array declared above.

    Dim Test(10) as Integer
    ReDim Test(25) as Integer
    'Reinitializing the array

    When using the Redim statement all the data contained in the array is lost. If you want to preserve existing data when reinitializing an array then you should use the Preserve keyword which looks like this:

    Dim Test() as Integer={1,3,5}
    'declares an array an initializes it with three members
    ReDim Preserve Test(25)
    'resizes the array and retains the the data in elements 0 to 2

    Multidimensional Arrays

    All arrays which were mentioned above are one dimensional or linear arrays. There are two kinds of multidimensional arrays supported by the .NET framework: Rectangular arrays and Jagged arrays.

    Rectangular arrays

    Rectangular arrays are arrays in which each member of each dimension is extended in each other dimension by the same length. We declare a rectangular array by specifying additional dimensions at declaration. The following lines of code demonstrate the declaration of a multidimensional array.

    Dim rectArray(4, 2) As Integer
    'declares an array of 5 by 3 members which is a 15 member array
    Dim rectArray(,) As Integer = {{1, 2, 3}, {12, 13, 14}, {11, 10, 9}}
    'setting initial values

    Jagged Arrays

    Another type of multidimensional array, Jagged Array, is an array of arrays in which the length of each array can differ. Example where this array can be used is to create a table in which the number of columns differ in each row. Say, if row1 has 3 columns, row2 has 3 columns then row3 can have 4 columns, row4 can have 5 columns and so on. The following code demonstrates jagged arrays.

    Dim colors(2)() as String
    'declaring an array of 3 arrays
    colors(0)=New String(){"Red","blue","Green"}
    initializing the first array to 3 members and setting values
    colors(1)=New String(){"Yellow","Purple","Green","Violet"}
    initializing the second array to 4 members and setting values
    colors(2)=New String(){"Red","Black","White","Grey","Aqua"}
    initializing the third array to 5 members and setting values

    Friday, October 23, 2009

    Delete duplicate records and delete all records from database


    WITH CTE (SecondCol,ThirdCol, DuplicateCount)

    AS
    (
    SELECT SecondCol,ThirdCol,
    ROW_NUMBER()
    OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
    AS DuplicateCount
    FROM testtable
    )
    DELETE
    FROM CTE
    WHERE DuplicateCount > 1
    GO



    How to Delete all the data from the Database with simple Query- Sql Server


    use practice
    use practice
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO
    EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
    DELETE FROM ?
    else
    TRUNCATE TABLE ?
    '
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO



    SELECT ALTERNATIVE ROWS

    WITH shital
    AS
    (
    SELECT ROW_NUMBER()OVER(ORDER BY IDA)RN,* FROM EMPLOYEE
    )
    SELECT *FROM shital WHERE RN%2=0

    Triggers in SQL Server

    Triggers

    Creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.


    DML Triggers

    DML triggers are frequently used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. However, DRI does not provide cross-database referential integrity. Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables. To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and CREATE TABLE. If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.


    DDL Triggers

    In your workplace does more than one person has access to databases that are vital to the smooth operation of your organisation? Do these users have rights to alter the database structure by using Data Definition Language (DDL) statements such as CREATE TABLE..., DROP TABLE etc?

    If the answer to the above questions are yes then are mechanisms in place to help monitor/audit who is making what changes to the database? Who deleted that table? Who changed that columns data type? What code was in the previous version of that procedure that now isn't working? This type of auditing is being asked for more and more as organisations become more and more dependent on their databases for helping in all aspects of day to day work.

    DDL Triggers (introduced in SQL Server 2005) provide you with the capability of auditing the creation, deletion and modification of database objects as well as other capabilities such as checking that DDL code conforms to your business rules before executing it.

    How Triggers work

    A Trigger is a block of T-SQL code that is executed or 'triggered' as a result of another statement that is sent to the database. Before SQL Server 2005 a trigger could be 'triggered' by either INSERT, UPDATE or DELETE (Data Manipulation Language - DML) statements. SQL Server 2005 introduced DML Triggers that can be set to fire on your chosen DDL events such as CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_DATABASE, CREATE_LOGIN etc.

    DDL Triggers can be set with either a Server scope or database scope. Triggers created with Server scope must target server DDL events such as CREATE_DATABASE or CREATE_LOGIN whilst triggers created with database scope must target database level events such as CREATE_TABLE or ALTER_PROC. See the full list of SQL Server DDL Trigger Events (including their scope).

    DDL triggers can only fire after the DDL statement has occurred. This is different from DML triggers which can fire before the triggering statement.

    Syntax of a DDL trigger

    CREATE TRIGGER [TriggerName]
    ON [Scope (Server|Database)]
    FOR [EventName...],
    AS
    -- code for your trigger response here

    The EventData function

    If you want to audit changes to your database schemas you need to be able to access the triggering events in your DDL trigger so that you can record what changes are being made. To access the triggering event we can use the EventData function in our DDL trigger. The EventData function returns an xml value.

    The EventData xml value includes the triggering SQL statement, the event time, the type of event and depending on what type of event was called, extra information such as the database name. The following example shows how EventData can be used to record all statements that changed the table or stored proc schemas into a table called DDLAudit.

    CREATE TRIGGER AuditProcChanges
    ON DATABASE
    FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
    AS

    DECLARE @ed XML
    SET @ed = EVENTDATA()

    INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login)
    VALUES
    (
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', nvarchar(100)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
    )

    The EventData function returns an xml value and is assigned to a variable called @ed which is of an xml data type. The xquery function value(Xquery, sqltype) returns the specified values from the xml variable. For more information on EventData see MSDN.

    This DDLAudit table could reside in the individual database or you could create a seperate ApplicationAudit database and use a 3 part name to record the audit in this ApplicationAudit database, i.e. INSERT INTO ApplicationAudit.dbo.DDLAudit ....

    Covering all databases and events

    If you wanted to audit the DDL events for all your databases you would need to create this trigger in each database. The above trigger only monitors DDL events that affect Tables and Stored Procedures. You can use the handy event name of DDL_DATABASE_LEVEL_EVENTS to make sure your trigger covers all DDL events that have database scope as follows:

    CREATE TRIGGER AuditDBScopeDDLChanges
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    -- trigger code here...

    Preventing DDL actions using triggers

    Sometimes you may want to prevent the alteration of a schema, because the triggering statement and trigger are joined in one transaction we can call ROLLBACK in our trigger to rollback the DDL statement that caused the trigger to fire:

    CREATE TRIGGER PreventDropTable
    ON DATABASE
    FOR DROP_TABLE
    AS
    PRINT 'Tables cannot be dropped'
    ROLLBACK

    What happens if you then want to drop a table in the database with the above trigger? You can disable the trigger, drop the table and then re-enable the trigger:

    DISABLE TRIGGER PreventDropTable
    ON DATABASE
    GO
    DROP TABLE MyTable
    GO
    ENABLE TRIGGER PreventDropTable
    ON DATABASE
    GO

    You could also if you wished extend use this method of schema change prevention for server scope events to prevent the dropping of databases:

    CREATE TRIGGER PreventDropDatabaseServerWide
    ON ALL SERVER
    FOR DROP_DATABASE
    AS
    PRINT 'Cannot drop tables, DDL Trigger will rollback'
    ROLLBACK

    Finding DDL triggers using system tables

    To view the DDL triggers in your databases of database scope you can query the sys.triggers table. To view the DDL triggers with server scope you need to query the sys.server_triggers table in the master database.

    Conclusion

    When designing your DDL trigger you will probably be performing one of more of the following actions:

    • Recording changes made to the database schema
    • Stopping certain types of changes being made to the database schema
    • Fire another action in the database in response to the schema change

    We have seen how DDL triggers can be used to a) audit and b) control schema changes using the EventData function and the ROLLBACK command respectively.

    Logon Triggers

    Logon triggers fire in response to the LOGON event. This event is raised when a user sessions is being established. For more information.

    Multiple Triggers

    SQL Server allows for multiple triggers to be created for each DML, DDL, or LOGON event. For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created. In earlier versions of SQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.

    Recursive Triggers

    SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

    Nested Triggers

    Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the RECURSIVE_TRIGGERS setting set by using ALTER DATABASE.

    Tuesday, October 20, 2009

    Drop all objects in a database on SQL Server

    Solution for Drop all objects from database at once!

    Following procedure run on any database (SQL Server)

    \***********************************************/


    CREATE PROCEDURE dbo.DropAllDBObjects
    @Action VARCHAR(20)=NULL
    As
    BEGIN

    DECLARE @Name VARCHAR(500)

    IF @Action='Table'
    BEGIN
    DECLARE DROPALL CURSOR
    FOR SELECT [NAME] FROM sys.TABLES

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP TABLE ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    END

    ELSE IF @Action='PROCEDURE'
    BEGIN
    DECLARE DROPALL CURSOR
    FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    IF @Name <> 'DropAllDBObjects'
    EXEC('DROP PROCEDURE ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    END
    ELSE IF @Action='Function'
    BEGIN
    DECLARE DROPALL CURSOR
    FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP FUNCTION ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    END
    ELSE IF @Action='View'
    BEGIN
    DECLARE DROPALL CURSOR
    FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP VIEW ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    END
    IF @Action is null
    BEGIN
    --Table
    DECLARE DROPALL CURSOR
    FOR SELECT [NAME] FROM sys.TABLES

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP TABLE ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    --Views
    DECLARE DROPALL CURSOR
    FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP VIEW ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    --Functions
    DECLARE DROPALL CURSOR
    FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC('DROP FUNCTION ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL

    --Procedure
    DECLARE DROPALL CURSOR
    FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

    OPEN DROPALL
    FETCH NEXT FROM DROPALL INTO @Name
    WHILE @@FETCH_STATUS=0
    BEGIN
    IF @Name <> 'DropAllDBObjects'
    EXEC('DROP PROCEDURE ' + @Name)
    FETCH NEXT FROM DROPALL INTO @Name
    END
    CLOSE DROPALL
    DEALLOCATE DROPALL
    END
    END
    GO

    \***********************************************/

    To execute Procedure:

    a)To Drop all objects from Database,execute following

    EXEC DropAllDBObjects

    b)To Drop all objects from Database,based on different object at time.

    EXEC DropAllDBObjects 'table'

    or

    EXEC DropAllDBObjects 'View'

    or

    EXEC DropAllDBObjects 'Function'

    or

    EXEC DropAllDBObjects 'Procedure'


    To View all objects,use following

    SELECT [NAME] FROM sys.TABLES
    SELECT [NAME] FROM sys.objects WHERE TYPE='P'
    SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

    After All work done,you can drop following procedure

    DROP PROC DropAllDBObjects

    Thursday, October 15, 2009

    Differences between varchar and nvarchar in SQL Server

    The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

    VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

    The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

    The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

    Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

    Wednesday, October 14, 2009

    Windows Vista Error Fix Desktop Icons And Taskbar Fails To Load At Startup

    There are really a lot of reasons why all of your icons and your Windows Vista desktop fails, all of a sudden, to load at startup. Some times the taskbar also refuses to load at Windows Vista startup.Anyway, most of the times this issue is due to a little key in the registry called explorer.exe which refuses to load or it is just missing or it doesn’t exist at all.This Issue in Windows Vista is easily fixed by using one of these 4 fixes:

    The following method i have checked out and its worked for my laptop inspiron 1545 windows vista home edition.

    Method1:

    Copy and paste the following piece of code in your notepad:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
    “Userinit”=”C:\\WINDOWS\\SYSTEM32\\userinit.exe,”

    Now save the file as Userinit.reg. Double-click on it to merge it in the Windows Vista registry. or Change registry key value and close registry editor and restart.

    Method 2:

    The key Explorer.exe in your registry is not correct.To fix this:

    • Click on Windows Vista Start.
    • In the start menu Search Box type “regedit” and hit Enter.
    • In Windows registry, locate the following key: [HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\Windows NT\CurrentVersion\ Image File Execution Options]
    • Look for Explorer.exe.
    • Delete it.
    • or Change registry key value to Explorer.exe to the above location and close registry editor then restart.

    Method 3:

    Restore Explorer.exe which is missing in your Windows Vista registry.To do this:

    • Copy this piece of code in your notepad editor.

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
    “Shell”=”explorer.exe”

    • Save the file as Logon.reg.
    • Double-click on it to merge it in the Windows Vista registry(Sometimes UAC prompt appears and click “Continue”).
    • or Change registry key value to Explorer.exe to the above location and close registry editor then restart.

    Method 4:

    If explorer.exe is not in C:\WINDOWS\ then insert your Windows CD and, using the ‘Task Manager’ (File -> New Task), run the ‘System File Checker’ utility with this command:

    sfc /scannow

    If the ‘System File Checker’ didn’t replace it, boot to the Recovery Console with the Windows CD and copy explorer.exe manuallyFor this:

    • Insert your Windows Vista DVD.
    • When the setup screen window appears, choose the Repair or Recover option by pressing “R”.
    • In the Command Prompt, type the following command: expand G:\i386 /F:EXPLORER.EX_ C: \WINDOWS\explorer.exe /y (don’t forget that G: is the letter of your DVD drive, while C: is the partition where Windows Vista is installed, change them if they are different in your PC)
    original link from:

    http://www.tomstricks.com/windows-vista-error-fixdesktop-icons-and-taskbar-fails-to-load-at-startup/

    Friday, August 21, 2009

    Identify last statement run for a specific SQL Server session

    SELECT DEST.TEXT
    FROM
    sys.[dm_exec_connections] SDEC
    CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
    WHERE SDEC.[most_recent_session_id] = (SELECT @@SPID)

    Monday, July 27, 2009

    RESEED All Identity Column in all tables of a Database

    IF EXISTS(SELECT *FROM SYS.PROCEDURES WHERE NAME='SP_RESEEDIdentityColumn')
    BEGIN
    DROP PROC SP_RESEEDIdentityColumn
    END
    GO
    CREATE PROC SP_RESEEDIdentityColumn
    @RESEEDVALUE INT=0
    AS
    BEGIN
    DECLARE CURRESEED CURSOR
    FOR
    SELECT NAME FROM SYS.TABLES

    DECLARE @TABNAME VARCHAR(225),@SQL NVARCHAR(225)

    OPEN CURRESEED
    FETCH NEXT FROM CURRESEED INTO @TABNAME
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @SQL ='DBCC CHECKIDENT('+@TABNAME+',RESEED,'+@RESEEDVALUE+')'
    EXEC(@SQL)
    FETCH NEXT FROM CURRESEED INTO @TABNAME
    END
    END

    CLOSE CURRESEED
    DEALLOCATE CURRESEED
    GO


    --Here if you are not specified param value it reseed from 0
    EXECUTE SP_RESEEDIdentityColumn

    --Here if you are not specified param value it reseed from 1
    EXECUTE SP_RESEEDIdentityColumn 1

    Delete the data from all tables In Database-Simple Sql Server Query!

    USE TEST
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO
    EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
    DELETE FROM ?
    else
    TRUNCATE TABLE ?
    '
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO

    Tuesday, June 30, 2009

    Different String Function in SQL Server 2005

    ASCII function in SQLServer

    Returns the ASCII code value of Leftmost charector in a string
    Syntax : ASCII ('char Expression')

    NCHAR
    function in SQLServer
    Returns the Unicode Charector with specified Integer code, as defined be Unicode standard
    Syntax :NCHAR(Integer Expression)

    SOUNDEX
    function in SQLServer
    Returns a four-charector code to evalute the similarity of two strings
    Syntax : SOUNDEX('char Expression')
    Ex:
    Select SOUNDEX('smith'),SOUNDEX('smythe')
    Result:
    S530 S530

    CHAR
    function in SQLServer
    Convert an Int ASCII code to a Charector
    Syntax : CHAR( Integer Expression )
    Note : Is an integer from 0 through 255, NULL is return if the integer expression not in the Range

    PATINDEX
    function in SQLServer
    Returns the starting position of the first occurance of a pattren in the specied expression, or Zero if the pattren is nor found the specified expression, on all valid text and charector data types.
    Syntax : PATINDEX('%pattren%',expression)

    SPACE
    function in SQLServer
    Returns a string of Repeated Spaces
    Syntax : SPACE (Integer Expression)
    Ex:
    Select 'Myname' + Space(2) + 'Lastname'
    Result:
    Myname Lastname

    QUOTENAME
    function in SQLServer
    Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier
    Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )
    ' character_string '

    Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

    ' quote_character '

    Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

    Ex:
    Select QUOTENAME('abc[]def')
    Result :
    [abc[]]def

    STR
    function in SQLServer
    Returns characters data converted from numaric data
    Syntax : STR (float Expression [,length,[[decima]])
    Ex;
    Select STR(123.45,6,1)
    Result
    123.4

    DIFFERENCE
    function in SQLServer
    Retruns interger value that indicates the difference between SOUNDEX value of two character expressions
    Syntax : DIFFERENCE (firat expression, second Expression)

    REPLACE function in SQLServer
    Replaces all occurences of Specified string value with another string value
    Syntax : Replace (car expression,old string,new string)
    EX:
    Print REPLACE('abcdefghcd','c','x')
    Result:
    abxdefghxd

    STUFF function in SQLServer
    Deletes a specied length of characters and insert another set of characters at specified starting point
    Syntax : STUFF(char expression,start,length,char expression)
    Ex :
    select STUFF('abcdef',2,3,'ijklmn')
    Result:
    aijklmnef


    LEFT function in sqlserver
    Return the left part of the char expression with specied number of characters
    Syntax : LEFT(char expression,length)
    Ex:
    print LEFT('abcdef',2)
    Result:
    ab

    REPLICATE function in SQLServer
    Replicate a string value in specified numver of times
    Syntax : REPLICATE(char expression, interger expression)
    Ex:
    Print REPLICATE('0',4) + 'S'
    Result:
    0000S

    SUBSTRING function in SQL Server
    Returns a part of charecter, byte, text or Image expression
    Syntax : SUBSTRING(char expression,start,length)
    Ex:
    select SUBSTRING('myname',1,2)
    Result:
    my

    LEN function in SQL Server
    Returns length of specified char expression, excluding trailing blank spaces
    Syntax : LEN(char expression)
    Ex :
    print LEN(' my Name')
    Result :
    7


    REVERSE function in SQL Server
    Returns reverse of char expression
    Syntax : REVERSE(char expression)

    Create your own key in Registry using Microsoft.Win32.RegistryKey in c#.Net


    //Check for key exists in Registry before creating New key and if not exists then create new key

    if (((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))) == null)
    {
    Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.CreateSubKey("MyChandruKey123");
    key.SetValue("MyConnStrChandru123","");
    }
    //Get the Key value based on key name
    string conns = ((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))).GetValue("MyConnStrChandru123").ToString();

    Encrypt and Decrypt the string using RSACryptoServiceProvider in c#.Net within Single Line of Code

    //Call the functions

    string EncryptedString= GetEncryptedText("Chandru");
    string DecryptedString = GetDecryptedText(EncryptedString);


    //Create an object for RSACryptoServiceProvider
    RSACryptoServiceProvider RSAEncrypt = new RSACryptoServiceProvider();
    //Encrypt the String Provided
    public string GetEncryptedText(string PlainStringToEncrypt)
    {
    return Encoding.Default.GetString(RSAEncrypt.Encrypt(Encoding.Default.GetBytes(PlainStringToEncrypt), false));

    }

    //Decrypt The String Provided
    public string GetDecryptedText(string EncryptedStringToDecrypt)
    {
    return Encoding.Default.GetString(RSAEncrypt.Decrypt(Encoding.Default.GetBytes(EncryptedStringToDecrypt), false));
    }

    Wednesday, June 3, 2009

    Access Table withought linking Servers

    Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',
    'SELECT * FROM pubs.dbo.authors') AS A Where A.MaintenanceDate <> MaintenanceDate




    Select A.* From OPENROWSET('MSDASQL',
    'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass',
    'SELECT * FROM database.dbo.table') as A
    Right Outer Join database.dbo.table As B on A.PlantID = B.PlantID
    AND A.RuleType = B.RuleType AND A.RulePrefix = B.RulePrefix and
    A.RuleSuffix = B.RuleSuffix
    Where A.MaintenanceDate <> B.MaintenanceDate




    SELECT A.*
    FROM database.dbo.table AS B
    LEFT JOIN OPENROWSET('MSDASQL', 'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass'
    ,'SELECT * FROM database.dbo.table') AS A
    ON B.PlantID = A.PlantID AND B.RuleType = A.RuleType AND B.RulePrefix = A.RulePrefix
    AND B.RuleSuffix = A.RuleSuffix AND B.MaintenanceDate <> A.MaintenanceDate