Just I was done with my SQL Server installation  all that I need now is a sample database to work on. And this requires to download the sample DB that is available from the Microsoft Corporation to be used as sample DB.

All I had to do was to follow the below simple sequence of steps,

  1. Download the AdventureWorks 2008 OLTP Script.zip, @ the link http://msftdbprodsamples.codeplex.com/releases/view/89502,
  2. Copy all the files in the downloaded zip to any folder location, I just copied the files to the location – ‘C:\AdventureWorks 2008 OLTP Script’
  3. And now all we have to do is open the ’instawdb.sq’ at the location C:\{AdventureWorks 2008 OLTP Script Path}\ with the SQL Servre management studio.
  4. The script available has to be edited as per the need, as I have done below, uncomment the variables
  5. Set the variable SqlSampleDatabasePath to the Data folder location which is mostly by default “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\” and also set the SqlSampleSourceDataPath to “C:\AdventureWorks 2008 OLTP Script\”, the location where you have your .CSV files placed.
  6. Do not forget to set the Query mode in Query menu to SQLCMD mode as below,
  7. Execute the query now and we are done, to see the sample database AdventureWorks2008 installed into our SQL Server. Share this to help some one.

    This results in the AdventureWorks2008 sample Database added to our SQL Server,

     

Advertisements

This was an interesting SQL concept i came across to check for the Distinct of records and get a Distinct A,B,C and also to get the Distinct (B), from the result of distinct combination. I am sure i have confused you!!

OK.. let me put this in a more simple form, We derive at combination of distinct Product, Price, Size. This shall give a result something as below,

And to get the same scenario in my sample table i query into the Product table, taking only a single product which gives all the distinct combinations as below (taken only one product for instance here),

But what if i need the one combination to get the one with the maximum price, which can be attained using the Row-Number() function as in my sample query,

SELECT ProductID,ReferenceOrderLineID,ReferenceOrderID, ActualCost
FROM
(SELECT Production.TransactionHistory.*,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ActualCost DESC) AS RN
FROM Production.TransactionHistory) AS t
WHERE RN = 1 

And here we go with the distinct and only transaction of every product with the maximum cost as is shown below,

So we can use the same where the Distinct and group by clause might be help less. Thanks for a read, share and enjoy 🙂

How to enable a select copy of headers?

Initially i was struggling to get this option done, and found it hard to type the headers manually where ever i copied my query results. So here i have the solution for you.

Its a simple flow of steps to follow to enable this to work. As usual on your SQL server just follow the below steps and you get your headers from the query result to be copied into your clipboard for a paste to any where.

Select the Tools menu on the top of your window. Then select the Options from tools menu.

Now appears the Options window, where on the left we have the menu Query Results -> SQL Server -> Results to Grid.

Click the ‘Results to Grid’ option and on right pane of the Options window we have the option ‘Include column headers when copying or saving the results’.
Just check this option and we are done, to enable the copying of headers along with the query result datas.

Thanks for reading tis post. Feel free to share your comments and views on the same. Have fun learning 🙂

%d bloggers like this: