Category SQL
Delete duplicate records from Table using SQL
January 15, 2015 Category: SQL
Comments:
Delete duplicate records from Table using SQLRead More
Generate Random Password In SQL
August 28, 2014 Category: SQL
Comments:
Today i'll explain how to generate random password in SQL by using a template.
Template work as like as format that every new password will generate as same format.
Read More
Convert Rows to Columns in SQL Server Without Using Pivot
August 24, 2014 Category: SQL
Comments:
Convert Rows to Columns in SQL Server Without Using Pivot
DECLARE @UserData TABLE (UserName VARCHAR(50),Mobile VARCHAR(50))
INSERT INTO @UserData VALUES (‘Abdul’,23243434)
INSERT INTO @UserData VALUES (‘Abdul’,345435345)
INSERT INTO @UserData VALUES (‘Abdul’,23423)
INSERT INTO @UserData VALUES (‘Raj’,2345435)
INSERT INTO @UserData VALUES (‘Raj’,324324)
INSERT INTO @UserData VALUES (‘Raj’,234234)
INSERT INTO @UserData VALUES (‘Manish’,3453453)
INSERT INTO @UserData VALUES (‘Manish’,345345)
INSERT INTO @UserData VALUES (‘Manish’,23452)
;WITH UserMaster AS
( SELECT
*,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber
FROM @UserData
)
SELECT DISTINCT
t.UserName
,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3
FROM @UserData t
LEFT OUTER JOIN UserMaster t1 ON t.UserName=t1.UserName AND t1.ColumnNumber=1
LEFT OUTER JOIN UserMaster t2 ON t.UserName=t2.UserName AND t2.ColumnNumber=2
LEFT OUTER JOIN UserMaster t3 ON t.UserName=t3.UserName AND t3.ColumnNumber=3 Order byt.userName desc
Read More Get List of All Stored Procedures,Tables,Triggers from Database in SQL Server
August 24, 2014 Category: SQL
Comments:
Get List of Stored procedure,Tables,Triggers names in database
To get list of procedures in SQL Server we need to write the query like as shown below
USE TempDB
SELECT * FROM SYS.PROCEDURES
Get List of table names in databaseRead More
Change Identity Column Value in SQL Server
August 24, 2014 Category: SQL
Comments:
I will explain to change identity column seed value in SQL server or reseed identity column seed value of table in SQL server or how to change identity column value in SQL server.
DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value)
Table_Name is name of your identity column tableRead More
Drop index from Table
August 24, 2014 Category: SQL
Comments:
To remove index from table we use drop index statement in sql server.
To create index on column in sql server that syntax will be like as shown below
Syntax to Create Index
CREATE INDEX indexname ON Tablename(columnname)Read More
Difference between Cursor and While Loop with Example
August 24, 2014 Category: SQL
Comments:
Here I will explain difference between cursor and while loop in sql server with example or explain cursor vs while loop with example in sql server. Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.Read More
Database Normalization Tutorial with example
April 8, 2013 Category: SQL
Comments:
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
Database normalization is the process of organizing the fields and tables of a relational database to minimize redudancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.
Normalization is a process of simplifying the relationship between the data in a record it is carried out for the following reasons.
To simplifying the maintenance of data through updates, insertion and deletions
To allow simple retrieval of data in response to query and requests
To avoid restructuring of data when new application requirements arises.
To structure the data so that any relationship can be easily representedRead More
Difference Between Sql Server CHAR, VARCHAR and NVARCHAR Data Type
April 8, 2013 Category: SQL
Comments:
CHAR is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.
OR
It stores fixed length of character. if you declare char(50) then it allocates memory for 50 characters. if you store 10 character word then it store it in 10 characters memory location and other 40 character's memory location will be wasted.
Char datatype can store upto 8000 bytes of fixed-length character.
VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.Read More
System Function in SQL-Server
April 5, 2013 Category: SQL
Comments:
GETDATE() Gives current date time (server’s time zone)
E.g. SELECT GETDATE()return 2011-08-25 09:54:39.963
GETUTCDATE() Gives current date time (GMT + 0 Time zone)
E.g. SELECT GETUTCDATE() return 2011-08-25 04:27:30.163
ISDATE('Value') Returns bool value whether given value is proper date or not
E.g. SELECT ISDATE('2011-08-25') return 1
SELECT ISDATE('2011-13-25') return 0
LTRIM('Value') Trims from left
E.g. SELECT LTRIM(' SQL SERVER ') return ‘SQL SERVER ‘
RTRIM('Value') Trims from right
E.g. SELECT LTRIM(' SQL SERVER ') return ‘ SQL SERVER‘
REPLACE() replace all occurrence of string with other string
E.g. SELECT REPLACE('SQL SERVER ','S','A') return ‘AQL AERVER’Read More