Convert Comma Separated string to table having a varchar column.

CREATE FUNCTION [dbo].[ToStringTable]
( 
    @ItemList varchar(500)
)
RETURNS 
@ParsedList table
( 
    ItemId VARCHAR(100)
)
AS
BEGIN 
    DECLARE @ItemID VARCHAR(100), @Pos int 

    SET @ItemList = LTRIM(RTRIM(@ItemList))+ ',' 
    SET @Pos = CHARINDEX(',', @ItemList, 1) 

    IF REPLACE(@ItemList, ',', '') <> '' 
    BEGIN  
        WHILE @Pos > 0  
        BEGIN   
            SET @ItemID = LTRIM(RTRIM(LEFT(@ItemList, @Pos - 1)))   

            IF @ItemID <> ''   
            BEGIN    
                INSERT INTO @ParsedList (ItemId)     
                VALUES (@ItemID)   
            END   

            SET @ItemList = RIGHT(@ItemList, LEN(@ItemList) - @Pos)   
            SET @Pos = CHARINDEX(',', @ItemList, 1)  
        END 
    END  
    RETURN
END

Convert comma separated string to a table having varchar and numeric column

=============================================
-- Author:  Changgyu Oh
-- Create date: 10/21/2011
-- Description: For comma separated int type items in a string, it returns
--              table having a string column and a decimal column for the items.
-- 
=============================================
CREATE FUNCTION [dbo].[ToStringNumericTable]
(
    @ItemList varchar(500)
)
RETURNS 
@ParsedList table
( 
    Keyword VARCHAR(100) NULL , 
    KeywordNumeric decimal NULL)
AS
BEGIN 
    DECLARE @ItemID VARCHAR(100), @Pos int 

    SET @ItemList = LTRIM(RTRIM(@ItemList))+ ',' 
    SET @Pos = CHARINDEX(',', @ItemList, 1) 

    IF REPLACE(@ItemList, ',', '') <> '' 
    BEGIN  
        WHILE @Pos > 0  
        BEGIN   
            SET @ItemID = LTRIM(RTRIM(LEFT(@ItemList, @Pos - 1)))   

            IF @ItemID <> ''   
            BEGIN    
                INSERT INTO @ParsedList (Keyword, KeywordNumeric)    
                VALUES( @ItemID          
                        , CASE WHEN ISNUMERIC(@ItemID) = 1 THEN CONVERT(decimal, @ItemID)            
                               ELSE -1 END)   
            END   

            SET @ItemList = RIGHT(@ItemList, LEN(@ItemList) - @Pos)   
            SET @Pos = CHARINDEX(',', @ItemList, 1)  
        END 
    END  
    RETURN
END

Determining the fragmentation of Indice

From http://www.sql-server-performance.com/2011/index-maintenance-performance/

SELECT
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[name],
    FRAG.avg_fragmentation_in_percent,
    FRAG.page_count
