Archive for category SQL server

Deleting large number of records from sql server 2005

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

Tags: ,

SQL – Handling Null Values

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

Tags:

SQL – Execute T-SQL batch multiple times

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

Tags: ,

SQL – Delete duplicate row without primary key

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

Tags: ,

SQL – Truncate vs Delete

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

Tags:

SQL Server Date formats

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

Tags: ,

SQL server and code names

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

Tags:

Recover MySQL Root Password

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

sql management studio – using non standard port

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

Tags: , ,

Find Largest table by Row count – SQL server 2005

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

Tags: