Narayanan DotNet Experience

add Seconds to DateTime in Sql Server

We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result. Below example shows how we can add two seconds to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
DATEADD(second,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
DATEADD(ss,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
DATEADD(s,2,GETDATE()) 'Now + 2 Seconds'

IIF funciton in SQL Server 2012 and give an Example

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

UPDATE SimpleTable
SET Gender = IIF(NAME = 'NARAYANAN', 'LAKSHMI', 'NARAYANAN')
GO
SELECT *
FROM SimpleTable
GO

Example for CASE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(MAX))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

CASE Funciton

UPDATE SimpleTable
SET Gender = CASE NAME WHEN 'NARAYANAN' THEN 'LAKSHMI' ELSE 'NARAYANAN' END
GO
SELECT *
FROM SimpleTable
GO

Example for REPLACE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO

Replace male into female using UPDATE Query

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable
GO

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = '<Sp_name>'

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%<search_word>%'

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

SQL Constraints

SQL Constraints

SQL constraints are used to specify rules for the data in a table.
If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax for SQL Constraints

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
);

In SQL, we have the following constraints

NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value when specified none for this column

Column Exists or Not in SQL Server

CREATE FUNCTION Axfn_ColumnExists
(
@TableName VARCHAR(100) ,
@ColumnName VARCHAR(100) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Result VARCHAR(100);
IF EXISTS
(
SELECT 1 FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName ) BEGIN
SET @Result = 'Already Exsits'
END
ELSE
BEGIN SET @Result = 'Not Available, You can create now!'
END
RETURN (@Result)
END
GO

Pages