Primary key: Primary key is a key that is used for uniquely identify the each rows in a table. A primary key cannot be null and cannot be more than one primary key in a table. Where we create a primary key a cluster index is created. But we can change it with non- cluster… Read More »
A Superkey is a set of columns in a table for which there are no two rows that will share the same combination of values. So, the superkey is unique for each and every row in the table. A superkey can also be just a single column. A candidate key is a closely related concept… Read More »
When we are dealing with dates in mysql sometimes we need to calculate the age in years months and days directly from the mysql query We can easily achieve it from the below sql query SELECT name,dob, CONCAT(TIMESTAMPDIFF( YEAR, dob, now() ),’ Years,’, TIMESTAMPDIFF( MONTH, dob, now() ) % 12,’ Months,’, FLOOR( TIMESTAMPDIFF( DAY, dob,… Read More »
Error: Unable to connect to the Database. Description: While trying or establishing a database connection and the connectivity to the database is not configured properly then this error occurs. More exception details as followed. Exception Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or… Read More »
In this post, we will learn what is temporary tables, types of temporary tables and difference between local & global temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The… Read More »
Drop all non-system stored procs DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = ‘DROP PROCEDURE [dbo].[‘ + RTRIM(@name) +’]’ EXEC (@SQL) PRINT ‘Dropped Procedure: ‘ + @name SELECT… Read More »
UPDATE TABLE_1 SET TABLE_1.col_1=TABLE_2.col_1, TABLE_1.col_2=TABLE_2.col_2, TABLE_1.col_3=TABLE_2.col_3 FROM TABLE_2 t1 INNER JOIN OTHER_TABLE t2 ON t1.ID=t2.ID WHERE TABLE_1.col_3=’test’
In below query, we will Drop a foreign key constraint only if it exists in SQL Server . IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N’dbo.FK_TableName_TableName2′) AND parent_object_id = OBJECT_ID(N’dbo.TableName’) ) ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]
Here, I will show you how to insert results of a Stored Procedure into a Temporary Table with an example as below: CREATE PROC sp_order AS BEGIN SELECT * FROM sys.t_order END GO sp_configure ‘Show Advanced Options’, 1 GO RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE GO SELECT * INTO #temp_orderdetails FROM… Read More »
Varchar is a Non-Unicode Variable Length character data type and takes number of bytes equal to the number of Characters entered plus two bytes extra for defining offset. It is good to use If we know that data to be stored in the column or variable doesn’t have any Unicode characters. Nvarchar is a UNicode… Read More »
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement. INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 ), ( Value1, Value2 ) ;