Feeds:
Posts
Comments

Archive for May, 2010

--Check if a table exists (and drop it if does) before creating it again
if exists(select name 
			from anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
			where  type = 'u' -- http://msdn.microsoft.com/en-us/library/ms177596.aspx
			and name = 'whatever') 
	drop table whatever

--Check if a database exists
if exists (select name from master..sysdatabases where name = 'DatabaseName') select 'ok'


declare @anotherWay varchar(100)
-- if select is used then the outer bracket must be used as well
 --set @anotherWay = (select DB_NAME())
set @anotherWay = DB_NAME()
print @anotherWay

Read Full Post »

Source: http://www.bigresource.com/Tracker/Track-ms_sql-p72iL0Yw/

Hi,

I’ve a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.

————-

Hi,

I just got the answer. Thanks.

Source:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20699/20699.html

SELECT

	TextId,

	replace ( replace(TextValue, char(10),

	''), char(13), '') ModifiedTextValue

FROM

	BadTextData

Read Full Post »

Snippet:

Select DATEADD(mm, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of month
Select DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE() )+1, 0)) -- End of month

Explanation:

SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE() )+1, 0))

1) Lets discuss the below line of code (modified from above):

DATEADD(mm, [NUMBER TO BE ADDED], 0))

That will add anyting in the place of [NUMBER TO BE ADDED] to 0

(the date value 0 = 1900-01-01 00:00:00.000)

2) Now, lets discuss this (the missing part from above):

DATEDIFF(m,0,GETDATE() )+1

That will give the the difference between 0 and the the current datetime. The number returned would be a positive number because it would deduct from 1900-01-01 00:00:00.000 (given that this month is april).

So DateDiff actually shows how many more months are there from start to end dates. Eg.

Start Date February and End Date April would give 2

Start Date March and End Date April would give 1

Start Date May and End Date April would give -1

When 1 is added so that the next month is displayed

DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE() )+1, 0))

3 mircoseconds is deducted in effect from the rest of the query to return the last day of the previous month.

-1 or -2 does not anytually deduct anything

-4 deducts -3 mc as well

-5 or -6 or -7 deducts -7 mc

SQL Server can only calculate in 3 nanoseconds.

Similar topics:

Using the CONVERT function could be affecting performance sometimes.

(Check the link – http://wp.me/pAchH-19 )

The link contians a method used to optimise conversion using DateAdd & DateDiff.

Read Full Post »

Source:
http://sqlserverpedia.com/wiki/CTE_-_Overview

CTE – Overview
—————-

SQL Server 2005 introduced numerous enhancements to Transact-SQL, not the least of which is Common Table Expressions (CTE). Similar to derived tables, CTE allow a programmer to create an intermediary result set which will be further manipulated by the outer query. Unlike derived tables, CTE is defined using a WITH clause within the query that will reference it. Recursive CTE’s can reference themselves and can be very helpful in implementing common requirements involving hierarchical data. Since the definition of CTE isn’t stored in the database CTE, is a good alternative to views or user-defined functions for infrequent, “one-off” queries. If you do wish to re-use a CTE, you can enclose it in views, user-defined functions or stored procedures. CTE can also improve code readability by dividing a single complex query into simpler building blocks.

Source:
http://forums.databasejournal.com/showthread.php?threadid=50653

A CTE (common table expression) is part of an SQL statement. The basic format is:

WITH cte-name (col datatype, col datatype, etc.)
AS( sql-statement)
, cte-name1 (col datatpe, col datatype, etc.)
AS( sql-statement)
SELECT cols
FROM table-name
, cte-name1
WHERE ...

Notes:
1) col /datatypes are usually not needed for a CTE but are nice to include anyway.
2) You can have 1 or many CTE defined. I used 2 in the above example but it could be 1 or 3 or many.
3) A CTE can reference a previous defined CTE but not one after it. In the above example, cte-name1 could refer to cte-name in the FROM but cte-name could NOT refer to cte-name1.
4) Once a CTE is defined it can be referenced in a FROM (of the same SQL statement) 1 or many times.
5) In the Select statement referencing the CTE you don’t have to reference other tables. In the example above, the FROM could be just FROM cte-name.

A CTE is NOT a stored procedure. It is just an extension of an SQL Statement. As such it would go in a program where normal SQL would go.

SDas
devans561
Registered User Join Date: Jun 2009
Location: South Florida
Posts: 4

 
CREATE TABLE #subject(id int PRIMARY KEY IDENTITY(1,1),parent char(1) NULL, child char(1))
INSERT INTO #subject(child) VALUES ('A')
INSERT INTO #subject(parent, child) VALUES('A', 'B')
INSERT INTO #subject(parent, child) VALUES('B', 'C')
INSERT INTO #subject(parent, child) VALUES('C', 'D')
INSERT INTO #subject(parent, child) VALUES('D','E'); 

WITH subj(parent, child, Level)
AS
(
-- Anchor member definition
SELECT s.parent, s.child, 0 AS level 
FROM #subject s
WHERE parent IS NULL
UNION ALL
-- Recursive member definition
SELECT s.parent, s.child,level + 1 
FROM #subject s
INNER JOIN subj sj
ON s.parent = sj.child
)
-- Statement that executes the CTE
SELECT parent, child, Level 
FROM subj 

DROP TABLE #subject 
 

Read Full Post »