123 TSQL functions - SQLServerCentral

123 TSQL functions - SQLServerCentral:

hmm.

Moving the master Database

Move System Databases: "Moving the master Database"

Moving the master Database

---

To move the master database, follow these steps.

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
4. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
   In the Specify a startup parameter box, change the parameter to the new path of the master database.
5. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change.
   In the Specify a startup parameter box, change the parameter to the new path of the master database.
   The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.
   -dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:
   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
7. Move the master.mdf and mastlog.ldf files to the new location.
8. Restart the instance of SQL Server.
9. Verify the file change for the master database by running the following query.
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID('master');
   GO

Move System Databases : Failure Recovery Procedure

Failure Recovery Procedure

---

If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. This procedure applies to all system databases except the master and Resource databases.

Important
If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

1. Stop the instance of SQL Server if it is started.
2. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
   • For the default (MSSQLSERVER) instance, run the following command:
     NET START MSSQLSERVER /f /T3608
     

   • For a named instance, run the following command:
     NET START MSSQL$instancename /f /T3608
     

   For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.
3. For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
   ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
   

   For more information about using the sqlcmd utility, see Use the sqlcmd Utility.
4. Exit the sqlcmd utility or SQL Server Management Studio.
5. Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.
6. Move the file or files to the new location.
7. Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.
8. Verify the file change by running the following query.
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'');

Reorganize and Rebuild Indexes

Reorganize and Rebuild Indexes: "To check the fragmentation of an index"

USE AdventureWorks2012;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table. 
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 
GO

SQL Server Backup, Integrity Check, Index and Statistics Maintenance

SQL Server Backup, Integrity Check, Index and Statistics Maintenance:

from the page:

Getting Started

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.

Learn more about using the SQL Server Maintenance Solution:

• DatabaseBackup: SQL Server Backup
• DatabaseIntegrityCheck: SQL Server Integrity Check
• IndexOptimize: SQL Server Index and Statistics Maintenance

Intelligent Index Maintenance

The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In theIndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

In this example, indexes that have a high fragmentation level will be rebuilt, online if possible. Indexes that have a medium fragmentation level will be reorganized. Indexes that have a low fragmentation level will remain untouched.

Update Statistics

The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. If an index is rebuilt, the statistics is not being updated. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Improve query performance with RAM disk

Improve query performance with RAM disk:

