Archive for category SQL server
WHILE EXISTS ( SELECT * FROM table WHERE <condition to delete>)
BEGIN
SET ROWCOUNT 1000
DELETE Table WHERE <Condition>
SET ROWCOUNT 0
ENd
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
1. A NULL value represents an UNKNOWN value
2. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.
IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory;GOCREATE TABLE [dbo].[SalesHistory]( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [S Read More
Executing T-Sql batch multiple times.
Description:
If we want to execute T-sql command multiple times
2000:
CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID unique identifier) CREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID unique identifier) GO DECLARE @counter INT SET @counter = 0 WHILE @counter < 1000 BEGIN INSERT INTO dbo.TEST (ROWID) VAL Read More
Deleting a duplicate row without primary key.
Scenario:
ID
FirstName
LastName
1
Bob
Smith
2
Dave
Jones
3
Karen
White
1
Bob
Smith
1
Bob
Smith
2000:
‘filters to no. of rows to process, here it allows to take only I record
set ROWCOUNT 2
‘so here it scans only 2 records from the available 3 records and deletes those 2 records
delete from customers2 where Read More
SQL – Truncate vs Delete
Jan 7
Difference between truncate and Delete (deleting records)
Truncate
Delete
Deletes all the records by deallocating the pages which reduces the resource overhead of logging the deletion and no. of lock acquired. Only one record is entered in the transaction log that is the deallocation of page.
Deletes one row at a time which is entered in the transaction log as LSN(Log sequence no.).
Records remo Read More
SQL Server Date formats
Jan 6
Date formats in sql server
DATE FORMATS
Format #
Query (current date: 12/30/2006)
Sample
1
select convert(varchar, getdate(), 1)
12/30/06
2
select convert(varchar, getdate(), 2)
06.12.30
3
select convert(varchar, getdate(), 3)
30/12/06
4
select convert(varchar, getdate(), 4)
30.12.06
5
select convert(varchar, getdate(), 5)
30-12-06
6
select convert(varchar, Read More
SQL server and code names
Nov 10
In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.
1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 †Read More
To recover MySQL root password:
http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
To connect sql management studio to any sql server using non defualt port, we have to use “,” beside the server IP (or host name).
For e.g., server: 1.1.1.1 and port 1234 we specify it as
1.1.1.,1234
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
USE AdventureWorks
GO
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
