[SQL Server] Search Text in Database Objects

We can easily search the data that we store in our database tables through queries, but what if we want to search the database objects, like stored procedure, trigger, views, user-defined functions, etc. For instance, if we want to know if any specific table is used in any database object, or want to search any special comment mentioned in any of these DB objects. Here is what we do to get it done:

SELECT O.[name] AS [ObjectName]
, O.[type_desc] AS [ObjectType]
, O.[modify_date] AS [ModifiedDate]
, C.text
FROM sys.syscomments C
INNER JOIN sys.objects O ON C.[id] = O.[object_id]
WHERE C.[text] LIKE '%<text-to-search>%'
AND O.[type] in ('V', 'FN', 'TR', 'P')

Solution is just like Aladin's Lamp...! Enjoy.

[SQL Server] Search Table using Column Name

In high pressure professional environment, we sometime require to know which tables have some xyz column, and its quite tedious to search entire database. Here is the magic-wand query for this problem:

SELECT * FROM sys.tables WHERE object_id IN
(SELECT object_id FROM sys.columns WHERE name LIKE '%<column-name>%')

Amazing... isn't it! :)

[T-SQL] Alter Table - Add/Drop Constraint Query

It is a good practice to identify and apply constraints while creating the table, but in real scenario, we usually require to add constraint on existing column or while adding a new column in existing table. For this purpose, a simple and quick solution is to do it through query. Below is how you achieve it:

Add Constraint on Existing Column:

ALTER TABLE <table-name>
ADD CONSTRAINT <constraint-name> 
PRIMARY KEY (<column-name>)

where, "<constraint-name>" is a user defined constraint name to refer it properly.

The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Add Constraint on New Column:

ALTER TABLE <table-name>
ADD <column-name> BIGINT IDENTITY
CONSTRAINT <constraint-name> PRIMARY KEY

where, "<constraint-name>" is a user defined constraint name and "IDENTITY"  is optional if you want to make this identity with default seed and increment value set to 1.


The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD <target-column-name> BIGINT
CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Drop Constraint from Existing Column:

To drop a constraint is very simple:

ALTER TABLE <table-name> 
DROP CONSTRAINT <constraint-name>

Enjoy!

[T-SQL] Table Constraints

Table constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing data integrity.

Types of Constraints:

1. NOT NULL enforces not to accept NULL values
2. UNIQUE assures that the data is unique in the column (including one NULL).
3. PRIMARY KEY uniquely identifies each record in the table without allowing NULL.
4. FOREIGN KEY constraints the data to be inserted that is available in table's column it points to.
5. CHECK is used to limit the value range that can be placed in that column.
6. DEFAULT is used to insert a default value to all new records, if no value is specified.

[SQL Server] SQL Server Isolation level

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. A lower isolation level increases concurrency, but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can affect concurrency negatively. The isolation level required by an application determines the locking behavior SQL Server uses. SQL-92 defines the following isolation levels, all of which are supported by SQL Server:

1. Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).
2. Read committed (SQL Server default level). 
3. Repeatable read.
4. Serializable (the highest level, where transactions are completely isolated from one another).

[SQL Server] Retrieving last generated Identity value

@@IDENTITY:

1. Returns the last IDENTITY value generated implicitly (via trigger or user defined function) or explicitly (by user) in current session.
2. It returns the value regardless of the scope of the statement and the table.

SCOPE_IDENTITY():

1. Returns the last IDENTITY value generated explicitly in current session and by a statement in the same scope.
2. It returns the value regardless of the table, generated explicitly rather than any identity that was created by a trigger or a user defined function.

IDENT_CURRENT(<table-name>):

1. Returns the last IDENTITY value generated in a table.
2. It returns the value regardless of the session and the scope of the statement.

[T-SQL] Referential Integrity

Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

[T-SQL] Database Transaction and ACID properties

Transaction:
A transaction is a sequence of operations performed as a single logical unit of work. This logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.

1. Atomicity: Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged.
2. Consistency: If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction could be rolled back to the pre-transactional state - or if a DBMS allows fields of a record to act as references to another record, then consistency implies the DBMS must enforce referential integrity: by the time any transaction ends, each and every reference in the database must be valid.
3. Durability: When the DBMS's guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure, and that all integrity constraints have been satisfied, so the DBMS won't need to reverse the transaction.
4. Isolation: Refers to the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed.

[T-SQL] Using specific index in SELECT clause

You can specify any index that you want to use explicitly in your SELECT clause after table name:

SELECT <column-names>
FROM <table-name> ( index = <index-name> )

This scheme is also called index hint.

[T-SQL] Difference between Primary Key and Unique Key

Primary Key:

1. Enforce uniqueness of the column on which they are defined.
2. By default, creates a clustered index on the column.
3. Doesn't allow NULL value.
4. A table can have only one primary key.

Unique Key:

1. Enforce uniqueness of the column on which they are defined.
2. By default, creates a non-clustered index on the column.
3. Allows one NULL value.
4. A table can have multiple unique keys.

My First Post

This is my first post. I'm so happy to eventually have it. I hope knowledge shared through this blog will be useful for others as it was for me. :)