--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
Archive for May, 2010
I’ve a text column (text datatype) that contains carriage return and line feed.
Syntax-wise, how can I replace these by a space?
I just got the answer. Thanks.
SELECT TextId, replace ( replace(TextValue, char(10), ''), char(13), '') ModifiedTextValue FROM BadTextData
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
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):
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.
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.
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.
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 ...
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.
Registered User Join Date: Jun 2009
Location: South Florida
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