sql-server

Generate assembly HEX

In case you don't have access to SQL Server file system in order to copy your assembly file, you can generate hex code:

private static String GenerateHexCode(String assemblyPath)
{
        if (!Path.IsPathRooted(assemblyPath))
                assemblyPath = Path.Combine(Environment.CurrentDirectory, assemblyPath);

        var builder = new StringBuilder();
        builder.Append("0x");

        using (var stream = new FileStream(assemblyPath,
                        FileMode.Open, FileAccess.Read, FileShare.Read))
        {
                int currentByte = stream.ReadByte();
                while (currentByte > -1)
                {
                        builder.Append(currentByte.ToString("X2",
                                CultureInfo.InvariantCulture));
                        currentByte = stream.ReadByte();
                }
        }

        return builder.ToString();
}

then register .NET assembly:

CREATE ASSEMBLY <AssemblyName.dll>
AUTHORIZATION [dbo]
FROM <hex code here>
WITH PERMISSION_SET = SAFE
GO

Getting IO and time statistics for SQL Server queries

Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

SET STATISTICS IO ON;
GO

-- Execute your query here
GO

SET STATISTICS IO OFF;
GO

Export query as a .txt file

You can use bcp utility:

  • In command line:
    bcp "<your query here>" queryout Contacts.txt -c -T
  • Using T-SQL
    DECLARE  
          @query VARCHAR(2048) = 'SELECT * FROM <tablename>'
        , @outputfile VARCHAR(2048) = '\\SERVER1\SHARE1\FOLDER\QueryOutput.txt'
        , @connectionstring VARCHAR(64) = '-U <username> -P <password> -S ' + @@servername
        , @bcpquery VARCHAR(MAX)
       
    SET @bcpquery = 'bcp "@query" QUERYOUT "@outputfile" -c -t -T @connectionstring'
    SET @bcpquery = REPLACE (@bcpquery, '@query', @query)
    SET @bcpquery = REPLACE (@bcpquery, '@outputfile', @outputfile)
    SET @bcpquery = REPLACE (@bcpquery, '@connectionstring', @connectionstring)
    SET @bcpquery = REPLACE(@bcpquery, CHAR(10), '')          -- Remove line breaks

    EXEC master..xp_cmdshell @bcpquery

Versioning of stored procedures

SQL Server provides a built-in mechanism for versioning of stored procedures.
You can create more versions for the same stored procedures by adding “;” and version number at the end of stored procedure name. If version number is not specified, SQL Server presumes it to be 1. Let’s have a look on the following sample:

CREATE PROCEDURE myproc;1
AS
PRINT 'You have run version 1 of myproc stored procedure'
GO

CREATE PROCEDURE myproc;2
AS
PRINT 'You have run version 2 of myproc stored procedure'
GO

Running a specific version of the procedure is straightforward:

EXEC myproc;1

or
EXEC myproc;2

One very interesting thing I should remark is you are allowed to use different signatures for each version. Among of drawbacks I would like to point out you can’t drop just a specific version using DROP PROCEDURE statement (you can delete all versions at a time).

Detecting fragmentation for all indexes in SQL Server

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using sys.dm_db_index_physical_stats DMV, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats dynamic management function includes the following columns:
avg_fragmentation_in_percent: the percent of logical fragmentation (out-of-order pages in the index)
fragment_count: the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages: average number of pages in one fragment in an index
avg_record_size_in_bytes: This number simply represents a useful measure for the amount of data stored within the record (index or heap).

SELECT
     OBJECT_NAME(S.OBJECT_ID) as TableName,
     I.name as IndexName,
     S.avg_fragmentation_in_percent
FROM
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as S
     INNER JOIN sys.indexes AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id
WHERE
     S.database_id = DB_ID()
ORDER BY
     TableName, IndexName

After the degree of fragmentation is known, check how you can "resolve" the fragmentation.

Isolation levels: dirty reads, non-repeatable reads, phantom reads

An image saying everything about transaction isolation levels in SQL Server:

  Description Dirty reads Non-repeatable reads Phantom reads
Read Uncommited (S) locks are not acquired by SELECT statements.
You can also achieve this degree of isolation on a query basis using the NOLOCK locking hint:
SELECT * FROM dbo.TheTable WITH(NOLOCK);
Yes Yes Yes
Read Commited (S) locks are requested by the SELECT statements, but (S) locks are acquired only while data is read (not until the end of the transaction). No Yes Yes
Repeatable Read Shared locks are retained by the SELECT statements until the end of the transaction which completely prevents others from updating any data covered by the lock. No No Yes
Serializable Instead of acquiring a lock only on the row to be accessed, the Serializable isolation level acquires a range lock on the row and the next row in the order of the data set requested.
This prevents update and the addition of rows by other transactions in the data set operated on by the first transaction.
No No No

How to find and replace text in all stored procedures and functions

Use SQL Search tool from RedGate, a free tool which allows you to search a specific text in procedures, functions, triggers, views, constraints, etc. The steps would be the following:

1/ Get the generated script by SQL Search
2/ Copy/paste it in SSMS and replace your string (via CTRL-H) 
3/ Validate the new script (i.e execute it between BEGIN TRAN / ROLLBACK TRAN statements)
4/ If everything is ok then you can run it in SSMS
 

How to get SQL server version

Option 1

SELECT
        SERVERPROPERTY('productversion'),
        SERVERPROPERTY ('productlevel'),
        SERVERPROPERTY ('edition')

Option 2

EXEC master..xp_msver

Option 3

SELECT @@version

Option 4

-- Option 4
DECLARE @ver NVARCHAR(128) = CAST(serverproperty('ProductVersion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SET @ver =
                CASE
                        WHEN @ver = '7' THEN 'SQL Server 7'
                        WHEN @ver = '8' THEN 'SQL Server 2000'
                        WHEN @ver = '9' THEN 'SQL Server 2005'
                        WHEN @ver = '10' THEN 'SQL Server 2008/2008 R2'
                        WHEN @ver = '11' THEN 'SQL Server 2012'
                        WHEN @ver = '12' THEN 'SQL Server 2014'
                        ELSE 'Unsupported SQL Server Version'
                END
SELECT @ver

Check column exists in table

IF EXISTS( SELECT 1
                    FROM sys.columns
                    WHERE Name = N'YourColumnName'
                         AND object_id = Object_ID('dbo.[YourTableName]'))
BEGIN
         PRINT 'Column exists'        
END

How to convert an Access database to a SQL Server database

Is your Access database outgrown? Then you might take in consideration converting it to a SQL Server database using the Upsizing Wizard: open Access database, select Tools menu, and then Database Utilities menu.

What you export is your data, indexes, and defaults. The great thing is that Upgrading Wizard maintains your table relationships and referential integrity after upsizing. Be aware, upsizing operation means only a conversion for you i.e. it won’t take advantage of SQL Server features.  Don’t even think Upsizing wizard is doing the entire job. You’ll have some fun converting Access queries to SQL Server views or stored procedures as wizard didn’t make it. Also, you shouldn’t forget in line SQL your application might use.

Upsizing utility is not included in default installation of Microsoft Access, so you’ll need to install in order to benefit of it.

Pages

Subscribe to RSS - sql-server