Category Archives: Learn SQL Server

OFFSET and FETCH – New in SQL Server 2012

Posted on by
3

In this article we shall learn how to limit the numbers of rows returned by a query using OFFSET and FETCH clause introduced in SQL Server 2012. The following query is executed against AdventureWorks Database.

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID

The following is the query result set when the above T-SQL code is executed.

Observe that the data is displayed sorted by the column SalesOrderDetailID, which is very much clear from the ORDER BY clause at the end of the query. This query will display all records in that table as we have not included any filtering condition.

You might be aware that using TOP clause we can restrict the query result set to as many rows we want it to display. Instead of the top n records, if we wanted to return x number of records from the middle of the result set, sorted on a particular column, we would have to do some additional manipulations and retrieve them. 

In SQL Server 2012, this can be achieved by using the OFFSET and FETCH clause at the end of the Select query, after ORDER BY clause. Let’s take a look at this sample query to understand this better..

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID 
 OFFSET 5 ROW
 FETCH NEXT 10 ROW ONLY

The OFFSET clause sets how many rows needs to be skipped before displaying the result set. In this case we gave OFFSET 5 ROW, so the first 5 rows are skipped. FETCH NEXT x ROW ONLY, displays the next x records, if there are records inside the table. In our example script, we used 10 so 10 rows are displayed. If we mention 10000000 rows, and if there are only 1000 rows in the table, then only 995 rows will be displayed. A small clarification, the keywords ROW or ROWS both will work the same.

Note: Please note that this is correct as of SQL Server 2012 RC0.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Installing SQL Server 2008 R2 Explained

Posted on by
9

This blog will show you step by step instructions on how to install SQL Server 2008 R2.

Before Installing SQL Server make sure you have updated the Windows with the latest Service Pack and hotfixes. .Net 3.5 Framework is a prerequisite for Installing SQL Server 2008 R2 and it has to be installed separately before we begin SQL Server Installation. Read the steps to Install .Net 3.5 framework and install it. Reboot your machine to make sure we are ready for a smooth installation.

Go to the location where you have copied the SQL Server Installation software and you should find the root directory something like what you see below. Make sure you logged in as a user who has Admin rights on the server. Right click the Setup.exe and choose Run as Administrator.

SQL Server Installation Center should open and by default the Planning page is displayed as shown below..

SQL Server Installation Center is designed to be useful when you are performing a new Installation, or modify an existing installation, perform an upgrade and many more tasks related to Installation. We need to click on Installation to display the Installation choices. The Installation page is displayed as below..

When you are installing SQL Server 2012 on a stand-alone server, we need to choose the very first link by clicking on the text in New SQL Server stand-alone installation or add features to an existing installation.

Setup Support Rules is displayed (picture below) which performs a quick check that might cause issues while installation. If there are any failures in the the checklist, those failures need to be corrected (such as a system reboot or user not having admin rights etc). Click OK to proceed.

The next screen is Product Key, in this screen you will have to key in the product key (25 characters long) which came with your Installation media. In this case I  am installing a Developer Edition and I typed in the Key, (for the blog purpose, I whitened the are) and continue clicking Next.

The next screen will display the License Terms and we need to accept the license terms by selecting the check box (as shown in the picture below).

Click Next to proceed. Click Install so that the Setup Support Files are installed.

Once the Setup Support Files are installed the next step is Setup Support Rules which performs additional checks for smooth Installation.

See that there is one warning (Windows Firewall) which will not stop us from installing SQL Server. Click Next to proceed. Remember that if there are any Errors, the Setup will not continue and you have to fix those errors and restart the Installation process.

The Installation now moves to the next step where we can choose how do we want the SQL Server installation to continue. Select SQL Server Feature Installation option button, as this would give us more control on what SQL Server components are going to be installed and installation drives and directories.

Click Next to continue.. In the next screen, you can choose what features do you want to install on your server. You can choose only what is required and in a work environment, not all features are going to be installed. Since I am installing on my personal Server, I selected all features except Books Online. Depending on the Instance features selected in this step, there will be some additional steps (screens) in the installation. For example if you choose Reporting Services feature, there will be a separate step in the Installation process where you will have to choose how the Reporting Services will be installed.. Once you have your selections, click Next…

One more round of Installation Rules to ensure that we do not run into trouble while Installing.. Since there are no failures, click Next.

