March 26, 2009

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

March 22, 2009

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151


Let me know it it helps you.

March 20, 2009

SQL SERVER: How to enable 'Ad Hoc Distributed Queries' SQL SERVER 2005

 

Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.

We can Enable this feature by two ways:

1. SQL Server Surface Area Configuration.

2. by sp_configure option.

Lets check with first way, by SQL Server Surface Area Configuration.

Open surface Area configuration, you will get this screen:

131

 

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

132

 

Lets see second option to enable this feature with sp_configure option:

sp_configure

If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which 'Ad Hoc Distributed Queries' is not exist. To see this, we need to enable the 'show advanced options' configuration parameter.

You can enable advance options by:

sp_configure 'show advanced options',1

When we run this command we will get this message:

“Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.”

so we need to execute reconfigure command as:

reconfigure

so now if we run sp_configure again, we will get result set as follows:

133

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:

sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

so, now if we run sp_configure, we will get result as follows:

134 

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.

That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.

Let me know if it helps you in any way.

March 19, 2009

SQL SERVER: Find Duplicate Rows with Row_Number() SQL SERVER 2005


This is very known question to find Duplicate Rows from the Table and Delete Duplicate rows from the table
I came to know about "ROW_NUMBER()" of SQL SERVER 2005.
Lets see how we can use this feature:
There is one Table:
CREATE TABLE FindDuplicates(
EmpID INT IDENTITY(1,1),
EmpName VARCHAR(500)
)

INSERT some data into the table:
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('B')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('D')
INSERT INTO FindDuplicates VALUES('D')

Now we have data like:
Find Duplicate Rows
Now, we need to Find the duplicate Rows from the table. So we need to get "A", "C" and "D".
Lets see the use of Row_Number():
;With CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpName) As RowID,
EmpID,
EmpName
FROM FindDuplicates
)
SELECT *
FROM CTE
WHERE RowID > 1

Output will be like:
Find Duplicate Rows
So we can Delete Duplicate Rows as we have EmpID (Primary Key)
I Used:
PARTITION BY: which will find same rows and assign ID accordingly.
ORDER BY: In which order we want to assign ID. By Default its Ascending

February 28, 2009

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.
 
Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.
 
Example:
I have Table like:
CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)
Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'
So now my table has Data like:
Get Comma separated List
Expected Output:
Get Comma separated List
Proposed Solution:
SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1
Get Comma separated List
My Output will be:
Get Comma separated List
 
Please make comments, if this helps you in any way

February 27, 2009

SQL SERVER: REPLICATE function

Today i read SQLAuthority.com, and I found that one developer has this issue. Lets see that problem as well as the solution for the same.

There is one numeric column. User needs to make sure that all data should be of same size. Like "17.00,12.00,8.17,4.44", these all should be "17.00,12.00,08.17,04.44" like that.

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE ("string that you want TO append" ,"INTEGER VALUE" )

FIRST parameter, i need TO SET the CHARACTER, which will append it BEFORE the NUMBER.

SECOND parameter, how many times this CHARACTER should be ADD TO the NUMBER.

Example:

DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one "0" to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions

December 24, 2008

SQL SERVER: How to Add Computed Column

Today I came across scenario where I need to add one computed column. Computed column means we can manipulate one or more column from the same table and we can use this column in queries as regular column.

Lets try it:

Create One Table: tblTestComputed

CREATE TABLE tblTestComputed(
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

Lets insert some data into it:

INSERT INTO tblTestComputed(FirstName, LastName)
SELECT 'Tejas', 'Shah'
UNION
SELECT 'Hiral', 'Shah'

So now I run:

SELECT * FROM tblTestComputed

I will get output like:

Add Computed column

Now, I need display name as "Shah Tejas", "Shah Hiral" like that. so what I did is, I added one new column as:


ALTER TABLE tblTestComputed
ADD FullName AS (ISNULL(LastName,'') + ' ' + ISNULL(FirstName,''))

So, now if I run:

SELECT * FROM tblTestComputed

So, Output like:

Add computed Columns

So, I can use this new column "FullName" everywhere to display Full Name.

Computed Column is auto updated, means if FirstName OR lastName is changed then content of this column is also changed accordingly

One more thing We can not update computed column as it computed by other columns

Reference: Tejas Shah (http://www.SQLYoga.com)