ROW NUMBER, RANK and DENSE RANK in SQL

In this post, We will learn the difference between Row_Number, Rank, Dense_Rank and how to use with example. ROW_NUMBER, RANK and DENSE_RANK functions are used to calculate RowID for the result set which is returned from a query but in a slightly different way. Firstly, We will create a table and insert some record for […]

Read More

Self Joins in SQL

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition. For example the following […]

Read More

Difference between Primary, Unique, Candidate and Composite Keys

Primary key: Primary key is the key that is used for uniquely identifying each row in a table. A primary key cannot be null and cannot be more than one primary key in a table. When we create a primary key, a cluster index is created there. But we can change it with non- cluster […]

Read More

Calculate age in years, months and days in mysql

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. A network-related or instance-specific error occurred while establishing a connection to SQL Server.

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 are as follows. Exception Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found […]

Read More

Difference between Local & Global Temporary Tables

In this post, we will learn what are 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 the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

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

Drop a foreign key constraint only if it exists in SQL Server

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]

Read More

Insert results of a Stored Procedure into a Temporary Table

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

Differences between varchar and nvarchar SQL Server data types

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

SQLExpress 2008 CREATE DATABASE permission denied in database ‘master’

Problem: The following error occurred while creating a new database: Create failed for Database (Microsoft.SqlServer.Smo). Additional information: This exception occurred while executing a T-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo): CREATE DATABASE permission is denied in database ‘master’. (Error: 262) Resolution: 1. Type “services.msc” in Run 2. Shut down SQL Server (SQLEXPRESS) from services. Note the exe path. […]

Read More