The next step is Instance Configuration. This is the step where you can choose if the SQL Instance that we are going to install is a Default Instance or a Named Instance. If you wish to install a default Instance, select Default Instance radio button else choose Named Instance and type the Instance Name (please note that there are certain rules while naming a SQL Instance; I wrote a blog about it, read them @ Rules to follow while naming SQL Server Instance). Once you are done typing the Instance Name, click Next.

Next screen displays the disk space required for the Installation, this space requirements depend on the components / features we choose. Click Next to proceed…

In the next screen of Installation, we have to choose the Service Accounts under which the the different SQL Services will run. It is recommended that separate accounts are used for each service. Also ensure that those service accounts have necessary permissions on the SQL Server machine. For this Installation, I would like to use only one service account for all the services, so I click on the button Use the same account for all SQL Server services and there will be a dialog box where I can enter the Account Name and password of the service account and click OK.

If you are a beginner, you might not be understanding this, so leave the task of having a service account and you go ahead and click the button Use the same account for all SQL Server services and there will be a dialog box, in the Account Name there will be a drop down list from which you can select NT Authority\LOCALSERVICE Name and you don’t have to enter a password, and click OK.

Also, note that there is a Startup Type column in which you can select if the SQL Service will start automatically when the Machine is started, you can choose a Manual Startup for a service, if you do not want that service to start as soon the machine starts. You can always change these startup types at any time after the Installation is done. Once you are done with all the choices in this screen, click Next..

The next few screens will be displaying the configuration for the components, the first being Database Engine (pic below). This is the place where you can set the Authentication Mode of the SQL Server Database Engine. By default it would be Windows authentication mode, If you choose Mixed Mode, you will be asked to enter sa password and additionally you might want to add any other users who would be sysadmins on the SQL Server. You can do both of these at a later time, but it is recommended that you add at least one more user as a sysadmin at the time of installation. There are couple of tabs apart from Account Provisioning.

Once you are done with the settings in this tab, click on Data Directories tab. In this Tab, you can choose the directories for User Databases, System databases and the Backup directory. You can make some changes at a later time also, if needed to change in this step, click on the ellipsis button (…)  far right side against each directory type. If you click on that button there will be a popup window to browse for the destination directory.. Make your changes as needed and click Next.

The next step is for setting the Admin accounts for Analysis Services, the same step to add an additional user is as explained in the previous step. Data Directory tab is also available in this step, which is similar to the Database Engine, so we are going to skip that tab. Click Next to proceed.

The next screen is for Reporting Services. If you would like the Installation to automatically configure Reporting Services for you, You can choose which option you think is good for you. I  chose Install, but do not configure the report server as I would like to configure manually at a later time. Click Next…

The option in the next screen lets you decide if you would like SQL Server to send Error Reports to Microsoft automatically. It is only optional and varies from user to user.

The next screen is the final check of Installation prerequisites before Installation begins..

We are all set for Installation, and the next screen is a summary of what we have chosen to Install, what components and chosen, installation location etc.. Click Install for the Installation to begin..

The screen below is a screen shot of the Installation process..

Once the installation is successfully competed, the following screen is displayed.

Once you are done installing successfully, you will have some post installation steps to perform on your SQL Server.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Examples for naming SQL Server Instances

Posted on by
Reply

This blog post is in response to a question / comment that to my blog posted earlier this week, if you haven’t read it, read it here Rules to follow while naming a SQL Server. I thought it would make sense when explained with examples instead of a short reply to that comment.

As with many things in IT, the simplest answer is “it depends”. So these examples that I am giving below are purely fictitious…

What to consider when naming the SQL Instance?

When naming a SQL Server Instance, I would consider the primary application that is going to use this SQL Server Instance, the location of the SQL Server and Environment the SQL Instance belongs to..

Let’s assume there is a Sales application and a SQL Server Instance in required for that application. The SQL Servers (QA, Dev and  Prod) are going to be deployed in New York, I would name the SQL Server Instances as SLSNYCQAS, SLSNYCDEV and SLSNYCPRD.

Another example where an organization named WhiteSun needs a SQL Instance for their Research & Development application, the SQL Instance names that I can think would be WSRNDSTG, WSRNDTRG, WSRNDTST, WSRNDDEV and WSRNDPRD. STG short code for Staging, TRG for Training, TST for Test etc.

There is no hard and fast rule that the Instance names should be ending with PRD, DEV and so on. These SQL Instance names mentioned above can be jumbled to make new names such as DEVSLSNYC or WSTSTRND as per the choice of the decision maker, be it a SQL Server DBA or a Project Manager..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty