SQL SERVER date functions & some common points

1. To know current year

 

    select datepart("yy",getdate())

    select year(getdate())

2. To know current month

    

    select datepart("mm",getdate())

    select MONTH(GETDATE())

 

1. How to clear procedure cache
DBCC FREEPROCCACHE
we can use sp_recompile system procedure to recompile the stored procedure
at the time of creating the sp we can add a tag WITH RECOMPILE. This causes the stored procedure to be compiled every time when it is called.

2. How to view the contents of a stored procedure
sp_helptext

3. Difference between Logins and Users
Loing is to establish a connection to the database where as an user is to access a particular database in a sql server.

4. What is sql server Role
Roles contain specific properties & an user can belong to one or more roles.

5. delete duplicates: emp
ex: ID Name Value
1 a 10
2 b 20
3 c 30
4 b 20
5 d 40

DELETE FROM emp
where
ID NOT IN (SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name AND e2.Value=emp.Value)

(or)

DELETE FROM emp
WHERE
ID > (
SELECT MIN(ID) FROM emp e2 WEHRE e2.Name = emp.Name AND e2.Value = emp.Value
)

(or)

DELETE FROM emp
WHERE
ID <
(
SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name and e2.Value = emp.Value
)



case 2: If all the columns have same value for ex:
Name Value
1 10
1 10
2 20
2 20

in this scenario

select * into into #temp
(select distinct Name, Value FROM emp)
as T

delete from emp
insert into emp select * from #temp


solution2: Instead of using temp tables, we can do one more thing here.
create another table with this structure. Now add a new column called Id to this table with identity one.
Insert values from previous table to this new table. Drop the previous table. Now this case became case 1.


6. Retrieving Row number
SELECT ROW
select row_number() over(order by ename asc) as rownum from duptest


7. Retrieving Nth highest salary using co-related subquery:

SELECT * FROM emp e1
WHERE
0 = (select count(distinct sal) from emp e2 where e2.sal> e1.sal)

Check if a table already exists in SQL SERVER

1. if object_id('TABLE1') is not null

print 'Table exists'

2. if exists (select * from sysobjects where id = object_id('TABLE1') and OBJECTPROPERTY(id, N'IsUserTable') = 1 )

print 'Table exists'

3. if exists (select * from sysobjects where id = object_id('TABLE1') and xtype= 'U' )

print 'Table exists'

-----------------------------------------------------------------------------------------

How to test if a column for a table already exists?

select * from information_schema.columns WHERE TABLE_NAME ='Table1' and COLUMN_NAME ='col1'

SQL SERVER 2000 Maximum Capacity Specifications

Object	SQL Server 2000
Batch size	65,536 * Network Packet Size1
Bytes per sort string column	8,000
Bytes per text, ntext, or image column	2 GB-2
Bytes per GROUP BY, ORDER BY	8,060
Bytes per index	9002
Bytes per foreign key	900
Bytes per primary key	900
Bytes per row	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB
Clustered indexes per table	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement
Columns per index	16
Columns per foreign key	16
Columns per primary key	16
Columns per base table	1,024
Columns per SELECT statement	4,096
Columns per INSERT statement	1,024
Connections per client	Maximum value of configured connections
Database size	1,048,516 TB3
Databases per instance of SQL Server	32,767
Filegroups per database	256
Files per database	32,767
File size (data)	32 TB
File size (log)	32 TB
Foreign key table references per table	253
Identifier length (in characters)	128
Instances per computer	16
Length of a string containing SQL statements (batch size)	65,536 * Network packet size1
Locks per connection	Max. locks per server
Locks per instance of SQL Server	2,147,483,647 (static) 40% of SQL Server memory (dynamic)
Nested stored procedure levels	32
Nested subqueries	32
Nested trigger levels	32
Nonclustered indexes per table	249
Objects concurrently open in an instance of SQL Server4	2,147,483,647 (or available memory)
Objects in a database	2,147,483,6474
Parameters per stored procedure	2,100
REFERENCES per table	253
Rows per table	Limited by available storage
Tables per database	Limited by number of objects in a database4
Tables per SELECT statement	256
Triggers per table	Limited by number of objects in a database4
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered

Parent Child Ids (Infinite Depth)

Input format:


Output:

CREATE TABLE [dbo].[tblSkillSet](

[SkillId] [int] IDENTITY(1,1) NOT NULL,

[SkillName] [varchar](250) NOT NULL,

[ParentSkillId] [int] NOT NULL CONSTRAINT [DF_tblSkillSet_ParentSkillId] DEFAULT (0),

[Depth] [int] NULL,

[Lineage] [varchar](100) NULL,

CONSTRAINT [PK_tblSkillSet] PRIMARY KEY CLUSTERED

(

[SkillId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--------------------------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SelALLParentsAndChilds]

AS

SET NOCOUNT ON

Create table #tblSkills(SkillId INT, SkillName VARCHAR(8000))

CREATE TABLE #stack (Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

CREATE TABLE #stack1 (SkillId INT ,Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

DECLARE @maxId INT

DECLARE @strskill VARCHAR(8000)

DECLARE @colskill VARCHAR(8000)

DECLARE @parentId INT

DECLARE @level INT, @line char(20)

Declare @SKillName VARCHAR(50)

SET @strskill=''

DECLARE @PSID INT

DECLARE @OuterPSID INT

DECLARE @PSName VARCHAR(100)

DECLARE cur_skill1 cursor for SELECT SkillId from tblSkillSet WHERE ParentSkillId = 0

OPEN cur_skill1

FETCH next from cur_skill1 into @PSID

SET @OuterPSID = @PSID

WHILE @@FETCH_STATUS=0

BEGIN

DELETE FROM #stack1

SELECT @SkillName =SkillName from tblSkillSet WHERE ParentSkillId =@PSID

INSERT INTO #stack VALUES (@PSID, @SkillName, 1)

SELECT @level = 1

WHILE @level > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE [level] = @level)

BEGIN

SELECT @PSID = Parent_Id FROM #stack WHERE [level] = @level

SELECT @line = space(@level - 1) + @PSID

PRINT @line

DELETE FROM #stack WHERE [level] = @level AND parent_id = @PSID

INSERT #stack

SELECT SkillId , SkillName , @level + 1 FROM tblSkillSet WHERE ParentSkillId = @PSID

DECLARE @strinnerSkillName VARCHAR(100)

SET @strinnerSkillName = ''

IF exists (SELECT * FROM #stack1 WHERE SkillId = @PSID)

BEGIN

SET @strinnerSkillName = (SELECT SkillName from #stack1 WHERE SkillId=@PSID) + '>>'

INSERT #stack1

SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName, @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

ELSE

BEGIN

SET @strinnerSkillName = (SELECT max(SkillName) from tblSkillSet WHERE SkillId=@PSID) + '>>'

INSERT #stack1 SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName , @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

IF @@ROWCOUNT > 0

BEGIN

SELECT @level = @level + 1

END

IF exists ( SELECT 'sometext' from #stack WHERE parent_id=@PSID)

SELECT @level=0

END

ELSE

SELECT @level = @level - 1

END -- WHILE

SET @PSName = (SELECT skillName from tblSkillSet WHERE SkillId=@OuterPSID)

INSERT INTO #tblSkills VALUES(@OuterPSID,@PSName)

INSERT INTO #tblSkills SELECT skillId,skillName from #stack1

FETCH next FROM cur_skill1 INTO @PSID

SET @OuterPSID = @PSID

END --for cursor

CLOSE cur_skill1

DEALLOCATE cur_skill1

SELECT * FROM #tblSkills

--------------------------------------------------------------------------------------------------------------------------