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 »
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 »
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 »