Some Good SQL-Related YouTube Videos

You may find below link useful to learn about SQL Server Best Practises.

Some Good SQL-Related YouTube Videos

Reference: http://www.helpwithsql.com/

SQL SERVER - T-SQL New Line Character

To print information like multi line address, we have to use new line character. Different operating systems have different ways to understand this new line character. Mac supports only '\r' where as Linux supports '\n' and the most popular OS Windows needs both of these characters to understand the new line which is '\r\n'.

Few synonyms for line feed (LF) and carriage return(CR) are listed as below

Line Feed – LF – \n – 0x0a – 10 (decimal)

Carriage Return – CR – \r – 0x0D – 13 (decimal)

E.g.

Without new line character

DECLARE @NewLineCharacter CHAR(2) = CHAR(13) + CHAR(10)

PRINT 'Address1 Address2'

GO

Result:

Address1 Address2

With new line character

DECLARE @NewLineCharacter CHAR(2) = CHAR(13) + CHAR(10)

PRINT 'Address1 ' + NewLineCharacter  +'Address2'

GO

Result:

Address1

Address2

Reference: DP (http://dptechnicalblog.blogspot.com/ )

Microsoft Web Camps - Hyderabad

Hi All,

Microsoft Web Camps - Hyderabad will happen on 16th June, 2011.

Please find the details as below.

Venue:
Microsoft R&D India Pvt. Ltd. MPR 1-5, Building 3
Gachibowli Hyderabad Andhra Pradesh 500032
India

Audience(s): Architect and Pro Dev/Programmer. 

To register, click the link here:

Reference: DP (http://dptechnicalblog.blogspot.com/ )

Microsoft Tech.Ed on the Road 2011.

' Tech.Ed on The Road'  had been bringing the best of Tech.ED sessions, that happened in Bangalore during March 2011,  from past one month  in Pune, Trivandrum, Hyderabad, Chandigarh and Delhi. Microsoft's efforts to reach out to thousands of developers and infrastructure professionals have received great appreciation. With great pleasure, Microsoft wish to announce that the rich Tech.Ed content will now be available in the following cities on the dates specified.

   Ahmedabad - 11-June-2001 
   Chennai - 18-June-2011 
   Mumbai - 25-June-2011
   Kolkata - 2-July-2011
   Bangalore - 9-July-2011 

Register at http://www.communitytechdays.com/

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQL Server: Using JOIN Clause(s) with UPDATE and DELETE

SQL Server JOINs are very useful in data processing.
They are commonly used in SELECT queries but can be used with UPDATE and DELETE statements.

E.g.

IF OBJECT_ID('TableA') IS NOT NULL THEN
BEGIN
           DROP TABLE TableA
END
GO

IF OBJECT_ID('TableB') IS NOT NULL THEN
BEGIN
           DROP TABLE TableB
END
GO

CREATE TABLE TableA(
ID INT,
Name VARCHAR(100)
)
GO

CREATE TABLE TableB(
ID INT,
Name VARCHAR(100)
)
GO

Lets assume that these tables are populated with some records.

UPDATE ta
SET ta.Name = 'Join clause with Update statement'
FROM TableA ta
INNER JOIN TableB tb ON ta.ID = tb.ID
GO

DELETE ta
FROM TableA ta
INNER JOIN TableB tb ON ta.ID = tb.ID
GO

Similar to INNER JOIN, other types of JOIN clauses like LEFT JOIN and RIGHT JOIN can also be used with UPDATE and DELETE statements.

Please review this article and provide your comments.

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQL Server: Checking Existence and to Drop Temp Tables

Temporary tables are useful for storing the data temporarily during the data processing. There are two types of temp tables available in SQL Server namely  local temporary tables and global temporary tables.
The name of local temp tables prefix with '#' and global temp tables prefix with that of '##'.

To check existence of a local temparary table, drop and recreate the same use below approach.

IF OBJECT_ID('tempdb.dbo.#localTempTable') IS NOT NULL THEN
BEGIN   
           DROP TABLE #localTempTable
END IF
CREATE TABLE  #localTempTable(ID INT NOT NULL)
GO

IF OBJECT_ID('tempdb.dbo.##globalTempTable') IS NOT NULL THEN
BEGIN   
           DROP TABLE ##globalTempTable
END IF
CREATE TABLE  ##globalTempTable(ID INT NOT NULL)
GO

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQL Server : To Retrive Columns of an Object

To find the all the columns of a db objects like table  and view we can use below approach using SQL Sever meta data objects like sys.objects and sys.columns.

USE AdventureWorks2008
GO
SELECT DISTINCT c.name [Column Name]
FROM sys.columns c
INNER JOIN sys.objects o on c.object_id=o.object_id
WHERE object_id('Production.Product')=o.object_id
ORDER BY [Column Name]

Where the Production.Product is a db table and can be substituted by any other table or view.

Partial result:
-------------------------------------------------
Column Name
Class
Color
DaysToManufacture
DiscontinuedDate
FinishedGoodsFlag

Reference : DP (http://dptechnicalblog.blogspot.com/ )