Splitting the Comma Separated String...

Following query is used to Split the Comma separated string into individual values.

This query can also be used to split any special character separated string.

Declare @P_text varchar(1000)

Set @P_text='1,2,3,4,5,6,7,8,9,10'

--select Charindex(',',@P_text)

--select PATINDEX('%,%',@P_text)

Declare @table as Table(Id Int)

Declare @x1 Varchar(100)

While CHARINDEX(',',@P_text) > 1

Begin

      set @x1=SUBSTRING(@P_text,0,PATINDEX('%,%',@P_text))

      Set @P_text=SUBSTRING(@P_text,PATINDEX('%,%',@P_text)+1,LEN(@P_text))

      Insert into @table(Id)

      select cast(@x1 as int)

End

Insert into @table

Select cast(@P_text as int)

select * from @table

Understanding System Databases

When we install SQL Server by default we will get 5 Databases.These can be seen under System Databases after you connect to SQL Server instance. The Default Databases are 

1. Master
2. Model
3. Msdb
4. Tempdb
5. Resourcedb

Resourcedb  is hidden database and it will not visible under system databases.

We will look at the functionality of each db individually.

Master : 

Master Database keeps the track of server installations and all other databases that are subsequently created.Master database has system catalogs that stores the information about system wide configurations such as 

•  endpoints
•  logins
•  Databases on current instance
•  Database files and usage
•  linked servers

Master database is critical to system.Operations such as creating another database,changing configuration values and modifying login accounts make modifications to Master database.

Model :

Model database is simply a template database, Every time we create a database SQL Server makes a copy of model to form the basis of the new database.

Msdb :

Msdb database is used by SQL Server Agent Service.Msdb stores the data related to following objects

• Jobs
• alerts
• log shipping
• policies
• database mail

Tempdb :

Tempdb is database is used as the work space. Tempdb is re created every time sql server restarted. It's used explicitly for temp tables created by users. Because Tempdb is re created, any objects that we create in it are lost the next time we restart the SQL Server instance.

Resourcedb :

The hidden mssqlsystemresourcedb is referred to as resource db. System objects such as system stored procedure and functions are stored here. Microsoft has created this database to allow fast and safe upgrades.

RowCount and Column Count For Each Table

Today, I got task to come up with the number of rows and number of columns for each table in given database.

This information is useful in defining the complexity while migrating database.

Below query is used to get such information.

Query:

SELECT A.NAME,COUNT(B.NAME) AS [NO.OFCOLUMNS],PT.ROW_COUNT --INTO #TEMP_COL

FROM SYS.TABLES A

JOIN SYS.COLUMNS B

ON A.OBJECT_ID=B.OBJECT_ID

JOIN SYS.DM_DB_PARTITION_STATS PT

ON A.OBJECT_ID=PT.OBJECT_ID

WHERE

PT.INDEX_ID < 2

GROUP BY A.NAME,PT.ROW_COUNT

Result:

Attaching DB from .mdf file

Find the below steps to attach a DB from .mdf file

a.       Connect to the DB server 

b.      Right click on the DB sever and select Attach..

c.       Click on the ADD button and Browse to the .mdf file

d.       Select the file and click on OK button.

          e.   Refresh on the server, you will find the DB
                              

         or fire the below query to attach the DB

           CREATE DATABASE MyAdventureWorks
           ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
           (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
           FOR ATTACH;     

CASE Statement in WHERE Clause

So many times we come across such situations where user can pass all the parameters defined in the Stored procedure or he can pass the parameter of his choice. For example, In search screens where user  can select all the parameters or any one of the given Parameters.

We have two choices to implement this logic at DB.

       1. Constructing dynamic queries depending on the parameters passed to stored procedure

       2. By writing the CASE statement in WHERE clause.

 In this article we will look at the second option.

 Use AdventureWorks2008R2

 go

 CREATE PROC SEARCHPERSON

 (

 @BUSINESSENTITYID INT=-1,

 @PERSONTYPE VARCHAR(2)='',

 @FIRSTNAME VARCHAR(50)='',

 @MIDDLENAME VARCHAR(50)=''

 )

 AS

 BEGIN

   SELECT * FROM PERSON.PERSON WHERE

   BUSINESSENTITYID= CASE(@BUSINESSENTITYID)

   WHEN -1 THEN BUSINESSENTITYID

   ELSE @BUSINESSENTITYID

   END

   AND PERSONTYPE=CASE(@PERSONTYPE)

   WHEN '' THEN PERSONTYPE

   ELSE @PERSONTYPE

   END

   AND FIRSTNAME=CASE(@FIRSTNAME)

   WHEN '' THEN FIRSTNAME

   ELSE @FIRSTNAME

   END

   AND MIDDLENAME=CASE(@MIDDLENAME)

   WHEN '' THEN MIDDLENAME

   ELSE @MIDDLENAME

   END

 END

 Now we will execute the stored procedure with different combinations of input parameters and see the         result set

 When I don’t pass any parameter it will return all the records from table

 

 When I pass the only person type parameter value

 When I pass combination of different parameters