Type 1:
SELECT Column_name(s)
INTO newtable
FROM sourcetable
Its column and its size created automatically
E.g. 1:
SELECT *,
Row_number() OVER(ORDER BY price DESC) AS rownumber
INTO #temptbl
FROM MASTER
E.g. 2:
SELECT resultset.*
INTO temptable
FROM (SELECT *
FROM SOURCE) AS resultset
Type 2
INSERT INTO @TempRecreationMasterID
SELECT Column_name(s)
FROM sourced
Have to create Table and its column
E.g. : To drop the table if already exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
CREATE TABLE #temp
(
propertyid INT,
propertyname VARCHAR(100),
publish INT,
starrate INT
)
INSERT INTO #temp
SELECT propertyid,
propertyname,
publish
FROM vw_basicsearch
Msg 217, Level 16, State 1, Procedure EditPropertyName, Line 24
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).give select table1.columnNames, table2.columnNames from table, table2Don't give select table1.*, table2.* from table1, table2
Last identity value generated in all tables
Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database. This is how you return all the tables with their last generated identity values in a SQL Server Database
SELECT Ident_current(table_name),
*
FROM information_schema.tables
WHERE table_type = 'base table'
AND Objectproperty(Object_id(table_name), 'TableHasIdentity') = 1
Cannot add rows to sysdepends for the current object because it depends on the missing object 'GetAssociate'. The object will still be created
Delete With Join
DELETE propertyrecreationmanager
FROM propertyrecreationmanager
INNER JOIN propertyrecreation
ON propertyrecreationmanager.recreationid =
propertyrecreation.recreationid
WHERE propertyid = @PropertyID
JOIN
JOIN combines two separate result sets
To get the procedure name in a database
SELECT name AS spname
FROM sysobjects
WHERE ( xtype = 'p' )
AND ( name NOT LIKE 'dt%' )
ORDER BY name,
crdate
Object Type is optional, and possible values it can hold are. (Items in bold are frequently used)
Object type:
AF = Aggregate function
(CLR)C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML triggerIF = SQL inlined table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table
Primary key
Only one Identity column—
Can't be null-
Identity
-- Automatic value
-- Only one Identity column
-- Can't be null
-- Can exist in addition of primary key,
GUID
-auto columns
- Uniqueindentifier
-- Can be multiple
-- Can be null
- Uniqueindentifier
- rowguid
-- Auto generated
-- Only one Identity column
-- Can be null
-- Can exist in addition of primary key,
Identity columns
- Unique Key
-- Can be null
-- Can be multiple
ACID (Atomicity, Consistency, Isolation, Durability)
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures.
For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Data Integrity - no two users should modify the same piece of data at the same time.
Above four rules are very important for any developers dealing with databases.
Aggregate Function "SUM" the group if it contains null in any of the column
SELECT SUM(location)
FROM review
WHERE location IS NOT NULL
SELECT propertyid,
COUNT(propertyid) AS propertyidcount,
SUM(Coalesce(cleanliness, 0)) + SUM(Coalesce(facilities, 0)) +
SUM(Coalesce(SERVICE, 0)) + SUM(Coalesce(location, 0)) + SUM(
Coalesce(pricing, 0)) AS customerratings
FROM review
GROUP BY propertyid
View
If a view is defined with the select * statement and the base table is altered by adding a few columns, the new columns do not get included in the view. The asterisk (*) symbol is interpreted and expanded only when the view is created. In order to access the new columns via the view, it is necessary to drop the view and recreate it.
Temporary Table
When we are creating Temporary Table the order of column name should be same as both in the new table and the oldtable and also if we use select statement
eg:
CREATE TABLE #temp
(
propertyname VARCHAR(100),
propertyid INT,
publish INT,
propertyoverview VARCHAR(5000)
)
INSERT INTO #temp
SELECT propertyid,
propertyname,
publish,
propertyoverview
FROM roomtype
ERROR1:
Conversion failed when converting the varchar value 'PropertyName', to data type VARCHAR.
--Why? Because the column order is different
ERROR2: also this error
Conversion failed when converting the varchar value 'Wrap in div or asp:Panel with width for basic wrapping... Any other solution seems quite silly. Wrap in div or asp:Panel with width for basic wrapping... Any other solution seems quite silly.' to data type int.
Dynamic ORDER BY in SQL Server 2005
Order BY CASE WHEN @SortOrder = 'Hotel A - Z' THEN (rank() OVER (ORDER BY
propertyname ASC)) WHEN @SortOrder = 'Hotel Z - A' THEN (rank() OVER (ORDER BY
propertyname DESC)) WHEN @SortOrder = 'Star Rate 1..5' THEN (rank() OVER (ORDER
BY starrate ASC)) WHEN @SortOrder = 'Star Rate 5..1' THEN (rank() OVER (ORDER BY
starrate DESC)) ELSE propertyname END
Note:-
Case WHEN @Conditions = 'StarRate' THEN starrate ELSE propertyname END
In the above StarRate is INT and PropertyName is VARCHAR We will get an error Conversion failed when converting the varchar value '************' to data type int.
To Over Come this situation we have to case them
Case WHEN @Conditions = 'StarRate' THEN CAST(starrate AS VARCHAR) ELSE
propertyname END
http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/
Row_number()
eg:--
SELECT Row_number() OVER (ORDER BY cityid) AS rownumber
FROM cityrank
is assigned to the results of the query. We can't able to give ROW_NUMBER()>10. Because the function is called after the query obtaining the results.
ROW_NUMBER () OVER ([
RANK () OVER ([
DENSE_RANK () OVER ([
NTILE (integer_expression) OVER ([
Note:
Eg:SELECT ROW_NUMBER() OVER ( ORDER BY (select 1)) AS 'Row Number'
(select 1) To overcome sorting since sorting takes more execution time,
Rank() OVER ( ORDER BY dbo.roomrates.DATE) AS 'Rank', dense_rank() OVER ( ORDER
BY dbo.roomrates.DATE) AS 'Dense Rank', ntile(4) OVER ( ORDER BY
dbo.roomrates.DATE) AS 'Quartile' FROM roomrates
SELECT HAI = 1
DECLARE @PropertyName VARCHAR(100)
DECLARE @CountryID INT
DECLARE @CityID INT
SET @PropertyName = NULL
SET @CountryID = 3
SET @CityID = 0
SELECT *
FROM vw_basicsearch
WHERE CASE
WHEN @CityID = 0
AND @PropertyName = NULL THEN @CountryID = 3
WHEN ( @CityID = 0
AND ! Isnull(@PropertyName, ' ') ) THEN
propertyname = @PropertyName
END
GroupBy
you can't refer to an aliases field in the GROUP BY clause. It is good to filter data in where clause before grouping.
Get all the tables in a DB
select count(column_name) as count,Table_name from information_schema.columns group by Table_name
Last Ran Query - Recently Ran Queryhttp://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/
exec sp_who2
Check for blocking. Run sp_who2 to see what process it blocking another one. Look at "blocks, avoiding" in SQL Server BOL
Get all the columns from database
SELECT column_name + ','FROM information_schema.columnsWHERE column_name <> 'HotelAmenitiesID'Fiter by column ==> HotelAmenitiesID
on delete cascade
Resolve Sql Server 2005 "Agent XPs disabled" error
Remove NULL coulum with some text
SELECT offersid,
priority = CASE
WHEN Isnull(priority, ' ') = ' ' THEN 'n/a'
--(or) WHEN Priority IS NULL THEN 'n/a'
ELSE priority
END
FROM hotelieroffers
ISNULL
ISNULL( , ) = Note both datatype should be same. Otherwise is give can't able to convert from to if we give something inside ''if we give ' ' no problem
While declare a parameter in Procedure give the correct size if declare VARCHAR without size it took only one char as its size and trim the value of the parameter to one char
SELECT as joins
SELECT roomids.items AS roomid,
noofrooms.items AS noofrooms
FROM (SELECT *
FROM dbo.Split(@RoomIDs, '±')) AS roomids
INNER JOIN(SELECT *
FROM dbo.Split(@NoOfRooms, '±')) AS noofrooms
ON noofrooms.id = roomids.id
Update With Joins
UPDATE roomrates
SET availability = availability + CAST(roomavailable.noofrooms AS INT)
FROM roomrates AS roomrates
INNER JOIN(SELECT roomids.items AS roomid,
noofrooms.items AS noofrooms
FROM (SELECT *
FROM dbo.Split(@RoomIDs, '±'))AS roomids
INNER JOIN(SELECT *
FROM dbo.Split(@NoOfRooms, '±'))AS
noofrooms
ON noofrooms.id = roomids.id)roomavailable
ON roomrates.roomid = roomavailable.roomid
To Delete All Table and StoredProcedures in a DATABASE
Original Link
CREATE PROCEDURE Usp_dropspfunctionsviews
AS
-- variable to object name
DECLARE @name VARCHAR(1000)
-- variable to hold object type
DECLARE @xtype VARCHAR(20)
-- variable to hold sql string
DECLARE @sqlstring NVARCHAR(4000)
DECLARE spviews_cursor CURSOR FOR
SELECT Quotename(routine_schema) + '.' + Quotename(routine_name) AS name,
routine_type AS xtype
FROM information_schema.routines
UNION
SELECT Quotename(table_schema) + '.' + Quotename(table_name) AS name,
'VIEW' AS xtype
FROM information_schema.VIEWS
OPEN spviews_cursor
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
WHILE @@FETCH_STATUS = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'PROCEDURE'
BEGIN
SET @sqlstring = 'drop procedure ' + @name
EXEC Sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a function
IF @xtype = 'FUNCTION'
BEGIN
SET @sqlstring = 'drop FUNCTION ' + @name
EXEC Sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a view
IF @xtype = 'VIEW'
BEGIN
SET @sqlstring = 'drop view ' + @name
EXEC Sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- get next record
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
END
CLOSE spviews_cursor
DEALLOCATE spviews_cursor
GO
CREATE PROCEDURE Dropspviews
AS
-- variable to object name
DECLARE @name VARCHAR(100)
-- variable to hold object type
DECLARE @xtype CHAR(1)
-- variable to hold sql string
DECLARE @sqlstring NVARCHAR(1000)
DECLARE spviews_cursor CURSOR FOR
SELECT sysobjects.name,
sysobjects.xtype
FROM sysobjects
JOIN sysusers
ON sysobjects.uid = sysusers.uid
WHERE Objectproperty(sysobjects.id, N'IsProcedure') = 1
OR Objectproperty(sysobjects.id, N'IsView') = 1
AND sysusers.name = 'USERNAME'
OPEN spviews_cursor
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
WHILE @@FETCH_STATUS = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'P'
BEGIN
SET @sqlstring = 'drop procedure ' + @name
EXEC Sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a view
IF @xtype = 'V'
BEGIN
SET @sqlstring = 'drop view ' + @name
EXEC Sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- get next record
FETCH NEXT FROM spviews_cursor INTO @name, @xtype
END
CLOSE spviews_cursor
DEALLOCATE spviews_cursor
Search for text in the StoredProcedureorHow do I find a stored procedure containing
Find Text
SELECT ROUTINE_NAME,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'AND ROUTINE_TYPE='PROCEDURE'
--This is with Schema
SELECT ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%babu%'
AND ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_SCHEMA = 'Vehicle'
ORDER BY ROUTINE_NAME
--(OR)--
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%foobar%'AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
--(OR)--
SELECT Name
FROM
sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%'
--(OR)--
SELECT OBJECT_NAME(object_id)FROM
sys.sql_modules
WHERE Definition LIKE '%foobar%'AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
Concatenate strings from multiple rows into one in SQL Server 2000 or 2005
DECLARE @Column VARCHAR(500)
SELECT @Column = COALESCE(@Column + ',', '') + Column1 FROM TABLE1
Date Condition
While checking the date in the condition operator it also check the time. So truncate the time by converting the format.
BETWEEN CONVERT(DATETIME, @CheckInDate, 102) AND CONVERT(DATETIME, DATEADD(DAY, -1, @CheckOutDate), 102)
Drop TempTable if already exists
IF EXISTS(SELECT *
FROM tempdb.sys.tables
WHERE name LIKE '#TempTbl%')
BEGIN
DROP TABLE #temptbl
END
(OR)
IF Object_id('tempdb..#TempTbl') IS NOT NULL
BEGIN
DROP TABLE #temptbl
END
Note: --
Temporary table created in the tempdb database. Delete it at the end of the procedure. Since it occupies some space.
Dynamic Select Field
SELECT propertyid,
propertyname,
CASE
WHEN ( @Conditions = 'StarRate' )
OR ( @Conditions = 'PropertyName' ) THEN starrate
WHEN @Conditions = 'Price' THEN ( SUM(price) )
ELSE starrate
END price,
starrate
FROM vw_basicsearch
--At the end of case statement we are passing all the column names in the Then satement.
--Why,because in the below statement we are ordering columns dynamically.
SELECT *,
Row_number() OVER(ORDER BY(CASE WHEN @Conditions = 'Price' THEN CAST(
price AS
VARCHAR) WHEN @Conditions = 'StarRate' THEN CAST(starrate AS VARCHAR)WHEN
@Conditions = 'PropertyName' THEN propertyname END)ASC)
FROM MASTER
How to implement WHERE and ORDER BY clauses dynamically using CASE statement?
Let’s have a look on table Customers from Northwind database.Customers: CustomerId, CompanyName, ContactName, City, Country, etc.The following stored procedure gets all customers having CompanyName or ContactName equals with a specific value. You need to find a company name or a contact name in database and then to order the results after the field you perform the search.
The following code snippet shows us how to use CASE statement.
CREATE PROCEDURE Getcustomers @field VARCHAR (50),
@name VARCHAR (50)
AS
SELECT customerid,
companyname,
contactname,
city,
country
FROM customers
WHERE CASE @field
WHEN 'CompanyName' THEN companyname
WHEN 'ContactName' THEN contactname
END LIKE 'a%'
ORDER BY CASE @field
WHEN 'CompanyName' THEN companyname
WHEN 'ContactName' THEN contactname
END
And now let’s execute our stored procedure:
EXEC GetCustomers 'CompanyName', 'a'
EXEC GetCustomers 'ContactName', 'a'
ALL Dynamic
DECLARE @ColumnIntValue INT
DECLARE @ColumnValue VARCHAR(100)
AND ( CASE @ColumnName WHEN 'Hotel' THEN PropertyName
END LIKE '%' + @ColumnValue + '%'
OR
CASE @ColumnName
WHEN 'CityID' THEN CityID
WHEN 'CountryID' THEN CountryID
END = @ColumnIntValue)
Here if column dataType is differnet. Declare different dataType local variable and ansign the value to it.Otherwise we wil get
Msg 245, Level 16, State 1, Line 31Conversion failed when converting the varchar value 'Asian Hotel' to data type int.
Dont's
Don't create SELECT column(s) INTO #temp FROM TableHere the SQL Server don't know about the size of each column.It has to analyze and create the size.So create the table before and do the insert.
Note:
SELECT column(s) INTO #temp FROM Table
If our query is like this and if we are calling Execution Plan (CTRL + L), it will show #temp errorBecause it doesnot know the size of the columns.
Start SQLServerAgent
EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
Checks the current identity value for the all the tables
SELECT 'DBCC CHECKIDENT (' + table_name + ')'
FROM information_schema.tables
SELECT *
FROM sys.objects
INNER JOIN (SELECT *
FROM sys.indexes) AS indexes
ON indexes.object_id = sys.objects.object_id
WHERE sys.objects.TYPE = 'U'
FillFactor: The 'fill factor' option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed), you can set the 'fill factor' option to 100. When the table's data modified very often, you can decrease the 'fill factor' option to 70 percent, for example.
Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT SUM(TotalKM) FROM Table
Change it to
SUM(CASE WHEN TotalKM IS NULL THEN 0 ELSE TotalKM end)
Get the Created and Modified Date of a Stored Procedure
SELECT name,
create_date,
modify_date
FROM sys.objects
WHERE TYPE = 'P'
ORDER BY modify_date DESC
Get all table names from all databases in the server
EXEC sp_MSForEachDb 'select TABLE_CATALOG, TABLE_NAME from [?].INFORMATION_SCHEMA.TABLES'
Get all table names from current database
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
View
Remember that a view using the WITH CHECK OPTION provides some flexibility that can’t be duplicated with a normal CHECK constraint.
Wait for sometime before insertion
WAITFOR DELAY '00:00:00.033'
Round up to 2 decimal places
Convert(DECIMAL(5, 2), round(255.87908765444, 2))
Reseed Identity
DBCC checkident ('PropertyDining', reseed, 0)
sql query works from left to right.
IDENTITY columns in 6.5 suffer a problem if the server is shut down unexpectedlyI usually recommend running DBCC CHECKIDENT on each table when the server starts up.
If we set primark key for a column it automatically creates cluster index for the column
--==========================
-- Created By :
-- Created date:
-- Modified By:
-- Last Modified Date:
-- Module :
-- Screen(s) :
-- Called by :
-- Description:
-- PARAMETERS
-- @ParamName -- Description
-- Return:
--**************************
--** Change History
--**************************
--** PR Date Author Description
--** -- -------- ------- ------------------------------------
--** 1 01/10/2008 Dan added inner join
--********************************