-- create database on physical drive c:\ with default setting
CREATE DATABASE [TestPerformance] ON  PRIMARY 
( NAME = N'TestPerformance', FILENAME = N'TestPerformance.mdf' , SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPerformance_log', FILENAME = N'TestPerformance_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- create database on ramdisk (drive L:\) with default setting
CREATE DATABASE [TestPerformanceRamdisk] ON  PRIMARY 
( NAME = N'TestPerformanceRamdisk', FILENAME = N'TestPerformanceRamdisk.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPerformanceRamdisk_log', FILENAME = N'TestPerformanceRamdisk_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- create simple test tables in both databases
CREATE TABLE TestPerformance..TestInsert (testColumn UNIQUEIDENTIFIER)
GO
CREATE TABLE TestPerformanceRamDisk..TestInsert (testColumn UNIQUEIDENTIFIER)
GO

-- run this script on both databases and compare time
DECLARE @i INT = 100000
DECLARE @start DATETIME = GETDATE()
DECLARE @end DATETIME 

WHILE @i > 0
BEGIN

    INSERT INTO TestInsert
        VALUES (NEWID())

    SET @i -= 1
END

SET @end = GETDATE()
SELECT CONVERT (TIME, @end - @start)

SQL Server Management Studio forgets password

Hum » SQL Server Management Studio forgets password:

It doesn’t take too long to set up a tool to monitor a file or directory – you can download FileMon from:

http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx

The Connect to Server dialog can pop up in different situations where there is no security context for the connection to use. If you use registered servers (and save the password there) when you view the registered servers, select a server in the Registered Servers task pane and then click New Query on the toolbar or Right Click, Connect, Object Explorer or New Query which will result in connecting under the security context you used when registering the server. If a server isn’t selected, there is no security context available so the Connect to Server dialog pops up. I haven’t heard of passwords  being “lost” or “forgotten” using the registered servers task pane and saving registration information there.

Good info.

Agreed. :)

Optimizing tempdb Performance : Viewing tempdb Size and Growth Parameters

Optimizing tempdb Performance

SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;

Starting a MSSQL Instance in a Command-Prompt Window

Starting a SQL Instance in a Command-Prompt Window

If the SQL instance fails to start as a service, it can be started in a command-prompt window. Generally, this is only done for troubleshooting purposes. (See http://msdn.microsoft.com/en-us/library/ms180965(v=SQL.100).aspx) For example, if a SQL instance starting up as a service cannot find the storage device that the tempdb files are to be stored on, the instance will fail startup. Since the service cannot be started, its tempdb location also cannot be reconfigured in the normal way. In this case, the solution is to run the instance in a command-prompt window, reconfigure the tempdb location, stop the command-prompt instance, and start it again, running as a service.To start a SQL instance in a command-prompt window, cd to the instance's Binn directory (eg. 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL_INSTANCE\MSSQL\Binn'), and enter the following command: sqlservr -f -s "SQL_INSTANCE" This command runs the SQL instance named "SQL_INSTANCE" inside the command-prompt window itself, with a minimal configuration. A minimal configuration limits instance execution to a single user. To reconfigure the tempdb file locations, open another command-prompt window and use one of the SQLCMD.EXE methods shown above. When finished, return to the command-prompt window in which SQL is running, type Ctrl-C, and confirm the shutdown request. Afterward, run the SQL instance as a service.

How to Relocate MSSQL tempdb Files via SQLCMD

How to Relocate Microsoft's SQL tempdb Files:

Using SQLCMD.EXE		To change the location using the command-line tool SQLCMD.EXE, open a command-prompt window with Administrator privileges, and cd to the instance's Binn directory. Then follow one of the three methods shown below: Note: In the examples below,  'MY_SERVER' is the name of the server running SQL.  'SQL_INSTANCE' is the name of the SQL instance.
First Method: Scripted		Create the two script files, 'ChangeLocation.sql' and 'ReportLocation.sql', with the content shown in the tables above. Modify the file path specification appropriately. To execute the scripts, open a command prompt and enter the commands shown below: sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ChangeLocation.sql"Changed database context to 'master'. The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ReportLocation.sql" name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)
Second Method: Interactive		Execute the commands in an interactive SQLCMD session, as shown below (modify the file path specification appropriately): sqlcmd -S "MY_SERVER\SQL_INSTANCE" 1> USE master 2> go Changed database context to 'master'. 1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf') 2> go The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. 1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf') 2> go The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. 1> quitsqlcmd -S "MY_SERVER\SQL_INSTANCE" 1> SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb') 2> go name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)   1> quit
Third Method: Command-Line Query		The query commands can be concatenated for use in a single argument on the command line, as shown below (modify the file path specification appropriately): sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"USE master;ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf');ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf');" Changed database context to 'master'. The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"SELECT name, physical_name;FROM sys.master_files;WHERE database_id = DB_ID('tempdb');" name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)

How to Relocate MSSQL tempdb Files via SSMS

How to Relocate Microsoft's SQL tempdb Files:

Using "SQL Server Management Studio"

To change the location using "SQL Server Management Studio" (requires that the SQL instance is running as a service): 1. Open "SQL Server Management Studio", and connect to the desired server. 2. Open a new query, copy the Change File Location text into the query pane, and execute the query. (Change the path as required.) 3. Open "Services" (Control Panel -> Administrative Tools), and stop and then start "SQL Server (Xxx)". ('Xxx' is the instance name.) 4. In "SQL Server Management Studio" open a new query, copy the Report File Location text into the query pane, and execute the query. 5. Verify the new file locations in the physical_name column.

How to Relocate MSSQL TempDB via SQL

How to Relocate Microsoft's SQL tempdb Files:

QUERY TO CHANGE FILE LOCATION (ChangeLocation.sql)

USE master  go  ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf')  go  ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf')  go

QUERY TO REPORT FILE LOCATION (MyReportScript.sql)

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb') go

Eric Barnard. Iterations: Getting Started with LocalDB

Eric Barnard. Iterations: Getting Started with LocalDB: "Sharing Instances
So what if you want to allow multiple users on a machine to have access to a single LocalDB database instance? Well you can also "share" a "Named" instance of LocalDB. You can control who the instance is shared with, and you can enable/disable sharing at any time.

