How to find Columns name in a sql table

SELECT
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM  
    sys.columns c
INNER JOIN
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('TableName')

Note: If you are using nvarchar as datatype, it will give you another column with same name but datatype of sysname. Also all the Max length for nvarchar datatype will be shown as double of original value.

Example:

Let's create a table and see how it work with nvarchar and varchar datatype to show it.

CREATE Table ContactAddress
(ContactID int IDENTITY(1,1) Primary Key,
FullName varchar(50),
Address1 varchar(40),
Address2 varchar(40),
City varchar(20),
Zip varchar(5),
[State] Char(2),
Country Varchar(30)
)

--And Create another table with similar structure but this time change zip data type from varchar to nvarchar

CREATE Table ContactAddress1
(ContactID int IDENTITY(1,1) Primary Key,
FullName varchar(50),
Address1 varchar(40),
Address2 varchar(40),
City varchar(20),
Zip nvarchar(5),
[State] Char(2),
Country Varchar(30)
)

And here's the result after you run the query:

You will also notice that their is another column "Zip" with sysname datatype.

COALESCE Function in TSQL

If you are new to TSQL or SQL in general, it’s always good idea to read about functions which we rarely use. Someday it might come handy (Who know it’s a tricky question and people want to trip you down memory lane.)

So let’s try to understand COALESCE function.

If you are not a big fan of msdn site, I don't blame you!!!! However it will give you a great starting point to do your research.

According to MSDN (http://msdn.microsoft.com/en-us/library/ms190349.aspx)

COALESCE says that it will "Returns the first nonnull expression among its arguments." 

Let’s go through some example:

Suppose we have a table called Address with three columns (Home Number, Work Number and Cell Number) along with all other columns. Also suppose that none of these column (phone number columns) are NOT NULL (meaning it’s up to user to enter information if he or she likes it). So we can have some users who entered phone number under Home and some under Work and still few under cell number and same never bothered to enter any number.

Let’s assume that we want to see this information in our select output along with other information.  Our preference choice is Home number first, Work number second and cell number third or it can be cell number first, home number second and work number third. Also we just want to show only 1 number at any given time. So how do we do this in our TSQL.

Here’s come-à COALESCE

Let’s go with our first choice (home then work then cell)

Select COALESCE (Home Number, Work Number, Cell Number) AS Phone Number from Address

This statement will look at home number first, if there is home number, it will return that number. If home number is missing, it will jump to work number. If work number is present it will return as output otherwise it will jump to cell number. So our result will have one number from these three columns.

COALESCE (444-444-4444, 555-555-5555, 666-666-6666) will give you-à 444-444-4444

COALESCE (NULL, 555-555-5555, 666-666-6666) will give you à 555-555-5555

COALESCE (NULL, NULL, 666-666-6666) will give you à 666-666-6666

More generic example:

COALESCE(1, 2, 3, 4, 5, NULL)à 1

COALESCE(NULL, NULL, NULL, 1, 2, 3) à 1

COALESCE(9, 8, 7, 6, 5, NULL)à 9

COALESCE(NULL, NULL, NULL, 4, 5, NULL)  à 4

What happen if none of the three columns have any number? In that case it will return a NULL value.

COALESCE(NULL, NULL, NULL, NULL) à NULL

Creating Constraint on Table to check for data integrity

Creating Constraint on Table to check for duplicate values

Sometime we are asked to make sure that there is no duplicate data in our table based on certain columns combination. There are many ways to do this.

The first thing that will come to our mind is TRIGGER.

Well Trigger are good but its not always  best solution, particularly in our case here.

So let see how we can do it.

Create a table and insert some data.



CREATE TABLE [dbo].[Product](
[ProductID] [int],
[ProductName] varchar(100) NOT NULL,
[IsActive] Bit NOT NULL,
[RetiredDate] Datetime2,
[MinPrice] Float,
[MaxPrice] Float
)

Just to show how constraint works, I am not adding any Primary Key or Foreign Key relation with other tables.

Let's insert some data in this table.



  Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 100.00, 500.00), (102, 'Mountain-rugged',1,299.99,999.99), (103, 'Mountain', 0, 199.99,399.99)

Check to make sure you have data in table you just inserted.


SELECT * FROM Product

Now let's say that our ProductID + ProductName+ IsActive has to be unique row in our table. So in this case based on this, there can be only two combination (0 or 1 for active and inactive values)

Suppose we do not want user to enter a value of above combination because we are considering this to be a unique values.



"101--Mountain-Bike--1"

Right now with no constraint, we will be able to enter this value.


 Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 59.99, 199.00)

Here, I have just changed MinPrice and MaxPrice but productID, productName and IsActive are same, which should not be allowed if we want to maintain data consistency.


To prevent these type of data, we can create trigger based on unique combination of three columns where we want to maintain data integrity.


To do so, we have to remove last inserted row from our table. Let's go and remove that row.

Method 1:


   
ALTER TABLE dbo.Product
ADD CONSTRAINT unique_PID_PNAME_Active UNIQUE([ProductID],[ProductName] ,[IsActive])

Method 2:


CREATE UNIQUE INDEX uq_product
  ON dbo.Product([ProductID],[ProductName] ,[IsActive]);

Method 3: which is trigger (not recommended but you can still do it)

CREATE TRIGGER dbo.BlockDuplicatesproduct ON dbo.product

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

IF NOT EXISTS (

SELECT 1

FROM inserted AS i

INNER JOIN dbo.product AS t ON i.productID = t.productID

AND i.[ProductName] = t.[ProductName]

)

BEGIN

INSERT dbo.product (

[ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

)

SELECT [ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

FROM inserted;

END

ELSE

BEGIN

PRINT N'';

END

END

GO;

Now test and see it works for you or not.

Inserting Data in temp table from Another table

Sometime when we update certain table, its always a good idea to save the entire data into another temporary table which will hold unless we delete that temp table.

Let's say we are working on a Employees table and we need to manually update certain rows or columns for this table.

To do so, let's back them up somewhere for our reference (incase we mess up whole table..I have done few times in beginning!!)


Select * INTO tmpEmployees
FROM Employees

This statement will create a table "tmpEmployees" which will have same structure as  Employees table.


Even we when we close current session, this table will remain there in our database.


However, if you do not want to retain your temp table after you are done with you update, you can create #tmp table


Select * INTO #tmpEmployees
FROM EMPLOYEES


If table already exist, than we have to use this


INSERT INTO #tmpEmployees 
Select * FROM Employees


:Kumar

How to pass a value or no value inside a T-SQL or stored Procedure

How to pass a value  or no value inside a T-SQL or stored Procedure

Let's say we have a table called state which hold three column (StateId, StateCode and StateName). If you don't have this table in your test database, go ahead and make one.

CREATE TABLE [dbo].[State]
(
StateCodeID int IDENTITY(1,1),
StateCode varchar(2) NOT NULL,
[State] varchar(25) NOT NULL
);

Let's insert some data in this table. You can copy these script and insert into your table.

Insert INTO State VALUES
('AK', 'Alaska')
,('AL', 'Alabama')
,('AR', 'Arkansas')
,('AZ', 'Arizona')
,('CA', 'California')
,('CO', 'Colorado')
,('CT', 'Connecticut')
,('DC', 'Dist. of Columbia')
,('DE', 'Delaware')
,('FL', 'Florida')
,('GA', 'Georgia')
,('HI', 'Hawaii')
,('IA', 'Iowa')
,('ID', 'Idaho')
,('IL', 'Illinois')
,('IN', 'Indiana')
,('KS', 'Kansas')
,('KY', 'Kentucky')
,('LA', 'Louisiana')
,('MA', 'Massachusetts')
,('MD', 'Maryland')
,('ME', 'Maine')
,('MI', 'Michigan')
,('MN', 'Minnesota')
,('MO', 'Missouri')
,('MS', 'Mississippi')
,('MT', 'Montana')
,('NC', 'North Carolina')
,('ND', 'North Dakota')
,('NE', 'Nebraska')
,('NH', 'New Hampshire')
,('NJ', 'New Jersey')
,('NM', 'New Mexico')
,('NV', 'Nevada')
,('NY', 'New York')
,('OH', 'Ohio')
,('OK', 'Oklahoma')
,('OR', 'Oregon')
,('PA', 'Pennsylvania')
,('RI', 'Rhode Island')
,('SC', 'South Carolina')
,('SD', 'South Dakota')
,('TN', 'Tennessee')
,('TX', 'Texas')
,('UT', 'Utah')
,('VA', 'Virginia')
,('VT', 'Vermont')
,('WA', 'Washington')
,('WI', 'Wisconsin')
,('WV', 'West Virginia')
,('WY', 'Wyoming');

So this script should enter 51 rows in our table.


Coming to the point, lets say someone ask you write a stored procedure where he need stateCodeID and State value if user enter statecode, if not he want to see all the values.

Let's go ahead write this script

CREATE PROCEDURE [dbo].[usp_GetStateInformation] 
(
@Statecode VARCHAR(2) = NULL
)
AS
Begin
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SELECT STATECODE
,[STATE]
FROM [STATE]
WHERE STATECODE = ISNULL(@Statecode, StateCode)
ORDER BY StateCode
END;


"ISNULL(@Statecode, StateCode)" this is important function

What ISNULL do is that if there is no value in our variable @StateCode, it will pass null value in select query and it will return all the rows.

Now try executing this stored procedure as

Exec usp_GetStateInformation 'TX'

In this you will get statecode and state value only for 'TX'

And like this

Exec usp_GetStateInformation

In this you will get all the statecode and state value for all rows (51)

SQL: Intersect, Except and Union

Let's say we have Table A with ID Column  and its values are (1,2,3,4,5) and Table B with its ID Column and values (3,4,5,6,7).

Let's say we want to write different queries giving us something like this:
Find me all the value which common in both table? (3,4,5)

Find me all unique value in both table? (1,2,3,4,5,6,7)

Find me all values which are unique in table A and that are also not in Table B (1,2)

Find me all values which are unique in both Table (1,2,6,7)

Fire your SQL Server and let's create these table and insert data in them and see how it work.


CREATE Table TableA
(ID int);
GO
INSERT INTO TableA VALUES (1),(2),(3),(4),(5);
GO
CREATE Table TableB
(ID int);
GO
INSERT INTO TableB VALUES (3),(4),(5),(6),(7);

Find me all the value which common in both table? (3,4,5)

To find values in both table, take a look at the picture above. The question ask: what are the values which are common to both table? What word comes to your mind? Intersect? right.. there is keyword "INTERSECT" in sql language.

Try this


SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;

Result

ID

3

4

5

Find me all unique value in both table? (1,2,3,4,5,6,7)

To find value which are unique in both table, take a look at picture above again. think about it. programming and database is all about logic.

Select * from Table A

Union

Select * from Table B

Result

ID

1

2

3

4

5

6

7

If you do UNION ALL, we will get repeat value of common number in both table like this.

ID

1

2

3

4

5

3

4

5

6

7

Find me all values which are unique in table A and that are also not in Table B (1,2)

In this case we have to use EXCEPT keyword.

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB;

Result

ID

1

2

Find me all values which are unique in both Table (1,2,6,7)

For this query we have to really think hard.. think about UNION and EXCEPT combining somehow?

SELECT * FROM TableA

Union 

Select * FROM TableB

EXCEPT

SELECT * FROM TableA

INTERSECT

SELECT * FROM TableB;

Result

ID

1

2

6

7

Hope this help you.

Few things to remember about these queries are:

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

1.   The number and the order of the columns must be the same in all queries.

2.   The data types must be compatible.

IF you don't follow this rule, will will get error message.

Cheers!!!

SubQuery and JOIN: A beginner concept

Microsoft define subquery  as "A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. "

Let's look at an example of this subquery and how can we avoid subquery all together by using JOIN functionality.

For this example I am going to use AdventureWork database.

We are going to look at some sales table and show you how you can avoid subquery by using JOIN.

Lets say we want to look at SalesOrderID, SalesOrderDate and Maximum UnitPrice from our sales table
(SalesOrderDetail and SalesOrderHeader ).

So first write down what we want.

--Select SalesOrderID, SalesOrderDate and Maximum UnitPrice from Table SalesOrderDetail and Table SalesOrderHeader 


So let's go ahead and write on query using subquery



SELECT  Ord.SalesOrderID
,Ord.OrderDate
,(
SELECT max(OrdDet.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderDetail OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID
) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord;


Now let's try writing down similar query which will give the same result.


SELECT  Ord.SalesOrderID
,Ord.OrderDate
,max(OrdDet.UnitPrice) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord
 JOIN AdventureWorks.Sales.SalesOrderDetail OrdDet ON Ord.SalesOrderID = OrdDet.SalesOrderID
GROUP BY Ord.SalesOrderID
,Ord.OrderDate;

If possible try avoiding use of subquery in your T-SQL.

Here is execution plan for the 2 queries.