V I SIVA RAO

Basic CTE with a Join

2014-02-19T08:52:00.000-08:00

USE AdventureWorks2008
GO
;WITH VendorCTE AS (
 SELECT Vendor.AccountNumber, Vendor.BusinessEntityID, Vendor.Name
 FROM Purchasing.Vendor WHERE CreditRating = 2), 
 OrderCTE AS 
 (SELECT  VendorID, SUM(TotalDue) AS SumTotalDue 
 FROM Purchasing.PurchaseOrderHeader POH
 JOIN Purchasing.PurchaseOrderDetail POD
 ON POH.PurchaseOrderID  = POD.PurchaseOrderID
 WHERE UnitPrice > 25.00
 GROUP BY VendorID)

Convert Text Data to DATETIME using STUFF method

2014-02-17T22:27:00.000-08:00

--create a table emulating a fixed-width text import
DECLARE @Imports TABLE (
 DATEFIELD char(19) NOT NULL,
 DATAFIELD char(10) NOT NULL )
INSERT INTO @Imports VALUES
('2010:09:09 16:00:46','$    10.88'),
('2010:09:16 13:56:41','$    21.68'),
('2010:09:16 14:05:50','$     5.57'),
('2010:09:19 16:43:31','$    15.33'),
('2010:09:19 18:42:21','$   259.23'),
('2010:09:25 13:50:29','$    73.79'),
('2010:09:25 13:50:37','$    46.92')
 
SELECT DATEFIELD,
 CONVERT(DATETIME, STUFF(STUFF(DATEFIELD, 8,1,''),5,1,'')) AS [DATEFIELD Converted],
FROM @Imports

Search SQL Server Agent Job Steps for Specific Keyword Text or Stored Procedure

2014-02-17T22:24:00.001-08:00

USE [msdb]
GO
SELECT j.job_id,
 s.srvname,
 j.name,
 js.step_id,
 js.command,
 j.enabled 
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
 ON js.job_id = j.job_id 
JOIN master.dbo.sysservers s
 ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%KEYWORD_SEARCH%'
GO

What is the best way to auto-generate INSERT statements for a SQL Server table

2014-02-15T01:09:00.002-08:00

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []

DECLARE @Table VARCHAR(max); SET @Table = 'Queues' -- your table

DECLARE @SQL VARCHAR(max)

SET @SQL = 'DECLARE @S VARCHAR(MAX)

SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +

''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '

PRINT @S'

EXEC (@SQL)

RESULTS WILL BE LYK:-

INSERT INTO Queues([QueueName], [iSort])

SELECT 'WD: Auto Capture', '10' UNION

SELECT 'Car/Lar', '11' UNION

SELECT 'Scan Line', '21' UNION

SELECT 'OCR', '22' UNION

SELECT 'Dynamic Template', '23' UNION

SELECT 'Fix MICR', '41' UNION

SELECT 'Fix MICR (Supervisor)', '42' UNION

SELECT 'Foreign MICR', '43' UNION

...

Specify a Custom Application Name to Distinguish Excel Applications

2014-02-06T08:57:00.001-08:00

If you create Excel worksheets or PivotTables with embedded connections to SQL Server, you may want to consider changing the name of the application so you can tell which spreadsheet template someone is using. That way in Management Studio or in any logging you setup, it will your custom name.

Data Source=SQL_SERVER_OR_IP;Initial Catalog=DATABASE_NAME;Application Name=EXCEL_TEMPLATE_MASTER_REPORT;User ID=USER_NAME;Password=PASSWORD

Table Variables How and Why to use them

2014-02-06T08:55:00.002-08:00

So what are table variables and why should I use them? There’s plenty of information out there about the table variables particularly as compared to a temporary table so let me just summarize some of the differences and point out a few use cases that are ideal for developers.
First the syntax is identical except that temporary tables are CREATED with a # while table variables are
DECLARED with a @.

CREATE TABLE #TestTable (
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      SomeValue varchar(30) NOT NULL
)
 