When connecting with a shared instance, your connection string will need to include an extra ".\" in the server portion. Going with the example above, one would use: "(localdb)\.\MySharedInstance". This tells the engine that this is a shared instance."

'via Blog this'

what can cause Visual Studio to rebuild my entire solution all the time? - Stack Overflow

what can cause Visual Studio to rebuild my entire solution all the time? - Stack Overflow: "You can use the configuration manager to set up a specific config for your sln that will only build the projects you specify. You can find it under Build->Configuration Manager"

This fixed it for me. Somehow one of the projects had become unchecked.

C# Threadsafe Random

    ///     A threadsafe wrapper around .
    public static class Randem {

        private static int seed;

        static Randem() { seed = Environment.TickCount; }

        ///

        ///     Provide to each thread its own .
        ///
        private static readonly ThreadLocal< Random > ThreadSafeRandom = new ThreadLocal< Random >( valueFactory: () => new Random( Interlocked.Increment( ref seed ) ), trackAllValues: false );

        ///

        ///     A thread-local .
        ///
        public static Random Instance { get { return ThreadSafeRandom.Value; } }

        ///

        ///     Returns a random float between and .
        ///
        ///
        ///
        ///
        public static float NextFloat( float min = 0, float max = 1 ) { return ( float ) ( min + ( Instance.NextDouble() * ( max - min ) ) ); }


        ///

        ///     Generate a random number between and .
        ///
        /// The inclusive lower bound of the random number returned.
        /// The exclusive upper bound of the random number returned.
        ///
        public static int Next( int minValue, int maxValue ) { return Instance.Next( minValue: minValue, maxValue: maxValue ); }

C# TakeLast()

        ///

        ///     Remove and return the last item in the list, otherwise return null.
        ///
        ///
        ///
        ///
        public static TType TakeLast( this IList list ) {
            if ( list == null ) {
                throw new ArgumentNullException( "list" );
            }
            var index = list.Count - 1;
            if ( index < 0 ) {
                return default( TType );
            }
            var item = list[ index ];
            list.RemoveAt( index );
            return item;
        }

C# List TakeFirst()

        ///     Remove and return the first item in the list, otherwise return null.
        public static TType TakeFirst( this IList list ) {
            if ( list == null ) {
                throw new ArgumentNullException( "list" );
            }
            if ( list.Count <= 0 ) {
                return default( TType );
            }
            var item = list[ 0 ];
            list.RemoveAt( 0 );
            return item;
        }

C#/.NET Little Wonders: 5 Easy Ways to Combine Sequences

C#/.NET Little Wonders: 5 Easy Ways to Combine Sequences:

If you need to squeeze a little more performance to combine large lists or other enumerable sources, that does not contain any duplicate items, then here's what I've found in my testing:

Fastest, with no duplicates: Concat(list1).Concat(list2).Concat(list3).Concat(list4).Union(list5);

Remember, this style worked the best in my classes. Always test yours..

Designing a Database: 7 Things You don't Want To Do | SQLRockstar | Thomas LaRock

Designing a Database: 7 Things You don't Want To Do | SQLRockstar | Thomas LaRock:

'via Blog this'

Microsoft SQL Server 2012 TempDB Best Practices

Here are my performance guidelines I've gleaned from experience as a DBA and the researching on the internet:

1. Move the primary TempDB.mdf to its own hard drive or the fastest drive in your system.
   • ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\Tempdb.mdf');
2. Add additional tempdb_N.mdf, one per spindle per physical cpu core.
   • As of 2013 a modern hard drive has one spindle. (A spindle is what the platters rotate around.)
   • Each core should have a fast spindle all to itself.
3. On each tempdb.mdf, set the Autogrowth to 1% and unlimited.
   • Some DBAs recommend setting autogrowth off and setting a maxium initial size to avoid fragmentation and 'pauses' on query executions. I disagree for the simple reason that in MSSQL 2012 tempdb makes good use of its internal bitmaps and will grow with the queries as needed. Never shrink the tempdb unless you need the space.
4. Move the primary TempDB.ldf it its own hard drive or the fastest drive in your system.
• ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'L:\TempDB\Tempdb.ldf');
5. Restart the server and verify your database changes: select [name], [physical_name] AS [CurrentLocation], [state_desc] from [sys].[master_files] order by [name]
6. Defragment the free space on all drives. I recommend Defraggler or Auslogics Defrag.