FROM
    sys.dm_db_index_physical_stats
    (
        DB_ID(),    --use the currently connected database
        0,          --Parameter for object_id.
        DEFAULT,    --Parameter for index_id.
        0,          --Parameter for partition_number.
        DEFAULT     --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
    JOIN
    sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
    --don't bother with heaps, if we have these anyway outside staging tables.
    FRAG.index_type_desc <> 'HEAP' AND
    (
    --Either consider only those indexes that need treatment
    (FRAG.page_count > @PageCount AND FRAG.avg_fragmentation_in_percent > @MinFragmentation)
    OR
    --or do everything when it is MaintenanceDay
    @IsMaintenanceDay = 1
    )
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;

@PageCount: Default value is 128. Anything below this threshold we ignore. The benefits here do not justify the efforts.
•@MinFragmentation: This defaults to 10%. Anything below 10% is ignored. We couldn’t observe any significant performance hit with fragmentation levels < 10% given our workload.
•@IsMaintenanceDay: Once a week we have a maintenance window, in which we can maintain all indexes.

How to enable T-SQL debugging in Visual Studio .NET 2005

1. Right click on a user that you want to enable to debug and select a properties.

2. Select Server Roles and check sysadmin role.

3. You may run following query:

4. Open your Visual Studio .NET 2005 and open a Server Explorer.
Then right click on Data Connection and select Add Connection…

5. Then setup db connection.

6. Now expand your db from Visual Studio.net 2005 and right click a stored procedure to debug, then choose step into.

7. Now, you provide inputs for SP.

8. Then finally you are able to debug T-sql in Visual Studio .NET 2005.
Congratulations!!!

Error: User Could Not Execute Stored Procedure sp_enable_sql_debug

I found a hint from here:http://msdn2.microsoft.com/en-us/library/ms241735(VS.80).aspx

The Stored Procedure sp_enable_sql_debug could not execute on the server. This can be caused by:
A connection problem. You need to have a stable connection to the server.
Lack of necessary permissions on the server. To debug on SQL Server 2005, both the account running Visual Studio and the account used to connect to SQL Server must be members of the sysadmin role. The account used to connect to SQL Server is either your Windows user account (if you are using Windows authentication) or an account with user ID and password (if you use SQL authentication).

sp_addsrvrolemember 'Domain\domainusername', 'sysadmin'

Overview of T-SQL and CLR debugging in SQL Server 2005

From:http://blogs.msdn.com/sqlclr/archive/2006/06/29/651644.aspx

SQL Server 2005 ships with a new debugging engine that supports debugging of any T-SQL and CLR code running in the server, including batches, stored procedures, user defined functions/aggregates/triggers, etc. You can use Visual Studio 2005 to debug against SQL Server 2005 or SQL Server 2000, but you can not use Visual Studio 2003 or earlier to debug against SQL Server 2005 because the debugging engine is not compatible.

There are some improvements in debugging in SQL Server 2005:
. Its much easier to setup than debugging in SQL Server 2000. You can enable debugging SQL Server 2005 from Visual Studio 2005 by following these simple steps (please note that Remote Debugging Monitor mentioned in the steps is not required for T-SQL debugging).
. Integration of T-SQL debugging and CLR debugging. You can step in/out from T-SQL code to CLR code, or vise versa. You will get mixed call stack consisting of T-SQL frames and CLR frames, and inspect T-SQL and CLR variables on any frame.
. Full functionality of CLR debugging.
. Isolation of database connections in T-SQL debugging. When you break in T-SQL code in one connection, other connections are not suspended and can continue to run normally until they require resource locked by the connection being debugged. In another word, the impact of debugging a connection to other connections on the server is similar to have a long-running connection.

The easiest way to develop and debug CLR code running in SQL Server is to use a C# or VB SQL Server project in Visual Studio. When you deploy a SQL Server project, Visual Studio deploys the CLR assembly and its symbol file (.pdb) and source code files to the database so that the assembly is ready for execution and debugging.

When you have a CLR assembly built in other ways, you can also use CREATE ASSEMBLY statement to deploy it to the database. In this case you need to use ALTER ASSEMBLY ADD FILE to attach the symbol file (.pdb) and source code files to the assembly, and then you can use Visual Studio to debug the assembly. If you forget to add a symbol file or source file, step-in or breakpoint in the corresponding CLR code will be skipped by Visual Studio.

In Visual Studio there are 3 ways that you can start debugging of T-SQL or CLR code in the database:
. Direct Database Debugging (DDD). You can open Server Explorer and add a Data Connection to a SQL Server database. Then you can browse to any T-SQL or CLR object in the database, such as a stored procedure or function, right click on it, and select Step Into to begin DDD.
. Debugging from a SQL Server project. When you start debugging from a C# or VB SQL Server project (e.g. by pressing F5), the assembly will be built and deployed, and the default test script will be run in debugging mode. Breakpoints in the default test script or in any T-SQL or CLR code called by the default test script will be hit. You can also right click on any test script in the project and select Debug Script.
. Application debugging. You can use Visual Studio to attach to any client application that opens a database connection to SQL Server, e.g. SQL Management Studio, and then you can debug T-SQL or CLR code executed on this connection. When you attach to the client process, you need to make sure the debugging type includes T-SQL code, along with any other desired types such as native code and/or managed code. There are some notable limitations in application debugging:
. You have to attach to the client process before the database connection is opened. Any code run on the connections opened before attaching will be ignored.
. A Data Connection to the server being debugged must present in the Server Explorer. Connections made by the attached client to databases not listed in Data Connections in Server Explorer will not be debugged.
. You can not step into a T-SQL or CLR store procedure from client code (managed or native). Usually you need to set a breakpoint in the T-SQL or CLR code that you want to debug. To do this browse to the desired object in Server Explorer, double click on the object to open its source code, and set a breakpoint.

The following are some imitations of T-SQL and CLR debugging in SQL Server 2005:
. T-SQL debugging is on the statement level. You can not step into the execution of a select statement.
. Visual Studio 2005 is needed to debug T-SQL. Microsoft SQL Server Management Studio doesnt support debugging. Some third party tools that can be used to debug SQL Server 2005 may be released in the near future (or may have already been released).
. Break into CLR code in one connection freezes all connections that are running CLR code. CLR debugging doesnt have connection isolation that is available in T-SQL debugging, because of limitations in CLR debugging architecture.
. Debugging doesnt work with parallel compiled plan execution. When a batch or stored procedure being debugged is compiled to a parallel plan, debugging may not work. In this case you need to disable parallel plan generation to debug it.

Debugging stored procedures in Query Analyzer of MSSQL2000

Bottom line is that the DCOM configuration must include the tcp/ip protocol.

The debugger does not use the same connection as SQL Query Analyzer, which means that there is second connection for the debugger. It uses DCOM to make a connection to the server.

If you can't debug, run dcomcnfg, select the protocols tab, and make sure tcp/ip is in the list.