Tuesday, March 31, 2009

How to alter a User Defined Data Type?

 

The only way to do it is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously. The problem is that you can drop the UDDT if it is in use.(PN: this is same with SQL Server 2008)

In case of the UDDT is in use you need to follow these steps.

1. Allocate different data type for the fields which are using UDDT. Rather than allocating arbitrary data type it is better to allocate data type of the UDDT, so that there won’t be any issue with the existing data.

2.Drop Stored Procedures which are using UDDT. You can drop them by using following T-SQL. However, you need to get the scripts of those SPs to create them later.

DECLARE @dropsps VARCHAR(8000)
SET @dropsps = ''
SELECT  @dropsps = @dropsps + ',' +
        OBJECT_NAME(id)
FROM    sys.syscomments
WHERE   TEXT LIKE '%latitude%'
        AND OBJECT_NAME(id) LIKE 'usp%'
SET @dropsps = 'DROP PROC ' + RIGHT(@dropsps, LEN(@a) - 1)
EXEC ( @dropsps

    )

3. Drop the UDDT

4. Create new UDDT

5. Allocate new UDDT to the fields which had previous UDDT.

6. Create Dropped Stored Procedures

You can use following script and you can change the first variable value according to your requirement. This script was tested for few data types.

SET NOCOUNT ON

DECLARE @udt VARCHAR(150)

DECLARE @udtschema VARCHAR(150)

DECLARE @newudtschema VARCHAR(150)

DECLARE @newudtDataType VARCHAR(150)

DECLARE @newudtDataSize smallint

DECLARE @OtherParameter VARCHAR(50)

SET @udt = 'Name' -- Existing UDDT

SET @udtschema = 'dbo' -- Schema of the UDDT

SET @newudtDataType = 'varchar' -- Data type for te new UDDT

SET @newudtDataSize = 500 -- Lenght of the new UDDT

SET @newudtschema = 'dbo' -- Schema of the new UDDT

SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL

DECLARE @Datatype VARCHAR(50),

@Datasize SMALLINT

DECLARE @varcharDataType VARCHAR(50)

DECLARE @Schemaname VARCHAR(50),

@TableName VARCHAR(50),

@FiledName VARCHAR(50)

CREATE TABLE #udtflds

(

Schemaname VARCHAR(50),

TableName VARCHAR(50),

FiledName VARCHAR(50)

)

SELECT TOP 1

@Datatype = Data_type,

@Datasize = character_maximum_length

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Domain_name = @udt

SET @varcharDataType = @Datatype

IF @DataType Like '%char%'

AND @Datasize IS NOT NULL

AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'

)

BEGIN

SET @varcharDataType = @varcharDataType + '('

+ CAST(@Datasize AS VARCHAR(50)) + ')'

END

INSERT INTO #udtflds

SELECT TABLE_SCHEMA,

TABLE_NAME,

Column_Name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Domain_name = @udt

DECLARE @exec VARCHAR(500)

DECLARE alter_cursor CURSOR

FOR SELECT Schemaname,

TableName,

FiledName

FROM #udtflds

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType

EXECUTE ( @exec

)

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

END

CLOSE alter_cursor

SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'

EXEC ( @exec

)

SET @varcharDataType = @newudtDataType

IF @newudtDataType Like '%char%'

AND @newudtDataSize IS NOT NULL

AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'

)

BEGIN

SET @varcharDataType = @varcharDataType + '('

+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'

END

SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '

+ @varcharDataType + ' ' + @OtherParameter

EXEC ( @exec

)

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema

+ '].[' + @udt + ']'

EXECUTE ( @exec

)

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

END

CLOSE alter_cursor

DEALLOCATE alter_cursor

SELECT *

FROM #udtflds

DROP TABLE #udtflds

Thursday, March 19, 2009

Application Name & SQL Server Profiler

SQL Server Profiler is a tool which you can use to gather information about what are the events happening at the SQL Server.  Following is the sample which you will get from the Profiler Trace file.

image

If you pay attention to the Application Name Column (3rd Column form your left hand side), You can see there are entries. For the First four rows, you can see a SQL Prompt and next four lines you will see Microsoft SQL Server Management Studio. This is due to the fact that those applications are using this particular instance of SQL server. If you look closer, you will see that next 3 lines has a entry name .Net SqlClient Data Provider.

Obvious question is what is this application. Well, this is because Application Name is not specified by the application. You have to set it from the client application by adding another parameter to the connection string.

string connectionstring = "Data Source=.; Integrated Security=SSPI; Initial Catalog=DB;Application Name=MyApplication";

NB: Whenever the Application Name is not specified it will use default application Name, .Net SqlClient Data Provider.

After if you analyze the Profiler trace again you will see the correct application name.

image

This very important option as this will allow you to analyze your Profiler Trace for application wise. Also, you can use filter option for Application Name in Profiler to filter by application.

Database Implications if IBM Acquires Sun

