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 […]
Category: SQL Server
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 […]
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 […]
Superkey in SQL
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 […]
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, […]
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 […]
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 […]
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 […]
UPDATE statement with JOIN in SQL Server
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’
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]
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 […]
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 […]
Inserting multiple rows in a single SQL query
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 ) ;
What is CROSS JOIN
CROSS JOIN is an output which is the number of rows in the first table multiplied by the number of rows in the second table, if there is not using WHERE clause with CROSS JOIN then can called as Cartesian Product. Sytax:- SELECT * FROM table1 CROSS JOIN table2
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. […]