Select Multiple Table Columns with JOINS

In one of our project(a kind of ETL project), there was a requirement to map the a single table columns with multiple table column. I searched in many forums and finally from MSDN learned a new implementation using Joins.

Here we go:

These are the three tables and their columns.

MasterTable:
ID
Name
Value
Area
State
County
CreatedDate
ModifiedDate

State:
ID
StateCode
State
CreatedDate
ModifiedDate

Area:
ID
AreaCode
Area
CreatedDate
ModifiedDate

The Area and State tables are like KeyValue pair tables, in which the AreaCode and StateCode represents the integer value for corresponding string values. The requirement is to create a View for MasterTable, in which the Area should be matched with AreaCode, and State to be matched with StateCode.

Code Snippet:

CREATE VIEW [dbo].[vMaster]
AS
SELECT m.ID,
m.Name,
m.Value,
a.AreaCode,
s.StateCode,
m.County,
m.CreatedDate,
m.ModifiedDate

FROM MasterTable m LEFT JOIN Area a
ON a.Area = m.Area LEFT JOIN State s
ON s.State = m.State

I think this might be useful. Don't feel shy to drop in your comments.

Happy coding. Have a good day.

Reseed Identity Column in a Table

Today in our web application we have to make a major release to production. Everything went fine but we have lot of test entries in the production. Our identity column of the User table increased from 1342 to 1456 because of the test entries. Myself and a friend of mine read an article for resetting the Identity Column. So after deleting the entries this is what you have to do.


Resetting Current Identity Value to New Value

use DBNAME

go
dbcc checkident(TABLENAME, reseed,seed value)

For Eg:

dbcc checkident(dbo.[User],reseed,1342)

which served my purpose.

To Get Current Identity Value

Also this seems to be useful. This gives the current identity value of the table.

use DBNAME

go
dbcc checkident(TABLENAME, noreseed)

For Eg:

dbcc checkident(dbo.[User],noreseed)

Please let me know your thoughts. Have a good day.

Basic SQL System Stored Procedures to be known by every Developer

There are large number of System Stored Procedures. Here are few stored procedures that should be known by every developer who work with Databases. And these stored procedures will be very handy at times(Particularly for me.My favorites :) ).


sp_Help:

Gives the structure of the table.

Syntax: sp_Help

Eg: sp_Help 'dbo.tblProduct'



sp_Helptext:

Gives the definition of the objects like Stored Procedures(both user defined and system defined), Triggers, Functions and etc.

Syntax: sp_Helptext

Eg: sp_Helptext 'dbo.sp_tblProduct_Insert'



sp_Depends:

Gives all the Stored Procedure and View associated with the Table.

Syntax: sp_Depends

Eg: sp_Depends 'dbo.tblProduct'



sp_Spaceused:

Gives information on the size database objects.

Syntax: sp_Spaceused

Eg: sp_Spaceused 'dbo.tblProduct'

I can assure you these System Stored Procedures will help you in Development.

Please let me know your thoughts. Have a wonderful time.