Featured
-
Find out a particular column throughout the database in Postgresql
Previously got a situation where I needed to get t
by Nitika.Verma
Tags
SQL : Difference between UNION and UNION ALL
Both UNION and UNION ALL operators are used to combine the results of two or more SELECT statements. However the two differ as below:
1) UNION performs a DISTINCT on the result set, removing any duplicate rows.There is a performance hit when ...
Endpoints in SQL Server
Endpoints in SQL
For providing TCP/IP communication between your SQL and other applications you need to create end point for that .
Creating end points is a series of steps
Message
Contract
Queue
Service
Route...
CTE in SQL
CTE Common Type Expression
For providing pivoting in tables say you want to view your data in such a form such that rows comes in form of columns and columns in form of rows. For doing that SQL provide facility to use CTE in SQL
Ex :
Suppo...
SQl Server : How to insert results of a stored procedure to a table?
In the following article we will learn how to insert results of a stored procedure to a table. There are two approaches to do this depending on whether we know the table schema beforehand or not.
We will be using the following stored procedure t...
SQL Server : How to insert values in identity column programmatically?
Identity field is typically used as a primary key in database. When a new record is inserted into a table, this field automatically assigns an incremented value to this column.However, inserting our own value into this column is not straightforwa...
SQL Server : How to truncate a table being referenced by a FOREIGN KEY constraint ?
Many times we need to truncate a table which has an FK constraint on it. Typically we get the following error:
Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
The solution to the above problem i...
SQL Server : How to shrink or clear the transaction log file ?
Many times the large size of transaction log file (.ldf) in Microsoft SQL is too big which leads to performance issues and loss of valuable disk space.Therefore it's imperative to periodically do database maintenance.
In order to clear or ...
SQL Server : Comparison of stored procedure and UDF
Stored Procedures are pre-compile database objects which are compiled for first time and execution plan is saved for further use. However UDF is compiled and executed every time when it is invoked.This article provides a detailed comparison of st...
SQL Server : Use of sysobjects table
SQL Server sysobjects Table contains one row for each object created. In other words, it has a row for every constraint, default, log, stored procedure etc in the database.The two columns most useful in this table are sysobjects.name and sysobje...
SQL Server : Instead of Trigger
A trigger is a special kind of a store procedure that executes in response to specific actions on the table like insertion, updation or deletion. It is a database object which is bound to a table. Triggers cannot be explicitly invoked. The only ...
Schemas in SQL
Schema or Structure can be defined in SQL
A schema is a collection of logical structures of data objects. A schema is associated with a database user and has the same name as that user. Each user have a single schema. Schema is mainly collecti...
SQL Server Management Data Warehouse (MDW)
Management Data warehouse is a database containing a warehouse of data useful for managing SQL Server. Ensuring the excellent and high performance for all the users and customers is a high priority for Database Administrators. Troubleshooting SQL...
Compression and Decompression in SQL Server 2016
In the upcoming version of SQL 2016 a new component of COMPRESS and DECOMPRESS T-SQL functions is added. SQL Server 2016 provides built in functions for compression and decompression.The COMPRESS and DECOMPRESS functions can store and retrieve da...
Recovery Models in SQL server
There are three types of Recovery model in SQL server
Simple recovery model -- gives you the backup to replace the entire database in the event of a failure . In simple recovery model data is recoverable only to the most recent backup ....
Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT
In most of our applications, We need last inserted identity value in SQL Server table. For getting the last inserted identity value we have multiple options:
@@IDENTITY
SCOPE_IDENTITY
IDENT_CURRENT
All these three return the l...
Finding Distance on basis of Longitude and Latitude from Database
Below is an example of finding all the user who are stored in db and lies in the given range of Distance on basis of Longitude and Latitude.
CREATE proc [dbo].[SP_UserByDistance]
(
@UserID uniqueidentifier,--UserId
@Date datetime, --Date
@...
Inserting data from one table to another
Inserting Data from one database table to different Database table.
we can insert or copy data from one database table to another table of different database in SQL Server 2008 using following query keeping one thing in mind that the datatype...
