SQL SERVER – Retrieving Random Rows from Table Using NEWID()

November 16, 2012 by pinaldave

I have previously written about how to get random rows from SQL Server.

• SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader
• SQL SERVER – Random Number Generator Script – SQL Query

However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well.

USE AdventureWorks2012
GO-- Method 1SELECT TOP 100 *FROM Sales.SalesOrderDetailORDER BY NEWID()GO-- Method 2SELECT TOP 100 *FROM Sales.SalesOrderDetailORDER BY CHECKSUM(NEWID())GO

You will notice that using NEWID() in the ORDER BY will return random rows in the result set. How many of you knew this trick? You can run above script multiple times and it will give random rows every single time.

Note: This method can be very resource intensive for large resultsets.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Difference Between DBMS and RDBMS

What is difference between DBMS and RDBMS?

DBMS – Data Base Management System

RDBMS – Relational Data Base Management System or Relational DBMS

A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.

RDBMS adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don’t support a tabular structure or don’t enforce relationships between tables.

DBMS does not impose any constraints or security with regard to data manipulation it is user or the programmer responsibility to ensure the ACID PROPERTY of the database whereas the RDBMS is more with this regards because RDBMS defines the integrity constraint for the purpose of holding ACID PROPERTY. RDBMS may be or may not be Client Server Database System.

Examples :

DBMS – File System, XML

RDBMS – SQL Server, Oracle

SQL SERVER – Move Database Files MDF and LDF to Another Location

October 28, 2012 by pinaldave

When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.

It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.

USE MASTER;GO-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for databaseALTER DATABASE TestDBSET SINGLE_USERWITH ROLLBACK IMMEDIATE;GO-- Detach DBEXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'GO

Now move the files from loc1 to loc2. You can now reattach the files with new locations.

-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DBCREATE DATABASE [TestDB] ON( FILENAME = N'F:\loc2\TestDB.mdf' ),
( FILENAME = N'F:\loc2\TestDB_log.ldf' )FOR ATTACH
GO

Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Changing Default Installation Path for SQL Server

November 11, 2012 by pinaldave

Earlier I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after database is already created. I had mentioned that we will discuss how to change the default location of the database. This way we do not have to change the location of the database after it is created at different locations.

The ideal scenario would be to specify this default location of the database files when SQL Server Installation was performed. If you have already installed SQL Server there is an easy way to solve this problem. This will not impact any database created before the change, it will only affect the default location of the database created after the change.

To change the default location of the SQL Server Installation follow the steps mentioned below:

Go to Right Click on Servers >> Click on Properties >> Go to the Database Settings screen

You can change the default location of the database files. All the future database created after the setting is changed will go to this new location.

You can also do the same with T-SQL and here is the T-SQL code to do the same.

USE [master]
GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'F:\DATA'GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\DATA'GO

What are the best practices do you follow with regards to default file location for your database? I am interested to know them.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

What is OLTP (Online Transaction Processing)?

In OLTP –(online transaction processing) systems, relational database design uses the discipline of data modeling and generally follows the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules, complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

(Read more here http://bit.ly/sql-interview)

What is sub-query? Explain properties of sub-query.

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also...

be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

(Read more here http://bit.ly/sql-interview)

What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:

Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete.
...

NO_WAIT:

Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.

(Read more here http://bit.ly/sql-interview)