Selects the no.of pages per page

create table pages(id int,name varchar(50))
create proc insert_increment
as
begin
 declare @x int
 set @x=1000
 while(@x<=100000)
 begin
  insert into pages select @x,'anil_'+cast(@x as varchar(100))
  set @x=@x+1
 end
end
execute insert_increment
select * from pages
create proc page_size_search(@PAGE_SIZE INT,@page_no int)
as
begin
--DECLARE @PAGE_SIZE INT,@page_no int
--SET @PAGE_SIZE=20
--set @page_no=7
 SELECT * FROM pages where id between (@page_no-2)*(@page_size) and (@page_size)*(@page_no)
end
exec page_size_search 20,2

GET THE GIVEN CHARACTERS AND NO OF CHARCATERS IN A GIVEN STRING

CREATE PROC GET_CHARVAL(@STR VARCHAR(100),@GSTR VARCHAR(1))
AS
BEGIN
    DECLARE @TEMP TABLE (RID INT IDENTITY, TCHAR VARCHAR(5))
    DECLARE @CNT INT, @I INT = 1
    SELECT @CNT = LEN(@STR)
    WHILE(@I <= @CNT)
    BEGIN
        INSERT INTO @TEMP
        SELECT SUBSTRING(@STR,@I,1)
        SET @I = @I + 1
    END
    SELECT TCHAR, COUNT(1) NOC FROM @TEMP
    WHERE TCHAR = @GSTR
    GROUP BY TCHAR
END

EXEC GET_CHARVAL 'SREESATYASAIBABA', 'E'

Unique records displaying

CREATE PROC UNIQ_NO
AS
BEGIN
DECLARE @TEMP TABLE (RID INT IDENTITY, TVAL INT)
INSERT INTO @TEMP
SELECT VAL FROM x ORDER BY val
DECLARE @RES TABLE (RNO INT IDENTITY, RVAL INT)
DECLARE @CNT INT, @I INT = 1, @J INT, @K INT, @CNT1 INT
SELECT @CNT = COUNT(1) FROM @TEMP
INSERT INTO @RES
SELECT TVAL FROM @TEMP WHERE RID = @I
SELECT @CNT1 = COUNT(1) FROM @RES
SELECT @J = RVAL FROM @RES WHERE RNO = @CNT1
WHILE(@I<=@CNT)
BEGIN
SELECT @K = TVAL FROM @TEMP WHERE RID = @I
IF(@J = @K)
BEGIN
DELETE FROM @TEMP WHERE TVAL = @J
END
ELSE
BEGIN
INSERT INTO @RES SELECT @K

END
SET @I = @I + 1
END
SELECT * FROM X
END

EXEC UNIQ_NO

To get the no of tables from db

create proc get_table_count as 

begin

create table #temp(database_id int,name varchar(30))

declare @res_table table(database_id int,name varchar(30),tables_count int)

insert into #temp

select database_id,name from sys.databases

declare @cnt1 int,@str nvarchar(1000),@i int,@db_name varchar(100),@db_id int

select @cnt1=count(1) from #temp

select @i=1

while(@i<=@cnt1)

begin

select @db_name=name,@db_id=database_id from #temp where database_id=@i

select @str= 'select '+cast(@db_id as varchar(10)) +','+char(39)+@db_name+char(39)+', count(1) from '+@db_name+'.sys.tables'

insert into @res_table

exec sp_executesql @str

select @i=@i+1

print @str

end

select * from @res_table

end

DBCC Commands

SQL Server – DBCC Commands

DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.
The DBCC Commands broadly falls into four categories:
Maintenance
Informational
Validation
Miscellaneous
Maintenance Commands

Performs maintenance tasks on a database, index, or filegroup.

1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE (‘AdventureWorks’,'Person.Contact’,0) 

2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)
USE AdventureWorks
DBCC DBREINDEX (‘Person.Contact’,'PK_Contact_ContactID’,80)

3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
DBCC DROPCLEANBUFFERS

4. FREEPROCCACHE – Removes all elements from the procedure cache
DBCC FREEPROCCACHE

5. INDEXDEFRAG – Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)

6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)

7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

USE AdventureWorks;
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)

8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks)Informational Commands

Performs tasks that gather and display various types of information.

1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.
DBCC CONCURRENCYVIOLATION

2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
DBCC INPUTBUFFER (52)

3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.
DBCC OPENTRAN;

4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
DBCC OUTPUTBUFFER (52)

5. PROCCACHE – Displays information in a table format about the procedure cache.
DBCC PROCCACHE

6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table
USE AdventureWorks
DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)

7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG (‘HumanResources.Employee’);

8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
DBCC SQLPERF(LOGSPACE)

9. TRACESTATUS – Displays the status of trace flags.
DBCC TRACESTATUS(-1)

10. USEROPTIONS – Returns the SET options active (set) for the current connection.
DBCC USEROPTIONSValidation Commands

Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)

2. CHECKCATALOG – Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)

3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)

5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP

6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT (‘HumanResources.Employee’)

7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE (‘HumanResources.Employee’)Miscellaneous Commands

Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)

2. TRACEOFF – Disables the specified trace flags.
DBCC TRACEOFF (3205)

3. HELP – Returns syntax information for the specified DBCC command.
– List all the DBCC commands
DBCC HELP (‘?’)
– Show the Syntax for a given DBCC commnad
DBCC HELP (‘checkcatalog’)

4. TRACEON – Enables the specified trace flags.
DBCC TRACEON (3205)

Date Format's In SQL Server

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010 SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02 SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyySELECT convert(varchar, getdate(), 104) -- dd.mm.yyyySELECT convert(varchar, getdate(), 105) -- dd-mm-yyyySELECT convert(varchar, getdate(), 106) -- dd mon yyyySELECT convert(varchar, getdate(), 107) -- mon dd, yyyySELECT convert(varchar, getdate(), 108) -- hh:mm:ssSELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM) SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyySELECT
-- yyyymmdd - ISO date format - international standard - works with any language setting convert(varchar, getdate(), 111) -- yyyy/mm/ddSELECT convert(varchar, getdate(), 112) -- yyyymmddSELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmmSELECT
-- Without century (YY) date / datetime conversion - there are exceptions! convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)SELECT convert(varchar, getdate(), 1) -- mm/dd/yySELECT convert(varchar, getdate(), 2) -- yy.mm.dd SELECT convert(varchar, getdate(), 3) -- dd/mm/yySELECT convert(varchar, getdate(), 4) -- dd.mm.yySELECT convert(varchar, getdate(), 5) -- dd-mm-yySELECT convert(varchar, getdate(), 6) -- dd mon yySELECT convert(varchar, getdate(), 7) -- mon dd, yySELECT convert(varchar, getdate(), 8) -- hh:mm:ssSELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)SELECT convert(varchar, getdate(), 10) -- mm-dd-yySELECT convert(varchar, getdate(), 11) -- yy/mm/ddSELECT convert(varchar, getdate(), 12) -- yymmddSELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmmSELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmmSELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)SELECT convert(varchar, getdate(), 23) -- yyyy-mm-ddSELECT convert(varchar, getdate(), 24) -- hh:mm:ssSELECT
-- SQL create different date styles with t-sql string functions convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm ddSELECT convert(varchar(7), getdate(), 126) -- yyyy-mmSELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyySELECT
-- SQL format datetime - - sql hh mm ss - sql yyyy mm ddSELECT [Default]=CONVERT(varchar,GETDATE(),100)-- US-Style format: 02/16/2011 SELECT [US-Style]=CONVERT(char,GETDATE(),101)-- ANSI format: 02/16/2011 SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)-- UK-Style format: 2011.02.16 SELECT [UK-Style]=CONVERT(char,GETDATE(),103)-- German format: 16/02/2011 SELECT [German]=CONVERT(varchar,GETDATE(),104)-- ISO format: 16.02.2011 SELECT ISO=CONVERT(varchar,GETDATE(),112)-- ISO8601 format: 2011-02-16T15:42:55.903SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126) substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm

How to Reset the Identity Values?

You can set the identity values using 1. DBCC CHECKIDENT(TABLENAME,RESEED,0) 
                                                                  2. Truncate table.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is a DDL, DML, DCL, TCL and DSPL concept in RDBMS world?

The Data Definition Language (DDL) includes,

CREATE TABLE – creates new database table
ALTER TABLE – alters or changes the database table
DROP TABLE – deletes the database table
CREATE INDEX – creates an index or used as a search key
DROP INDEX – deletes an index

The Data Manipulation Language (DML) includes,

SELECT – extracts data from the database
UPDATE – updates data in the database
DELETE – deletes data from the database
INSERT INTO – inserts new data into the database

The Data Control Language (DCL) includes,

GRANT – gives access privileges to users for database
REVOKE – withdraws access privileges to users for database

The Transaction Control (TCL) includes,

COMMIT – saves the work done
ROLLBACK – restore the database to original since the last COMMIT

DSPL – Database Stored Procedure Language came to relational databases relatively late in the game – and thus the languages used for triggers, event handlers, and stored procedures are completely different among the database vendors. Oracle’s PL/SQL is quite different even in statement syntax from SQL Server’s Transact SQL which in turn differs again from DB2′s Stored Procedure language. And of course given the underlying differences in DDL, DML, and DCL it is inevitable that the stored procedure languages would vary in content as well as syntax.