Table Hierarchy updated

Recently a need resurfaced to explore the foreign key tree (hierarchy/genealogy) as it related to a specific table within a database.  As I pulled out the script from the repository, I realized there was some unfinished work to be done.  But there was also some polish that needed to be added.  This is an update to the most recent posting of that script.  You can see several revisions in the series at this link or the group here.

Some of the changes involve formatting and and labeling.  I added a new column called “Direction” to help understand the relationship of the key to the table in question.  I also changed up the FKGenealogy (formerly called SortCol) to reflect the source table more accurately in the case when the key comes from an ancestor rather than a descendant.  The Level of the FK was also modified to help understand a little better how far away the ancestor was in relationship to the origin table.

A final adjustment also comes from the Genealogy attribute.  Ancestors were all starting at the wrong point in the lineage.  I adjusted that so the lineage can be seen from the point in the tree that the ancestor is related rather than as a root direct from the origin table.

All in all, this script should make more sense to the user than the previous versions.

Table Hierarchy goes CS

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: November 16, 2011

At the urging of a friend, this script is being updated for those that are dealing with Case Sensitivity.  The first few rounds, I neglected Case Sensitivity and never tested for that.  It makes sense to have this script updated for that if anybody out there is using it.

The updates are simple enough, it is just frustrating if you run into an error caused by CS and then you waste time troubleshooting it.  Believe me, it has happened to me recently – and I don’t much like it.

Without further ado, here is the udpated script:

[codesyntax lang=”tsql”]

[/codesyntax]

Still on the todo list is to make this bad boy run faster in the event of circular references.  If you find something else with it that you think could use adjusting, let me know.

FK Hierarchy v 2.1

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: August 9, 2011

Last month I published an update to my Foreign Key Hierarchy script.  Today, I am providing a new update for that script.  A friend (Rémi Grégoire) helped out with some mods to this script.  The change for this month is nothing too intrusive.  The script is now updated for those databases that are Case Sensitive.

[codesyntax lang=”tsql”]

[/codesyntax]

This update should make it more usable for any that may be using it or is interested in using it.  Thanks for Rémi for taking the time to propose this update.

Foreign Key Hierarchy Update

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: July 11, 2011

Today I would like to revisit a post of mine that is rather old.  More precisely, the script in that post needs revisiting.  This is one of my more favorite scripts and I still have more ideas to implement with it.  The post/script in question can be found here.

In revisiting this script, I simplified it a bit.  I also had to work on a problem with it that occurs in Hierarchies with circular dependencies.  Quite frankly, that was a huge pain in the butt.  There are some tricks out there to try and help with it – but I was having a hard time getting any of them to work in this scenario.  I also updated the script to better handle self-referencing objects.

When you have circular references, and are trying to recurse the tree via a CTE, an instant blocker comes into play.  You can only reference the anchor of the Recursive CTE once.  Fixing a circular reference would be many times easier if you could reference the anchor twice.

In the end, the biggest hint to getting this to work came from this post.  For it to work, I needed to find which combination of fields would work best.  I finally settled on using the Object_ID to help reduce my pain.  I settled on using the following in the anchor:

[codesyntax lang=”tsql”]

[/codesyntax]

and the following in the recursive definition.

[codesyntax lang=”tsql”]

[/codesyntax]

You can see that I am concatenating into a string for this column.  This seems to work well for the purpose of eliminating those circular references.

Other adjustments to the script are not quite as significant but there is a small performance gain to be seen by these subtle changes.  The most notable is the change to remove the two joins out to sys.columns in order to get the column names of the Parent and Child objects.  In lieu of these joins, I am using the COL_NAME() function.  This little change came to me thanks to a little work done last week on my statistics script that you can read here.

The final notable change comes in the naming of the CTEs in this script.  I decided to rename the CTEs to something a bit more meaningful.  In this case, Hierarchy and Ancestry are much more appropriate.

Without further adieu, here is the next major revision of that script.

[codesyntax lang=”tsql”]

[/codesyntax]

I hope you will play with this script, test it out and make recommendations or even find bugs with it and let me know.

T-SQL Tuesday #104: Just Can’t Cut That Cord

We all have our favorite scripts, tools or utilities. Those are the things that help make our jobs easier. Some of us may have an unhealthy relationship with some of those scripts (similar in nature to the relationship many have with their phone). Whether or not the need to cut that proverbial cord exists, today we are not discussing the health of that dependence. Suffice it to say, sometimes we simply need to upgrade our scripts. How else can we get better scripts or make our scripts better – by sharing them.

This is precisely the goal Bert Wagner (b | t) seems to have envisioned for the 104th installment of TSQL Tuesday.

If you are interested in reading the original invite, you can find that here.

For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.

Maybe you built a maintenance script to free up disk space, wrote a query to gather system stats for monitoring, or coded some PowerShell to clean up string data.  Your work doesn’t need to be completely original either – maybe you’ve improved the code in some open source project to better solve the problem for your particular situation.”

There is a high probability that through the sharing of your script, somebody out there can benefit from that script. In addition, it is very likely that somebody will make a suggestion to help make your script better. Worst case (emphasis on worst case here), you have the script stored somewhere with half decent instructions on what it does and making it easily accessible for you to use again and again. Just in case you forget you have it out there – you can google for it again and find it on your own blog ;).

Personally, I have been able to find and re-use some of my older scripts. Not only do I get to re-discover them, but I also get to re-imagine a new use or improvement for the script.

Brief Intermission

A shout out is absolutely necessary for Adam Machanic (twitter) for picking the right blog meme that has been able to survive so long in the SQLFamily. This party has helped many people figure out fresh topics as well as enabled them to continue to learn.

Easy Access

While pondering the topic for today, I had the thought occur about how frequently I post a script on my blog already anyway. An easy out for this topic would have been to re-share one of those old scripts. For instance, I could easily redo a recent article about server access that has a couple scripts demonstrated in it. Or I could go back a few years to my articles about foreign keys (here or here) and space use (here or here). Even more intriguing could be to re-envision some of my articles on Extended Events. But where would the fun in that be?

Rather than take the easy road and rehash something, I have something different. This one goes hand in hand with the numerous articles and scripts I have previously provided on auditing – yet it is different.

Not every shop can afford third party software or even Enterprise edition and so they have to come up with a different way to audit their database instances. One of the problems with a home grown solution is to ensure the data is not stored local to the server (lots of good reasons for that). Here is an example of what I did for one client that happened to have a developer that found a back door that was giving him SA access to the SQL Server Instance and was changing things and trying to cover his tracks – even after being warned.

First the query

This query will be run from a job on a different server that is restricted in access to just a select few people. I do rely on the use of the default trace in this query. I am also reliant upon a little bit of sneaky behavior. If I run this from a separate server, prying eyes are usually unlikely to find that it is running and thus makes it easier to catch them red-handed. In addition, if they discover via some sort of trace and by a lot of luck that it is running, then they have no access to the remote server to alter anything that was captured.

The query does go out to the default trace and pull back any changes to permissions or principals on the server in question. The captured data is then stored in a database that is also restricted to a select few people. Lastly, the captured data can be routinely queried, or automated reports can be created to send email notifications of changes encountered.

The second part of the trickery here is that I am using a linked server to perform the queries (a slight change and I could also do this via powershell which will be shown in a future article). The linked server query uses the openquery format and sends the default trace query to the remote server. Since I am running this from a job on an administrative server that pulls a limited data set, I am not overly concerned with the linked server setup here.

Storing It

Once I query the data, I need to put it somewhere on my administrative server. The table setup for that is very straight forward.

After creating this table, I am ready to store the data. All I need to do is throw the audit query into an agent job and schedule it to run on a regular schedule. For my purposes, I usually only run it once a day.

TSQL2sDay150x150The Wrap

This has been my diatribe about service and giving back to the community. When done properly, there is a natural born effect of enhancing one’s personal life equal in some way to the amount of effort given towards the community.

Oh, and if you are interested in some of my community contributions (which according to Jens Vestargaard is an awesome contribution), read this series I have published.

An Introduction to Templates

Comments: No Comments
Published on: December 30, 2017

Templates are a powerful tool in so many trades and crafts. From decals and stickers all the way up to the largest cruise ships in the world, templates can be found everywhere in just about everything that do.

In SQL Server, templates are readily available for your use in so many different ways that I am sure we are unaware of most of them.

Templates

A good example of a template in SQL Server, that we clamor about frequently, is the unattended install. I am sure you have probably used that kind of template as you work to save yourself some hair and brain cells trying to remember the exact setup needed for your environment. With an unattended install, you build it once and then use the script to build all future installs.

Even though we may not call the unattended install script a template, it is. Think about the definition(s) of a template for a minute:

  1. something that serves as a model for others to copy.
  2. a preset format for a document or file, used so that the format does not have to be recreated each time it is used.

Looking at this definition, we can easily see that the unattended install routine fits perfectly as a “template”. This is just an example of the availability of templates within SQL Server. There are many different kinds of templates – many we probably don’t ever think about as being “templates” and many that we just flat out don’t bother to think about at all.

I have one specific kind of template in mind that I want to introduce and discuss. This template type comes with Extended Events. I actually introduced the concept very briefly in a recent post about the XE Profiler feature. That article is just one of many articles about Extended Events that I have written. You are welcome to catch up on the series of articles here.

XE

Extended Events has had templates available for quite some time. If you recall from the article about XE Profiler, templates were not introduced as a part of the XE Profiler feature – the templates were introduced quite some time prior to that.

There are really three distinct areas of focus for templates that I would like to discuss. I have them somewhat illustrated in this next screenshot.

In this image, you will see that I have three color coded regions. In green, I have the option to create a template from file. In blue, I have the XE specific templates that have no correlation to profiler. And then in red I have the templates that were created to simulate the behavior of the Profiler based templates while providing some sort of comfort zone for the profiler fanatics out there.

Today, I will focus on the templates that are readily available (red and blue zones). In a future article, I will cover the creation of a custom template (green zone). That said, this is not going to be a traditional article with the technical geekery showing the ins and outs of all of those templates. Oh no, this article will be an entirely different flavor of geekery. Today, I will be focusing on something that is more about fun. I will be exploring the available templates strictly via script (no XE GUI).

Exploratory Surgery

If you are like me, you sometimes just don’t care to pick up the mouse to browse the file system or even browse the SSMS hierarchy to find the exact screen that has some specific information that you want. For me, I ran into this when thinking about the templates in XE. What templates are there in XE again? Crap, now I have to open the GUI to figure that out. I wanted a quick way to be able to look at all of the templates – and not just the templates available to me in the version of SSMS that I am using, I want to see everything out there on the system.

The question is how to do this. The templates are not listed in any system table or view that I could find. The list of templates is specific to the version of SSMS that you are using and the templates are all xml files stored in a directory on the OS. A thought occurred to me while contemplating this dilemma – can I maybe use the registry?

The registry does give me some options so maybe, I can use xp_regread. I won’t be able to use xp_instance_regread since this is not a registry key that appears at the instance level, rather it is an SSMS related reg string. One problem that comes to mind is that I would need to find the SQLPath for each of the versions of SSMS installed (red arrows indicate each of the registry strings I might have to query). I start to think to myself after realizing this that I do not like the looks of this option – but it could be done.

I then thought of another option before going too far down the registry route. When I install an application, that application may modify my “Path” environment variable for the OS. This sounds a little promising. I still have some of the drawback with multiple versions of SSMS installed – but I think I can manage that more easily from the “Path” environment variable.

Let’s take a crack at a script.

Ouch, my eyes hurt already! What have I done here? I have written multiple loops. Never fear! Loops are not all evil. Sometimes, it makes sense to use a looping mechanism. This is one of those times where it makes some sense. This script is a limited use script for the eyes of the DBA only.

I can hear the moans about the use of xp_cmdshell and xp_dirtree and so forth. I am not listening to those grumbles. Again, this script is for fun and on my machine. The discussion on securing the server and xp_cmdshell is a discussion for another time.

In this script, I use the path variable to determine all of the flavors of SSMS that have been installed. Since the path for SSMS is all the way to the BINN folder, I need to do a little replace to get the correct paths for the xe templates. I also added a bit of a union in there because I had to duplicate the results – sorta. You see there are templates for AzureDB that, depending on your Azure Subscription or lack thereof, you may never see. I wanted to include those in my result set!

Looking at these results on a machine with SSMS 14 and SSMS 17, I would see something like the following.

I highlighted a few different sections of the results to help quickly show some of the templates available between different SSMS versions as well as those for Azure DB versus a local instance of SQL Server. Things are a tad different if we have SSMS 16 installed. Here is a sample result from a machine with SSMS 16 installed.

The main point of interest here (at least for me) is the removal of a template from SSMS 16 to SSMS 17. This is probably a template you don’t necessarily want to run on a production system and that is likely why it is gone from the available templates that get installed.

The Wrap

I am sure you can probably find an alternate routine to query these templates via TSQL that may be more efficient. I would be interested to hear about it.

What’s next now that I can get these templates? Well, a thought occurred to me to use this type of routine to create a means to use TSQL to read one of those template files to create the XE session from pure TSQL statements and eliminate the need to use the GUI. I think that would be a lot of TSQL/XE Geekery based fun.

Seattle SQL Pro Workshop 2017 Schedule

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 26, 2017

db_resuscitateSeattle SQL Pro Workshop 2017

You may be aware of an event that some friends and I are putting together during the week of PASS Summit 2017. I have created an Eventbrite page with all the gory details here.

With everybody being in a mad scramble to get things done to pull this together, the one task we left for last was to publish a schedule. While this is coming up very late in the game, rest assured we are not foregoing some semblance of order for the day. 😉 That said, there will still be plenty of disorder / fun to be had during the day.

So the entire point of this post is to publish the schedule and have a landing page for it during the event. *

Session Start Duration Presenter Topic
Registration 8:30 AM All
Intro/Welcome 9:00 AM 10 Jason Brimhall  
1 9:10 AM 60 Jason Brimhall Dolly, Footprints and a Dash of EXtra TimE
Break 10:10 AM 5    
2 10:15 AM 60 Jimmy May Intro to Monitoring I/O: The Counters That Count
Break 11:15 AM 5    
3 11:20 AM 60 Gail Shaw Parameter sniffing and other cases of the confused optimiser
Lunch 12:20 PM 60   Networking /  RG
4 1:20 PM 60 Louis Davidson Implementing a Hierarchy in SQL Server
Break 2:20 PM 5    
5 2:25 PM 60 Andy Leonard Designing an SSIS Framework
Break 3:25 PM 5    
6 3:30 PM 60 Wayne Sheffield What is this “SQL Inj/stuff/ection”, and how does it affect me?
Wrap 4:30 PM 30   Swag and Thank You
END 5:00 PM Cleanup

*This schedule is subject to change without notice.

SQL Server Locks – Back to Basics

loch_nessPrelude in SQL Minor

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

SQL Server Locks

A fundamental component of SQL Server is locking and locks. Locks within SQL Server are critical to the proper functioning of the database and the integrity of the data within the database. The presence of locks does not inherently mean there is a problem. In no way should locking within SQL Server be considered a monster, though locks may often times be misconstrued in that light.

So what is locking? What are locks? In SQL Server, locking is a pessimistic mechanism that limits, or synchronizes, access to resources between multiple different requests. A lock is the manifestation of that mechanism. Think of a lock within SQL Server as if it were a padlock that was temporarily securing a gate to a storage shed limiting access to the shed.

That is the overly simplistic illustration of a lock in SQL Server. Have you ever seen a gate with more than one lock on it? Well, just like that multi-lock gate, resources within SQL Server can have several different locks placed on them. This is where it starts to get complicated and tricky.

To help control the locks and direct access to resources within the database, SQL Server utilizes a service called the lock manager. When a query is executed, the Database Engine (more precisely the query processor) determines what resources are needed and what locks are needed for those resources. Then the query processor walks over to the lock manager and begs for the lock types needed. The lock manager takes a look at the locks in use on the resources and either grants the locks or tells the query processor to wait until the locks are available.

This process is not terribly different from a tenant requesting the use of a meeting room from the building manager (or property manager). If the meeting room is available, the building manager will grant access to the meeting room and place a hold on that room preventing other tenants from using it at the time you requested it. Well, not terribly different but on a much much larger scale.

Just as you may see in a building with multiple meeting spaces, offices, rooms, and equipment as resources, there are many types of resources within SQL Server. The lock manager has to coordinate different types of locks for the varying types of resources. Let’s take a look at some of the lock types and resource types.

Lock Types

First up is to explore the different types of locks in SQL Server. One can either explore the internet or query SQL Server direct to try and find what is available. I will show two ways to find the different types of locks available within SQL Server via the following queries:

Looking at the results from the first query, I will get results similar to the following:

name map_value
lock_mode NL
lock_mode SCH_S
lock_mode SCH_M
lock_mode S
lock_mode U
lock_mode X
lock_mode IS
lock_mode IU
lock_mode IX
lock_mode SIU
lock_mode SIX
lock_mode UIX
lock_mode BU
lock_mode RS_S
lock_mode RS_U
lock_mode RI_NL
lock_mode RI_S
lock_mode RI_U
lock_mode RI_X
lock_mode RX_S
lock_mode RX_U
lock_mode LAST_MODE

The last lock type in this list is kind of an interesting one. I believe this maps to the RangeX-X lock type seen in the documentation and in the second of the two queries provided. What do these letters mean? It looks like a bunch of alphabet soup right? Well, there is a little more info to be found once turning to the documentation (check the image by the heading of “key”). Here’s a little table to decrypt it as well:

name map_value Decrypted
lock_mode NL No Lock
lock_mode SCH_S Schema Stability Locks
lock_mode SCH_M Schema Modification Locks
lock_mode S Shared
lock_mode U Update
lock_mode X Exclusive
lock_mode IS Intent Shared
lock_mode IU Intent Update
lock_mode IX Intent Exclusive
lock_mode SIU Shared with Intent Update
lock_mode SIX Shared with Intent Exclusive
lock_mode UIX Update with Intent Exclusive
lock_mode BU Bulk Update
lock_mode RS_S Shared Range-Shared
lock_mode RS_U Shared Range-Update
lock_mode RI_NL Insert Range-Null
lock_mode RI_S Insert Range-Shared
lock_mode RI_U Insert Range-Update
lock_mode RI_X Insert Range-Exclusive
lock_mode RX_S Exclusive Range-Shared
lock_mode RX_U Exclusive Range-Update
lock_mode LAST_MODE Exclusive Range-Exclusive

I want to just cover some quick highlights about a few lock types:

Lock mode Description
Shared (S) Used for Read operations that do not change data.
Update (U) Used on resources that can be updated. Helps to reduce some deadlocks.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish imminent need for a lock.
Schema Used when a schema dependent operation is executing.
Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Lock Resources

Lock resources are the things upon which a lock is held. This is the lock granularity and hierarchy. It is this hierarchy that helps to reduce the overall cost of locking in SQL Server (again banishing the notion of it being a monster). The hierarchy of locks can be explored via queries in SQL Server (and it can be found – to a degree – in the documentation). Here are a couple of queries to help explore the locks hierarchy:

The results of the first of these queries are:

name map_value
lock_resource_type UNKNOWN_LOCK_RESOURCE
lock_resource_type NULL_RESOURCE
lock_resource_type DATABASE
lock_resource_type FILE
lock_resource_type UNUSED1
lock_resource_type OBJECT
lock_resource_type PAGE
lock_resource_type KEY
lock_resource_type EXTENT
lock_resource_type RID
lock_resource_type APPLICATION
lock_resource_type METADATA
lock_resource_type HOBT
lock_resource_type ALLOCATION_UNIT
lock_resource_type OIB
lock_resource_type ROWGROUP
lock_resource_type LAST_RESOURCE

There are a few differences between this list and what is found in the documentation. There are also a few differences between the results of the first and second queries. This should give a pretty good idea about the level at which locks could be held from a key all the way up to database.

I have shared these because the lock resources may come in handy when troubleshooting or even designing queries and database schemas.

Lock Owners

The last bit about locks for this time is the lock owner. Another way of thinking about this one is what is happening while the lock is being held. Is the query a part of a transaction that is holding the lock? Maybe the query is a cursor and the cursor is holding locks for the queries within the construct of the cursor. The following query that shows what the possible owners are can be a little helpful in understanding what an owner is.

name map_value
lock_owner_type Transaction
lock_owner_type Cursor
lock_owner_type Session
lock_owner_type SharedXactWorkspace
lock_owner_type ExclusiveXactWorkspace
lock_owner_type LockConflictNotificationObject
lock_owner_type LockTableIterator
lock_owner_type LastLockInfoOwner

This is another one of those items to explore which could help understand the nature of the locks if you happen to be troubleshooting or investigating a certain locking issue within your environment.

Recap

 

Locking is a fundamental concept in SQL Server. Locking is the mechanism that coordinates access to resources while maintaining data integrity. Locks are the tool used in locking to ensure access is only granted to the correct requestor and preventing access to the resource to outside sessions.

This was a basic introductory article on the topic. Stay tuned for a more in-depth look at a specific scenario involving locking and deadlocking.

TSQL Sudoku

Comments: 9 Comments
Published on: August 17, 2011

I am a big Sudoku fan.  Typically if I need a break, I will break out a Sudoku puzzle from any of a number of different sources (Websudoku, Android Apps, Puzzle Books).  Over time, I have come across a solution here or there to solve these puzzles via TSQL.

There are a few of these solutions out there already, such as one by Itzik Ben-Gan (which I can’t get to download without the file corrupting so I still haven’t seen it), or this one on SSC (which works most of the time but does provide inaccurate results from time to time).  I still wanted something to do this via CTE (much like the solution by Itzik is described to be at the link provided – if you have that code, I want to SEE it).

Just a couple of years ago, there was a post at SSC asking for some help converting a solution from Oracle to TSQL.  I checked out that code and worked on it for a day or two.  Then I got busy with other work that replaced the pet project.  I hadn’t given the idea much thought until just a few days ago as I was browsing my Topic list I had been building for articles.

This solution stuck with me this time around and I wanted to finish it up.  The Oracle solution for whatever reason made a lot more sense to me this time around, and I made great progress quickly.  It was actually this project that I was working on that prompted another post.  While working through the solution, I learned a fair amount about both flavors of SQL.  So, in preface to continuing to read here, you may want to check out the other article real quick since it pertains to some of the conversions done in this project.

Problems First

The OP supplied the Oracle solution asking for help in creating a TSQL Solution.  Here is that Oracle version.

[codesyntax lang=”sql”]

[/codesyntax]

If you read that other post I mentioned, you will quickly identify 5 functions/objects in use in this script that just don’t work in TSQL.  Those are:  dual, instr, substr, connect by, and trunc.  I did not mention mod in my other post, but mod is also done differently in TSQL than in Oracle.  I thought this one was a bit obvious and stuck with the top 5 ;).

Solution

After figuring out some of the subtle differences between commands and the best way to approach this, I was able to come up with a TSQL solution that works.  Take not first of that last where clause in the CTE of the Oracle solution.  That clause is very similar to what I refer to as the train-stop method to get unique paths in a hierarchy.  There are several methods to do similar functionality – I have concatenated strings with Stuff as wells cast to produce this functionality.

So here goes with the first rendition of this query.

[codesyntax lang=”tsql”]

[/codesyntax]

Notice that I have chosen to use an Itzik style numbers table/CTE.  This functions as my “dual” table translation and is necessary in the remainder of the query.  The final where clause of the CTE is simplified in TSQL by simply removing the TRUNC commands.  The original solution was merely removing the decimal precision.  In TSQL, the conversion is done to INT implicitly in this case.  I need to test a few more cases, but so far it works without error.

What this does not do…

This is the first rendition of the script.  Currently, it only returns the number sequence in one big long string.  I am working on modifying this script to produce a grid layout with the solution.  I envision this will require the use of PIVOT and possibly UNPIVOT to get me close.  In addition, I expect that further string manipulation will be needed – such as stuffing commas and then splitting it to make the PIVOT/UNPIVOT easier.  I’ll have to try some things and figure it out.  Also, I expect that some explicit conversions may be best in this query.  That could help improve performance a tad.

This, to this point, has been a fun diversion.  This has helped to learn a bit about Oracle, hierarchies, and to do a little math – all in one.  Better yet is that there is still work to be done on it and more learning.  If you have ideas how to make it better – I am very interested.

Top 5 Oracle Nuances I learned Today

Categories: News, Professional, SSC
Tags: ,
Comments: 1 Comment
Published on: August 16, 2011

I don’t do much with Oracle – at all.  Once in a blue moon, I find a little project to do that might involve Oracle.  I have never put a lot of thought to the differences between SQL and Oracle.  On the pet project I am doing right now, I put a little more thought into those differences and finally decided to write a little something about five things I am working with in the Oracle world and how those translate (or at least how I translated them) to the SQL world.

Let’s start with some very similar commands.

  1. substr().  In SQL, this translates to substring.  Easy enough right?  There is one more difference between the two than just the name.  The parameters are ordered differently in substr() than they are in substring().   Pay careful attention to your parameter sequence when converting this function from Oracle to SQL Server.
  2. instr().  This one is less obvious.  I have used PatIndex() and CharIndex() for this one – depends on needed functionality.  If you understand that instr is searching for a value within a string – it makes it a little easier to understand.  Also knowing that PatIndex searches for “Patterns” and Charindex() searches for a character is helpful.  If you need to supply the optional parameter used by instr(), then you should use Charindex.  Though not entirely the same – similar functionality is available in SQL for the instr() function.
  3. trunc().  This is a function used in Oracle to convert date and numbers to a shorter format (either different date format or fewer decimal places).  This is achieved through different means in SQL.  Two common methods are cast() and convert().
  4. dual.  This is not a function.  This is an internal table containing a single row.  There are many uses for this internal table.  One common use is equivalent to the Numbers/Tally table in SQL server.  Pick your favorite numbers/tally table method in these types of cases.
  5. connect by.  This is actually a pretty cool piece of functionality unique to Oracle.  I have seen this used in recursive CTEs to help control the hierarchy.  In these cases, it limits the result set to rows meeting the criteria of the connect by statement.  Similar functionality can be achieved through use of Joins and the Where clause.  This is a command that would be really cool in SQL.  It is true that you can build the hierarchy without this command in SQL.  I think it would help make that task easier and give it more flexibility.  It would also make it a little easier to read/understand.

This is all pretty cool.  It should be pretty straight forward stuff for most DBAs.  Some day, maybe we’ll explore a post dedicated to connect by and how some of the features of that command can be translated into SQL.  For now, just know that there is some commonality between the two RDBMSs – just a little translation may be necessary.

«page 1 of 2

Calendar
November 2018
M T W T F S S
« Jul    
 1234
567891011
12131415161718
19202122232425
2627282930  

Welcome , today is Monday, November 12, 2018