Reported or rumored merger discussions between IBM and Sun are generating huge amounts of discussion today (some links below). Here are some quick thoughts around the subject of how the IBM/Sun deal — if it happens — might affect the database management system industry.

  • IBM is already serious about supporting multiple database management systems. DB2 on open systems is IBM’s flagship DBMS. But DB2 on mainframes and at least one flavor of Informix seem to be getting maintained and enhanced fairly seriously as well. And IBM has further DBMS products as well (e.g., DB/2 on the AS/400). There’s little reason to think IBM would orphan MySQL or any other DBMS product.
  • IBM is very open-source-friendly. For a company that grew up for decades on proprietary software — and still is a huge software products vendor — IBM is very serious about open source. If you doubt that, I have two words for you: “Linux” and “Eclipse”.
  • MySQL might finally get its industrial-strength act together. IBM is good at database management and good at open source. MySQL becoming a no-apologies transactional DBMS would obviously put pressure on Ingres, PostgreSQL, and EnterpriseDB, although there surely would be lots of happy talk about the open source DBMS market being validated, lifting all the vendors and so on. Also, a better MySQL could be bad news for Microsoft SQL Server too.
  • Sun has a lot DBMS partnerships right now. Obviously, Sun owns MySQL, and has partnerships with MySQL storage engine vendors such as Infobright and Kickfire. Sun also has a substantial partnership with Greenplum, and a Barneyesque* one with ParAccel. And of course Sun has strong working relationships with major database vendors such as Oracle and Sybase. What’s more, on a case-by-case basis, Sun may cooperate in the field with yet other DBMS sellers. E.g., I’ve confirmed at least one instance of a Sun sales rep recommending a Kognitio DBMS.
  • IBM partners with outside DBMS vendors too. You’d think IBM’s gazillion DBMS product lines would be enough. But nooooo. I frequently hear rumblings of IBM’s hardware or services operations working with other DBMS products as well. (This is, of course, actually to their credit.)
  • Short-term, there probably would be little effect on partnerships. Greenplum runs on Sun’s Thumper/Thor line of boxes. DB2 doesn’t, and certainly isn’t optimized for same. In the short term, to sell Thors, Sun would presumably continue to sell Greenplum.
  • Longer-term, there could be a DBMS rationalization. DB2, Informix, MySQL + storage engines, and big independent vendors such as Oracle and Sybase would surely always get attention. That’s a lot. There might not be room for much mind share for many database products and vendors beyond that list.

*A Barney partnership is one in which two or more vendors get on stage and do a song and dance about how much they love each other, with little substance beyond that.

Related links

From http://www.dbms2.com/2009/03/18/database-implications-if-ibm-acquires-sun/

Tuesday, March 17, 2009

Multiple CTEs in a Single T-SQL Statement

What is CTE

From BOL,

Common Table Expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

However, in BOL there is no example, how to write a single T-SQL statement with multiple CTEs. hence I have seen lot of queries about this in many forums. Following is the format you should use.

WITH CTEA (ID,Col1)

AS

(

Syntax for CTEA

),

CTEB (ID,Col2)

AS

(

Syntax for CTEB

)

SELECT CTEA.ID,CTEA.Col1,CTEB.Col2 FROM CTEA

INNER JOIN  CTEB

ON CTEA.ID = CTEB.ID

Notice that two CTEs are separated by , and for the second CTE you SHOULD NOT use WITH statement.

Here is an example, where you want to compare sales values between year 2003 and 2004 for each product. 

WITH Prod2003 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2003
GROUP BY Prod.ProductNumber),
Prod2004 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2004
GROUP BY Prod.ProductNumber
)

SELECT Prod2003.ProductNumber,
Prod2003.Amount Sales2003,
Prod2004.Amount Sales2004 ,
Prod2004.Amount - Prod2003.Amount SalesIncrement
FROM Prod2003
INNER JOIN   Prod2004
ON Prod2003.ProductNumber = Prod2004.ProductNumber

Output for the above query is displayed in following image.

image

Thursday, March 12, 2009

Start Data Collector Set of Reliability & Performance Monitor from C#

Reliability & Performance Monitor a.k.a perfmon is a valuable tool to have for a Database Administrator, as it provides various counters to measure your system with defined time interval.

Data Collector Sets (Similar to Counter Logs in Windows 2003 and XP or before versions)  will give you the opportunity to collect these data to file so that it can be analyzed later.

image

In the Data Collect Set, you need to start the defined Data Collector Set. Problem here is if you want to analyze something soon after you do it, there will be a time delay. For example, if you want to measure % of Processor soon after the execution of a stored procedure. Normally, you need to execute the stored procedure and manually start the Data Collector Set which lead to some delay. Instead, you can include both the executing the Stored Procedure and starting the Data Collector Sets inside a C# or VB.net application.

So the next question is, how to start Data Collector Sets from C#. Though you have perfmon with previous versions of OS you can start Data Collector Sets from C# only with Vista and Windows 2008.

First, add PLALibrary to the reference from the %windir%\System32\PLA.dll and include using PlaLibrary; at the top of your code. Following code will start, Data Collector Set named Page Split.

private void Start_PLA()
        {
            IDataCollectorSet cs = new DataCollectorSetClass();
            string name = "Page Split";
            cs.Query(name, null);
            cs.start(false);
        }

Similarly, there are many functions available with PLALibrary for other operations like, Create and Stop Data Collector Sets.

Friday, March 6, 2009

Integrating perfmon and profiler

perfmon and profiler are valuable tools for DBAs to monitor your SQL Server instance. In SQL Server 2005 you have the option of integrating these two tools. Following video show how to integrate them.

Video1 Video2 Video3 Video4 Video5 Video6

This is my second video and it took me more than just pain to get this video done. Initially I couldn’t get this video for one avi file. Then I did the video for 3 videos and found a simple tool to integrate them. After integrating them, final file size was 500+ MB, which was not possible to upload. Then found another tool to split them. After all manage to find to split into six avi files.

As said before, I am not a flashy presenter and please forgive any Language errors. Please send your comments.