Rename column name in SQL Server 2008/2005

Recently I have been asked that we can change the name of column from GUI easily but how can we do it from T-SQL. Well, this is very small thing but when I have been asked, it strikes in my mind that quite a few people doesn’t know this actually so I decided to write this very small script. This task is easiest stuff in T-SQL with the help of sp_rename system stored procedure
Have a look.
–Emps is a tablename
–Name is current column name in emp table
–want to give new name “Name11” to old name “Name” of emps table
–“Column” at last, is a key word which tells SQL Server that we
–want to change column name
EXEC sp_rename ’emps.[name]’, ‘Name11’, ‘COLUMN’
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Copy LDF and MDF file without stopping SQL Server services:

Today I have been asked by someone that I am not able to copy MDF and LDF file from our production server.  Whenever I want to copy it, I have to stop SQL Service. This is very common problem and not everybody aware with why this happens? So I tempted to write something small about this.
If you are using your database, you are not able to copy the data or log files; you can do it without even stopping services of SQL with following small script.
 
ALTER DATABASE YourDatabase
SET OFFLINE WITH ROLLBACK IMMEDIATE;
–Now Manually copy your file(s) to anywhere you want
–and set your database online
–during this process your database will be offline
–It’s not good but better than stoping SQL Server services
ALTER DATABASE YourDatabase
SET ONLINE;



Actually take a backup of database and move backup file anywhere could be good but even if you wish to copy MDF or/and LDF file, you can have above approach.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

TechEd on Road at Ahmedabad on June 20, 2009 Saturday

I was very disappointed when I was not able to attain TechEd event at Hydrabad last time, fortunately One of the good gala event is going to happen in Ahemedabad, heart of Gujarat. Many technology leaders is going to come from all over Gujarat so it would be great opportunity to meet them in-person. I am really eager to meet both the SQL Server MVPs Mr. Pinal Dave (SQLAuthority.com) and Mr. Jacob Sebastian (BeyondRelational.com). 
Here is the highlight of that event.
Date and Time:
June 20, 2009 Saturday 1:45 PM


Agenda:
1:45 – Keynote By Pinal Dave & Jacob Sebastian
2:00 – Microsoft Exchange Server 2010,Windows Server 2008,Virtualization
2:45 – SQL Server Best Practices by Pinal Dave
3:30 – High Tea, Networking Opportunity and Gift Giveaway
4:00 – Understanding Query Execution Flow and Optimization Tips by Jacob Sebastian
4:45 – Data compression in SQL Server by Pinal Dave and Jacob Sebastian

Location:
Rock Regency
C.G. Road, Lal Bunglow,
Ahmedabad

If you have missed TechEd India 2009 at Hyderabad you can now attend the same kind of event inAhmedabad, India on June 20, 2009 Saturday. TechEd on Road is organized in Ahmedabad at Rock Regency a prime location in middle of the city. This event is going to be largest event happening with the brand TechEd in Ahmedabad. If you are technology enthusiast and interested in meeting great people from all over state, this is one event marked for you.

Do show up little early at event around 1:30 PM, if you want good seats and want to meet two MVPs (Pinal Dave and Jacob Sebastian), who will be attending this event. Entry is first come and first serve. Arrive early enough to secure your entry to this FREE event.

Please refer the flayer below for agenda, map of venue, sponsors and giveaway. Yes, there will be lots of gifts and giveaways for everybody.  You can participate in this gala event in many different ways – attendee, volunteers, sponsors. Feel free to send email to pinal ‘at ‘ sqlauthority.com if there are any question regarding this event.

You can find more details of the event at
Hope to see you there!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Find first and last Friday of every month in SQL Server 2008/2005

Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1st Jan 2006 to 31st Dec 2020. This is bit a challenging job. There are quite a few ways to do so like temp table, CTE etc. and I have had my hand in CTE to solve this problem. Let us see the script itself.
WITH CTE AS
(
select CONVERT(datetime,’01/06/2006′) as dt,  datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,’12/31/2020′)
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt



Quite a simple to do so in CTE but if you will try to attempt above query, you will be greeted by following error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.



How to fix that annoying error? It is crossing default recursion limit of SQL Server which is 100 and above CTE will iterate much more time than 100 so obviously you are going to face an error. All you need to do is, just have one clause after above CTE, OPTION (MaxRecursion 0).  So finally query will looks like
WITH CTE AS
(
select CONVERT(datetime,’01/06/2006′) as dt,  datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,’12/31/2020′)
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt
OPTION (MaxRecursion 0)
Enjoy!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Read XML node on same level inSQL Server 2008/2005

I have written one article few days back for loading relational data in SQL Server from XML file. I got very good response from that article so I am tempted write another one on XML. Suppose you have different occurrences of data within same level of XML file, how would you query or populate in SQL Server? Let us have a look at it.
–create one variable with XML data
–you can have .XML file saved on your disk
–and can use the same query
DECLARE @xData xml;

SET @xData =
<chem>
   <employee>
    <id>ch001</id>
    <FName>Ritesh</FName>
    <LName>Shah</LName>
    <dept>MIS</dept>
    <dept>Network</dept>
    <dept>DBA</dept>
 </employee>
   <employee>
    <id>ch002</id>
    <FName>Rushik</FName>
    <LName>Shah</LName>
    <dept>Programmer</dept>
    <dept>Developer</dept>
 </employee>
</chem>’
–suppose employee work in more than one department
–how would you list employee with all concern department?
–ofcourse with below give query 😉
SELECT outr.ID,outr.FName, outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            Inr.i.value(‘FName[1]’,‘varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,‘varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
–or you can just list of department based on ID
SELECT outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            –Inr.i.value(‘FName[1]’,’varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,’varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
where outr.ID=‘ch001’
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Find fully qualified path in SQL Server 2008/2005

It has been observed so many times that when programmer creates table or stored procedure in SQL Server and when tries to run it with its name, they face an error that object doesn’t exists. If you are sure that you have created object even though it is not available even after you are having full permission in database than there is a case that, object owns by particular schema and you are not referring it while calling.
There are few schema in Adventureworks database and each owns few tables. If you try to use table name only in SELECT statement, you will face an error given below.
–try running below query
use AdventureWorks
go
select * from Address
–it will show you below given error
–Msg 208, Level 16, State 1, Line 1
–Invalid object name ‘Address’.
–reason is Address table is owned by Person schema
–so if you try like below, you are done.
select * from Person.Address
–Person.Address
–we have specified schema name and table name in above query
–but the good practise is to specify ServerName.DatabaseName.Schemaname.ObjectName
–this is called fully qualified path and to find out fully qualified path of object
–use below give T-SQL statement.
select ‘[‘+@@SERVERNAME+‘]’  + ‘.’ +DB_NAME() + ‘.’ +ss.name+‘.’+so.name as ‘Full Qualified Name’ from sys.objects so join sys.schemas ss
on so.schema_id=ss.schema_id where so.name=‘Address’



Enjoy!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Change collation in SQL Server 2005/2008

Collation: It’s nothing but the set of rules that defined how data is stored and compared.
SQL Server default uses collation “SQL_Latin1_General_CP1_CI_AS” which is:
–The ISO code page 1252.
–The dictionary order, case-insensitive character sort order.
–The General Unicode collation



If you want to change this default collation to your own regional collation for your database, you can use below given script.
Alter Database AdventureWorks –give your database name
Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply
 

You may face the error while running above script sometime if your database is in multi user mode, in that case you have to set your database to single user mode, change collation and set your database to multi user mode. Look at the script below.



 Alter Database AdventureWOrks set single_user
Alter Database AdventureWorks –give your database name
Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply
Alter Database AdventureWOrks set multi_user



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com