Hello Friends, It may you know about it but I would like to share this thing with you. We were working on one of project where we faced issue. we are storing data through sql server store procedure using date parameter. As per our logic, the date parameter has not date value it may be empty string. Whenever date parameter has empty string sql server store initial date(1900-01-01) in the date field. We checked our whole logic to find out how the initial date come if date parameter has an ...

Hello

Once I want to get the list of all tables which contains the identity column. After some googling finally I got the sql query which return all the tables which has identity column. It will also return column name, the Seed Values, Increment Values and Current Identity Column value of the table.

 SELECT TABLE_NAME, c.name AS ColumnName, IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity FROM INFORMATION_SCHEMA.TABLES INNER JOIN sys.tables t ON t.name = TABLE_NAME INNER JOIN sys.columns c ON t.object_id = c.object_id AND c.is_identity = 1 WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' 

Hello Friends,

Try to avoid function (user defined or inbuild) in where condition of sql query as it is always overlook indexing created on tables and taking much longer time to execute in case of table having large number of rows (over million)

Have a look below query. How to avoid date range condition? I hope it may be helpful to you ...

Today I was trying to setup fulltext indexing on one of my database table. I have finish with creating fulltext catelog and tried to right click on table to create fulltext index but option was greyed out and there is no way to create index. This is becuse Full-text index was diabled at database leve you can enable it by running below statement.   use [mydatabase name] go EXEC sp_fulltext_database 'enable'   That's it and refresh table group and option is enable now ...

Following code can be used to measure query elapsed time by performing multiple runs of the same query and finding the average elapsed time of the query ...

Easiest way to reset the identity column value in MSSQL is to use 'TRUNCATE TABLE' on the table.
This will reset the identity column value.This will work only if the table has no references made by any child ...

Hello Friends,

Today, I wanted to list down all table from column name in sql server. Sql server stores all table, column, database or etc in it's own system table. So it's easy to fetch record from it. I have given example below how to list down table list from field name. Copy and paste below code and just pass column name in this query. I hope it is helpful for you and you may like it ...

Temporary Table

The following code demonstrates how to create a temporary table.
Syntax :

 CREATE TABLE #TempEmployee
	(
	employeeID int,
	employeeName varchar(100)
	);