SQL – Turning Several Result Sets into a Single Result Set

In case you need to get several result sets through a single result set, for whatever reason. In my case I have program that works on one result set at a time that needed some aggregate data displayed at the end of the report. Here is what you do:

Create a temporary table to store the table with the most fields in your result set. Everything will use this table for its data. Be sure create a primary key using an int identity(1,1). Without creating a primary key the data will not be stored in any order.

Order the data the way you need it to display and insert it into the temporary table. Then add following result sets to this table.

Note: Data types are important in getting this to work and character/string based data types tend to work the best.

Computers Blogs
Computers

Advertisements

Creating Fixed Width / Ragged Right Files Through SQL

Here is a great tip I thought of on-the-fly. Say you need to make a fixed width file for something that needs to get out right away. Here is what you do: convert to char!

Concatenate all the fields together and converting all of the data to the char data type will allow you to save the results in a perfect fixed width / ragged right format.

Simple and fast!

SQL Server Maintenance Plans vs. Powershell Scripts

I have been working on maintenance plans for some time now and while they are very powerful I am starting to wonder if Powershell might be a better platform to perform my server maintenance. I am starting to lean towards Powershell and here is why. Keep in mind that this is up for debate and I am sure this could stir up some controversy. 

With Powershell it is easier to get access to the OS.  With maintenance plans it gets a little more complicated. Maintenance plans are built for SQL DBAs, not for the person who owns the whole box. Sometimes DBAs have limited access to the server, so for those people Powershell isn’t the best choice. Also if you don’t have a programming background, Powershell might not be for you although you might want to consider it since there are some very simple scripts that can get some serious information to you very quickly, but I digress.

Scripts are highly portable. SSIS packages are as well but you can’t edit them easily without BIDS. Notepad is all you need for Powershell.

I am going to keep you all posted as I am making changes to my scripts.  Once I make a really good script I will post it.

PowerGUI – Powershell SQL Server Database Information Script Node

This short script will prompt you for the name or IP of the server you wish to connect to. As long as you have the correct rights to that server you can use this script. Hope this helps Fernando!

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$name = [Microsoft.VisualBasic.Interaction]::Inputbox(“Enter the IP or Name of the server:”)
$server = New-Object(‘Microsoft.SqlServer.Management.Smo.Server’) $name
$server.Databases | format-table Name,Size,RecoveryModel,PrimaryFilePath -AutoSize

If you found this useful, subscribe.

SQL Server Distributed Queries!!!

Distributed queries in SQL Server are a blessing and a curse. What can I say, they are fascinating. For those of you who are not familiar with distributed queries, here is the crash course.

What is a distributed query?

So here is the deal, a distributed query is query that relies on data that lives on another data source. The classic example of distributed queries is the Linked Servers in SQL Server. You can also use the OpenQuery and OpenRowset stored procedures to get to the remote data. The difference between OpenQuery and OpenRowset is that the OpenQuery uses linked servers (linked servers store the connection string data); whereas OpenRowset requires the entire connection string to be passed.

For sanity purposes I like the OpenQuery it is cleaner and is more abstract.

Example:

What not to do!

SELECT RTRIM(name), id FROM OPENQUERY(linkedserver, ‘SELECT name,id FROM foo’) WHERE id = 24234

–Much better!

SELECT name, id FROM OPENQUERY(linkedserver, ‘SELECT RTRIM(name),id FROM foo WHERE id = 24234’)

In the above example something might be popping in your head, and you’re right, it is a nested query! The theory that I use often is this: All you are doing is creating a temporary table from one server to use on another. All of the processing inside the OpenQuery occurs on the linked server first and then fills in a temporary table on the SQL Server from whence you are executing the distributed query. SQL Server then takes that temporary table (results of the OpenQuery) and runs the remainder of the query.

Here is the order of execution, prepare to have your mind blown:

  1. OPENQUERY
  2. FROM
  3. WHERE
  4. SELECT

Here is the mind blowing part, this also relates to performance. Let me explain. The more criteria (restricting the number of rows and fields) you impose in step 1, makes all the remaining steps faster. Likewise any data manipulation you do in step 1 will speed up the entire query. If you have more than one OPENQUERY linking to eachother then join them if possible (they have to be from the same data source), see the example below. Try to get to one OPENQUERY. Remember what I said from earlier, it downloads all that data and puts it into temporary tables. You could take 5 Mb of data transferred from a couple OPENQUERY’s and combine them to be under 1 Mb, this has a huge difference in network traffic and computing time on the SQL Server.

–This is really bad.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, id from foo’) AS foo INNER JOIN OPENQUERY(linkedserver1,’SELECT name2, id from goo’) AS goo

ON foo.id = goo.id

–This is much better.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, name2 FROM foo INNER JOIN goo ON foo.id = goo.id’)

Gotcha’s with Eloquence and the SQL/R ODBC

Only inner joins are accepted in the very old style: FROM foo, goo WHERE foo.id = goo.id

You can only link 5 tables per OPENQUERY

If you have any questions, just post a comment.

Compound Primary Key SQL Trickery Using Exists Keyword!

I recently came across an interesting problem. What I was trying to do was to figure out which id’s were new to the quarter 201001. Take a look at the following queries.

select id
from table as a
group by id, quarter_index
having count(*) = 1 and quarter_index = 201001

select id
from table as a
group by id
having count(*) = 1 and exists (
select quarter_index
from table as b
where b.id = a.id and b.quarter_index = 201001)

Table looks like this:

ID Quarter
1 201001
1 200901
3 200901
4 201001

The first query produces ID’s 1 and 4. The second produces the ID 4. The difference between the two queries are as follows: query 1 is including both the quarter_index and the id in the group by statement thereby including row 1 from the table; query 2 is much different, it is only using id in the group by and is using the exists word in SQL. In query 2, the first part of the query is returning ID’s that only “exist” in one quarter. The second part of the query is saying “of this group of ID’s, give me only the ones that exist in quarter 201001”.

This is something that is very interesting and efficient as well. This is the first time I have used the exists keyword in the having portion of a SQL statement. I think it is rather cool and hope this helps someone.