DECLARE @TestTable TABLE (

TO FIND WHICH ARE ALL TABLES HAVING IDENTITY PROPERTY

2014-02-06T08:53:00.000-08:00

SELECT OBJECT_NAME(id) as TableName, name as ColumnName
FROM syscolumns
WHERE status = 0x80

What is Normalization?? what is the use of normalization while designing Databse

2014-02-02T08:59:00.001-08:00

rmalization or data normalization is a process to organize the data into tabular format (database tables). A good database design includes the normalization, without normalization a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.

Normal Forms

We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. Generally we organize the data up to third normal form. We rarely use the fourth and fifth normal form.

To understand normal forms consider the folowing unnormalized database table. Now we will normalize the data of below table using normal forms.

First Normal Form (1NF)

A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:
1. Separate the repeating fields into new database tables along with the key from unnormalized database table.
2. The primary key of new database tables may be a composite key
1NF of above UNF table is as follows:
Second Normal Form (2NF)

A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(means the value of field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.
The process of converting the database table into 2NF is as follows:
1. Remove the partial dependencies(A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.
2. If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.
2NF of above 1NF tables is as follows:
Third Normal Form (3NF)

A database table is said to be in 3NF if it is in 2NF and all non keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.The process of converting the table into 3NF is as follows:
1. Remove the transitive dependecies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key.Hence its value is determined, indirectly by the primary key )
2. Make separate table for transitive dependent Field.
3NF of above 2NF tables is as follows:
Boyce Code Normal Form (BCNF)

A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:
1. Remove the non trival functional dependency.
2. Make separate table for the determinants.
BCNF of below table is as follows:
Fourth Normal Form (4NF)

A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:
1. Remove the multivalued dependency.
2. Make separate table for multivalued Fields.
4NF of below table is as follows:
Fifth Normal Form (5NF)

A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:
1. Remove the join dependency.
2. Break the database table into smaller and smaller tables to remove all data redundancy.
5NF of below table is as follows:

HOW TO FIND LOCKED TABLE IN SQL

2014-01-27T08:30:00.001-08:00

QUERY TO FIND THE LOCKED TABLE:-

select  
    object_name(P.object_id) as TableName, 
    resource_type, resource_description
from
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

VERY INTERESTING TOOL TO SEARCH SQL DATABASE

2014-01-25T07:44:00.001-08:00

Here is the download link:-

http://www.2shared.com/file/EJwRuYvK/SQLSearch_11957.html

HOW TO WRITE A TRIGGER?

2014-01-25T07:37:00.000-08:00

Hi,

CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
AS
INSERT INTO T2 (PK, OldValue, NewValue)
SELECT i.PK, i.Value, d.Value
FROM INSERTED i INNER JOIN DELETED d ON i.PK = d.PK

HOW TO FIND LONGEST RUNNING QUERY IN SQL

2014-01-24T08:36:00.000-08:00

SELECT DISTINCT TOP 10

t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY

s.max_elapsed_time DESC

GO

How do I find the columns that depend on a specified CLR user-defined type or alias type?

2014-01-23T00:11:00.002-08:00

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,c.name AS column_name 
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,TYPE_NAME(c.user_type_id) AS user_type_name
    ,c.max_length
    ,c.precision
    ,c.scale
    ,c.is_nullable
    ,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID('<schema_name.data_type_name>'); 
GO

WHAT IS THE USE OF MSDB DATABASE

2014-01-21T04:58:00.001-08:00

Hi ,

I was asked with a question about the use of taking backup of MSDB system database in SQL??

I am explaining the same for viewers:-

There are Four System databases in SQL server 2000 and later versions

Master DB : The use of this DB is most important coz if this DB corrupts then whole user databases are get into affect , atleast once in a month taking BU of Master DB is good practice

MSDB : what ever the Jobs/Plans/Alerts running on the User DBs are related with the database.....Usually if we take the backup of database these jobs/plans not get back up..So it is very very important that if we need to restore the jobs then u have to take the backup MSDB

Querying the resultset of a stored proc into a temp table

2014-01-21T04:43:00.001-08:00

declare @lines table (id int identity(1, 1) primary key, line varchar(8000));

insert into @lines(line)
    exec sp_executesql N'sp_helptext ''information_schema.tables''';

select *
from @lines
order by id;

www.varuntechnologies.in

How to SELECT * INTO [temp table] FROM [stored procedure]

2014-01-21T04:35:00.000-08:00

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.d

atabases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

How to select rows with no matching entry in another table?

2014-01-21T04:32:00.001-08:00

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

www.varuntechnologies.in

VARUN TECHNOLOGIES

2013-07-05T23:58:00.001-07:00

COMPLETE SQL SERVER TRAINING

HOW TO AVOID USING CURSORS IN SQL PROGRAMMING

2013-05-18T00:31:00.004-07:00

SYNTAX:-

declare @count int,

@i int,

@productid int,

@productname varchar(500)

declare @tab1 table (sno int identity(1,1),productid int)

declare @tab2 table (produtname varchar(500),denom varchar(100))

insert into @tab1(productid) select distinct productid from products

set @count=select count(sno) from @tab1 while (@i<=@count)

begin

select @productname=productname,@denom=deno from productdetails where productid=@productid

insert into @tab2 (productname,denom) values (@productname,@denom)

select @i=@i+1

end

select * from @tab2

Note: for any clarifications/assistance feel free to contact me at chinna7485@gmail.com

GMi technologies

2012-08-05T00:20:00.000-07:00

Count repetition of rows

2012-05-05T09:07:00.001-07:00

use this syntax:-

create table mytable (mytableid int primary key, userid int,counterid int )

insert into mytable values (

1,           1,         4),
(2   ,        1 ,        7),
(3 ,         4 ,       3),
(4,           11,        9),
(5          , 11 ,       3),
(6           ,4   ,      6),
(9           ,11   ,     9)

select * from mytable

main syntax will be:-

;WITH Ordering
AS
(
SELECT mytableid,
userid,
counterid,

ROW_NUMBER() OVER (ORDER BY userid, counterid) RN
FROM mytable
)
SELECT
CurrentRow.mytableid,
CurrentRow.userid,
CurrentRow.counterid,

CurrentRow.RN -
    ISNULL((SELECT TOP 1 RN FROM Ordering O3 WHERE RN < CurrentRow.RN AND (CurrentRow.mytableid <> O3.mytableid OR CurrentRow.counterid <> O3.counterid OR CurrentRow.userid <> O3.userid) ORDER BY RN DESC), 0) Repetitions
FROM Ordering CurrentRow
LEFT JOIN Ordering O2 ON CurrentRow.RN + 1 = O2.RN
WHERE O2.RN IS NULL OR (CurrentRow.mytableid <> O2.mytableid OR CurrentRow.counterid <> O2.counterid OR CurrentRow.userid <> O2.userid )
ORDER BY CurrentRow.RN

using table twice in select stmt

2012-05-05T08:32:00.001-07:00

i've been asked a question like this:-

table structure is:

mytableid int pk
userid int
counterid int

in this table he has some data like this:

MyTableID   UserID   Counter
---------   ------- --------
1           1         4
2           1         7
3           4         3
4           11        9
5           11        3
6           4         6
...
9           11        9

now his query is, he wants use a select stmt which gives the result with max counterid and the userid should not repeat.......

so many peoples are posted so many syntaxes.........but he is not satisfied with their syntaxes.....

then...i gave the solution like this

select distinct(a.userid),
MAX(a.counterid) as counterid
from mytable a, mytable b
where a.mytableid=b.mytableid group by a.userid

ff
f
finally it works............

these all are done in famous site WWW.STACKOVERFLOW.COM

shortkuts

2012-05-03T09:04:00.002-07:00

shortkut for opening sql server ....type SSMS on run command...it is only applicable in server 2008

CTRL+U for changing of database option

ALT+N for switch over to new query window

CTRL+R for minimizing result window

njoy sql programming.....i hope these shortkuts will be useful for many of u......

my first achievement through microsoft

2012-04-15T03:01:00.000-07:00

Microsoft Certifications

2012-04-15T00:30:00.000-07:00

hai.frnds........

i've earned Microsoft Certification Yeaster day on my first attempt.......exam expereience was awesome....was very much tensed...to view my certification ..visit https://mcp.microsoft.com/authenticate/validatemcp.aspx . Transcript code is 997943 AND access code is CHINNA7485