Friday, July 29, 2011

Is TRUNCATE TABLE transactional ?

1: CREATE TABLE tTest ( i INT )  
2: INSERT tTest ( i ) VALUES( 1 ) 
3: INSERT tTest ( i ) VALUES( 1 ) 
4: INSERT tTest ( i ) VALUES( 1 ) 
5: INSERT tTest ( i ) VALUES( 1 ) 
6: 
7: BEGIN TRAN 
8:  TRUNCATE TABLE tTest 
9:  SELECT * FROM tTest 
10: ROLLBACK 
11: 
12: SELECT COUNT(*) FROM tTest 
13: DROP TABLE tTest 
14: 
15: 


So, yes - it is subject to the transaction; also - it is a logged operation, its just that the individual rows are not logged "individually" like they would if you used DELETE.

How to remove blank line in SSMS “Find and Replace” dialogue

The answer is simple; in Management Studio go into Find and Replace (ctrl+H ) , in "Find Options" check "Use" and select "Regular Expressions".

Now, in the Find what: box enter the text ^\n

In the Replace with: box enter nothing, please don't take me literally on that and actually type the word nothing, I mean leave the text box empty.

Click Replace All.

Wednesday, July 20, 2011

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, we may need a quick script to find relationship  between tables. Here is the script which helped me a lot, hope this will be useful for you too

  1: SELECT  OBJECT_NAME(rkeyid) ParentTable
  2:        ,OBJECT_NAME(fkeyid) ChildTable
  3:        ,OBJECT_NAME(constid) FKey
  4:        ,c1.name FKey_Col
  5:        ,c2.name Ref_KeyCol
  6: FROM    sys.sysforeignkeys s
  7: INNER JOIN sys.syscolumns c1 ON ( s.fkeyid = c1.id
  8:                                   AND s.fkey = c1.colid
  9:                                 )
 10: INNER JOIN syscolumns c2 ON ( s.rkeyid = c2.id
 11:                               AND s.rkey = c2.colid
 12:                             )
 13: ORDER BY 1,2

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

 

USE [AdventureWorks]
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
    @val as BigInt,
    @base as int
)
returns varchar(63)
as
Begin
    /* Check if we get the valid base */
    If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;
    /* variable to hold final answer */
    Declare @answer as varchar(63);
    /*    Following variable contains all
        possible alpha numeric letters for any base
    */
    Declare @alldigits as varchar(36);
    Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    /*    Set the initial value of
        final answer as empty string
    */
    Set @answer='';
    /* Loop until your source value is greater than 0 */
    While @val>0
    Begin
        Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
        Set @val = @val / @base;
    End
    /* Return the final answer */
    return @answer;
End

TSQL Function to encode HTML Text

While browsing through the SharePoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note, this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

 

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[fn_HtmlEncode] (
       @Value NVARCHAR(1023)
      ,@PreserveNewLine BIT )
RETURNS NVARCHAR(4000)
AS 
	BEGIN
	DECLARE @Result NVARCHAR(4000)
	SELECT  @Result = @Value
	IF @Result IS NOT NULL AND LEN(@Result) > 0 
	BEGIN
		 SELECT @Result = REPLACE(@Result, N'&', N'&amp;')
		 SELECT @Result = REPLACE(@Result, N'<', N'<')
		 SELECT @Result = REPLACE(@Result, N'>', N'>')
		 SELECT @Result = REPLACE(@Result, N'''', N'&#39;')
		 SELECT @Result = REPLACE(@Result, N'"', N'&quot;')
		 IF @PreserveNewLine = 1 
			SELECT  @Result = REPLACE(@Result, CHAR(10), CHAR(10) + N'<br>')
	END
    RETURN @Result
END

Tuesday, July 19, 2011

SQL Server Migration Checklist

Here is a quick checklist for your SQL Server Migration.

1. Build your New Server, Install SQL Server and required updates and keep the server ready for migration

2. Stop Application Service(s) and kill all the connections from sql server.
This is to ensure that the no applications are connected to the Databases during the migration process.

3. Disable all the jobs that refers to this database

4. Take the database to ‘SINGLE_USER’mode

5. Take a FULL backup of all the User databases that are involved in the Migration Process.

6. Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.

7. Change the compatibility level of the databases (Optional)
Do this if the applications connecting to these databases are independent of the database compatibility level.

8. Transfer logins using SSIS (Transfer Logins Task) or using "sp_help_revlogin"
More information about sp_help_revlogin is at http://support.microsoft.com/kb/246133

9. Check for Orphaned Users in the databases and Fix them (if Any)

10. Update Usage on the migrated Databases

11. Update Stats on the migrated Databases

12. Re-Index or Re-Organize Indexes on the migrated Databases

13. Transfer Jobs using SSIS or manually create them

14. Build Maintenance plans (if Any)

15. Recompile database objects if required

16. Move or rebuild SSIS or DTS packages (if Any)

17. Create Alerts and Operators (if Any)

18. Setup High Availability Options (if Any Like Replication, LogShipping, Mirroring)

19. Test the High Availability options that were setup in the previous step

20. Point the Application(s) to new Server and start the Application Service(s)

21. If the application uses any of the user defined error messages, you may need to copy them to the new server.

22. Test the Application(s)