SQL Server # TSQL to Rename Database Name along with its Logical and Physical Name

There are many ways to rename the SQL server Database name. Today, I will explain how to rename a Database in SQL Server using T-SQL along with its Logical and Physical Name.

The very easiest and most popular way of renaming is – Suppose, you have a database named as “TestDB”. Now you want to rename it with “NewTestDB”.

  --Rename the data base "TestDB to New_TestDB".

  EXEC sp_renameDB 'TestDB','New_TestDB'

  GO

Above given example just renames the Database title (name); However if you want to rename the physical file, just Detach the database, Go to the file path which is currently located, rename both the LDF and MDF file, and now here Attach the database.

Below steps are given for renaming the database by using the TSQL statement – Just open SSMS Copy the below scripts and press F5.

USE TestDB

GO

è First of all set the database for a single user, it will disconnect all the currently connected users.

ALTER DATABASE TestDB

  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

è Now rename the Database title (Name).

EXEC master..sp_renamedb 'TestDB','NewTestDB'

GO

è Now the change the Logical file name, because above statement just change the name of the Database.

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB', NEWNAME=N'NewTestDB')

GO

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB_log', NEWNAME=N'NewTestDB_log')

GO

è Now Detach the Database for change the Physical file name, as we can't do anything with the physical file when the database is in active mode.

EXEC master.dbo.sp_detach_db @dbname = N'NewTestDB'

GO

è Now Change the SSMS settings if you haven’t set it.

è This is necessary because the below provided command will not run without set this.

sp_configure 'show advanced options'

GO

/* 0 = Disabled , 1 = Enabled */

sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE WITH OVERRIDE

GO

è Now rename your physical files with the use of command.

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB.mdf", "NewTestDB.mdf"'

GO

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB_log.ldf", "NewTestDB_log.ldf"'

GO

è Now just attach the database. (Currently detached by us).

CREATE DATABASE NewTestDB ON

( FILENAME = N'F:\CD\Temporary Work\NewTestDB.mdf' ),

( FILENAME = N'F:\CD\Temporary Work\NewTestDB_log.ldf' )

FOR ATTACH

GO

è Now set the database as multi users can work on this.

ALTER DATABASE NewTestDB SET MULTI_USER

GO

è Finally you can see that, the physical files are renamed.

SELECT

  name AS [Logical Name],

  physical_name AS [DB File Path],

  type_desc AS [File Type],

  state_desc AS [State]

FROM

  sys.master_files

WHERE

  database_id = DB_ID(N'NewTestDB')

GO

Conclusion

Hence, we have learnt how we can rename the SQL Server Database along with its Logical & Physical using some simple TSQL statements.

SELECT TOP .. WITH TIES

The SQL Server TOP Expression returns only the first set of rows that is either a number or a percent of rows. If you want additional rows to be returned from the base result set if duplicate values exists in the qualified set of results, then use TOP..WITH TIES as shown in the example below:

   1: DECLARE @Sales TABLE 

   2: (

   3:   Customer NVARCHAR(200),

   4:   TotalSum FLOAT

   5: )

   6:  

   7: INSERT INTO @Sales

   8:   (Customer,  TotalSum)

   9: VALUES

  10:   ('Vijay', 4000000),

  11:   ('Ajay', 1000000),

  12:   ('Mehul', 2000000),

  13:   ('Kalpesh', 1000000),

  14:   ('Jatin', 20)

  15:  

  16: SELECT 

  17:   TOP (3) *

  18: FROM 

  19:   @Sales

  20: ORDER BY 

  21:   TotalSum DESC

I have written Top (3) in above query so it is confirm that result will be only 3 rows.
Output:


But what if i want to know that how many customers are there that having top 3 sales in above query "Mr. Ajay" is missing though he is also having TotalSum = 1000000.
So in this situations we can use "TOP (n) WITH TIES" sql statement to get the result.

   1: SELECT 

   2:   TOP (3) WITH TIES *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:


Note : TOP [WITH TIES] is always use with ORDER BY only.
Now, understand this query in depth,
what is the scenario if record set is like below.

Output:


Now run the same query that we have run above.

   1: SELECT 

   2:   TOP (3) *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:

   1: SELECT 

   2:   TOP (3) WITH TIES *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:


Conclusion :
From above example it is clear that TOP [WITH TIES] repeat only those record which is available in basic record set that is TOP (n) and that is fully depend on ORDER BY Column.

LINQ Zip Operator

The Zip operator was added in Framework 4.0. It enumerates two sequences in step (like a zipper), returning a sequence based on applying a function over each element pair.
The extension method signature is:

   1: public static IEnumerable<TResult> Zip<TFirst, TSecond, TResult>(

   2:                                     this IEnumerable<TFirst> first,

   3:                                     IEnumerable<TSecond> second,

   4:                                     Func<TFirst, TSecond, TResult> resultSelector)

Zip cycles through two sequences using deferred execution. The items at the same index are paired and each pair is transformed using a function that is usually provided as a lambda expression. The sequence stops as soon as one of the sequences exhausts its elements; ideally, the sequences are identical in length or you can say in other word continues until all of the items in either sequence have been processed. If one sequence has more elements than the other, the extra elements are not projected into the new sequence. The merging happens in the func function. It takes two arguments (the element from first source, and the element from the second source) and lets you return a combined type.

Example:

   1: using System;

   2: using System.Linq;

   3:  

   4: namespace ZIP_OPERATOR_DEMP

   5: {

   6:   class Program

   7:   {

   8:     static void Main(string[] args)

   9:     {

  10:       //Initialize two lists of integer with same sequence.

  11:       int[] integers1 = new int[] { 1, 2, 3, 4, 5 };

  12:       int[] integers2 = new int[] { 10, 20, 30, 40, 50 };

  13:  

  14:       //Now apply zip operator on it.

  15:       var sumsZip = integers1.Zip(integers2, (i, j) => i + j);

  16:  

  17:       Console.WriteLine("Output of zip of two interger series.");

  18:       Console.WriteLine("=====================================");

  19:  

  20:       //Get the out of Zip operation.

  21:       foreach (var sum in sumsZip)

  22:       { Console.WriteLine(sum); }

  23:  

  24:       Console.WriteLine("\n");

  25:  

  26:       //Initialize two lists with different sequence and different datatypes.

  27:       int[] integers3 = new int[] { 1, 2, 3, 4, 5 };

  28:       char[] characters = new char[] { 'A', 'B', 'C', 'D', 'E', 'F' };

  29:       var items = characters.Zip(integers3, (c, i) => string.Format("{0}{1}", c, i));

  30:  

  31:       Console.WriteLine("Output of zip of two different datatype series.");

  32:       Console.WriteLine("===============================================");

  33:  

  34:       //Get the out of Zip operation.

  35:       foreach (var item in items)

  36:       { Console.WriteLine(item); }

  37:  

  38:       Console.WriteLine("\n");

  39:  

  40:       //Now we zip more then two list with one by one basis.

  41:       //So final output will be full address line for each element.

  42:       var bldgNum = new string[] {"A5", "A2", "A1" };

  43:       var flatNum = new int[] {104, 109, 25, 200 };

  44:       var streetNm = new string[] {"Baker Street", "Cross Street", "Hu Street" };

  45:       var city = new string[] { "CO", "WA", "AU", "CA" };

  46:  

  47:       Console.WriteLine("Output of multiple zip operation multiple series.");

  48:       Console.WriteLine("=================================================");

  49:  

  50:       //Zip all the partiall address to full address.

  51:       var address = bldgNum.Zip(flatNum, (bl, fl) => bl + ", " + fl.ToString())

  52:                            .Zip(streetNm, (fl, st) => fl + " , " + st)

  53:                            .Zip(city, (st, ct) => st + ", " + ct);

  54:  

  55:       foreach (var addr in address)

  56:         Console.WriteLine(addr);

  57:  

  58:       Console.ReadLine();

  59:     }

  60:   }

  61: }

Output:

 

Explanation:

For the first example we'll zip together two sequences containing the same number of elements, where each sequence contains the same data type. In the code below you can see that the Zip operator is used as an extension method of the first array. The second array is passed to the first parameter. The second argument specifies the projection function that is used to generate the items in the resultant collection. In this case each pair of values is summed.

In the second example we add an array of characters so that we can demonstrate combining sequences of differing types. Here a character and an integer from the source arrays are combined with string.Format to generate a string. Note also that the list of characters is longer than the integer sequence. The final item, "F", is dropped from the results. 

Conclusion:

We can use this "ZIP" linq operator where we want some action to perform on same or different data types sequences but operator consider only "Source" & "Destination" index match else ignore other element in sequence.

For more details you can refer MSDN.