January 16, 2017 at 9:26 am
I don't necessarily like GUIDs as keys but I get tired of people just dismissing them completely. There are times that using a GUID as a primary key actually makes sense. As in all things, it depends. It all comes down to making the correct decisions when building a database solution to meet business requirements.
January 16, 2017 at 9:41 am
Eric M Russell - Monday, January 16, 2017 8:34 AMSQL suffers from the same predicament as HTML and JavaScript; it's an accessible high-level language that's widely used by a lot of people, but only a few people actually know how to code it at a professional level. When I'm called in to help resolve a performance issue, it's typical for IT management and application developers to have this prejudicial assumption that the root cause has something to do with the configuration of the database server or network itself. The developers bristle when I suggest that there is something wrong with how the SQL itself is coded. After all, they self evaluate themselves as an 8 out of 10 when it comes to proficiency at SQL. At least initially, they dismiss my advice because they think I'm wasting time by nitpicking their coding style, but when they see how much impact in performance can be achieved simply by modifying a join or removing a user defined function from the where clause, they realize there is so much more to be known.
Eric, you said a mouthful. There are a lot of coders, but not a lot of people who understand how things work. I'm not a consultant, but am asked to resolve performance problems internally. Too many times I hear "you're right that the structure needs work, but we have a lot of things built around it so we can't change it" as the reason for not making real change. The code is most often the source of the problem (the scalar function in the WHERE clause like you said or an implicit cast on a join) but the underlying design is sometimes the limit on making huge improvements.
I also encounter the "hey, it works" syndrome where people get defensive about their code and don't believe it can be made any faster. When it is made 30x faster, they think there's a trick somewhere. Naturally, it couldn't be their cursor. Sadly, I know I'll encounter the same code and the same attitude again soon.
January 16, 2017 at 11:18 am
I will say that properly implemented self-service BI is a big step towards better queries. At least where I've worked, I've seen a department manager question an aggregated number on a report and go "this can't be right". But they have pivot table skills, so they're able drill down to the exact transaction that caused the problem, and then either come back to IT/BI with what's wrong, or more often than not, go to the person that did something wrong in the source system and have them fix whatever it is they did that's now showing up in the report data.
The more people who can drill-down & validate/troubleshoot an aggregated number in an organization, the better.
Leonard
Madison, WI
January 16, 2017 at 11:51 am
Gary Varga - Monday, January 16, 2017 5:56 AMOne problem I see from time to time is a business run on the back of hand written ad hoc queries which sometimes go on to "prove" wrong the analysed and verified queries i.e. the business decides that their processes using incorrect data must be correct.
+1000000
January 16, 2017 at 2:40 pm
Lynn Pettis - Monday, January 16, 2017 9:26 AMI don't necessarily like GUIDs as keys but I get tired of people just dismissing them completely. There are times that using a GUID as a primary key actually makes sense. As in all things, it depends. It all comes down to making the correct decisions when building a database solution to meet business requirements.
I used GUIDs because they allow easier tracking across databases and external servers. For example I can tag export or import files with a FileUID and carry that FileUID across servers or databases without fear (just anxiety 🙂 ) of conflict.
They can also be usefull for editing records offline and re-syncing back to the main server.
They are also nice for restoring data in a table (like an accidental delete query) and not worrying about keys getting regenerated of conflicting with existing data.
I add a GUID to all my tables, not always because I have an immediate need for them but for later use.
Btw - I never use a GUID for a clustered index but if my dataset is mostly static or very small then maybe I would and cringe a little.
January 17, 2017 at 7:48 am
In the case of a distributed database environment, like retail point of sale, I'd use a composite key like StoreID + TransactionID before I would resort to using GUIDs. So long as each POS location is setup with a unique StoreID, each transaction should have a globally unique key when imported into the central corporate database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 17, 2017 at 9:00 am
Steve Jones - SSC Editor - Saturday, January 14, 2017 6:28 PMComments posted to this topic are about the item Writing the Correct Query is Important
FWIW, I've never worked at a place where they do code reviews. I don't know why, but I haven't.
Good point that you brought up. I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.
Kindest Regards, Rod Connect with me on LinkedIn.
January 17, 2017 at 9:52 am
Rod at work - Tuesday, January 17, 2017 9:00 AM...I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.
Anyone who has written a SQL statement has written an incorrect SQL statement. The embarrasment is in releasing it (again, guilty as charged).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 17, 2017 at 9:59 am
Kyrilluk - Monday, January 16, 2017 9:03 AMEric M Russell - Monday, January 16, 2017 8:34 AMSQL suffers from the same predicament as HTML and JavaScript; it's an accessible high-level language that's widely used by a lot of people, but only a few people actually know how to code it at a professional level. When I'm called in to help resolve a performance issue, it's typical for IT management and application developers to have this prejudicial assumption that the root cause has something to do with the configuration of the database server or network itself. The developers bristle when I suggest that there is something wrong with how the SQL itself is coded. After all, they self evaluate themselves as an 8 out of 10 when it comes to proficiency at SQL. At least initially, they dismiss my advice because they think I'm wasting time by nitpicking their coding style, but when they see how much impact in performance can be achieved simply by modifying a join or removing a user defined function from the where clause, they realize there is so much more to be known.And don't get me started on software developers that put GUID everywhere (PK, FK) and set all the other columns as nvarchar(max)! Good luck in creating indexes with that!
Note: the act of putting GUID's everywhere isn't the issue: it's how they are used in the data model. There are LOTS of valid reasons to use GUIDs over other identifier schemes; requiring them to be your clustered key or primary key "just because" is, well.... stupid?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2017 at 10:06 am
Steve Jones - SSC Editor - Monday, January 16, 2017 7:24 AMnick.mcdermaid - Sunday, January 15, 2017 10:29 PMWhen you wrote 'I don't want this to be political' my first thought was 'don't blame the business or management' but that's not what you meant.... so I will blame the business and management!Some very basic user testing catches these issues out. Either do proper testing or employ a test team. In my experience, finding someone in the business who is a thorough and conscientious tester is a rarity.
Might I add, it really doesn't help when the latest BI fad is 'self service'. Convincing business that you don't need ETL, data cleansing anymore, just mash all that mess up in the reporting tool!!!
Certainly this is an issue. Good testers are hard to find, but it's also important that once they start testing something, we capture their tests as much as possible in automation. Humans are poor at repeating tests.
We also need a good set of test data that evolves. It's easy to use test data that includes happy paths and not the anomalies we might get in the real data set.
As with many things, it depends on what you mean by "self-service". Self service done right is usually NOT just plopping the user down in front of an empty query window and telling to have at it. Coders aren't users and users shouldn't code. On the other hand if you've built up the appropriate business object and concept layer, with know relationships, etc... dragging and dropping the elements you want to relate absolutely are very enabling to the business.
Of course you don't get to that second option just by blindly dumping data into a DB and letting the users have fun.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2017 at 10:50 am
Rod at work - Tuesday, January 17, 2017 9:00 AMGood point that you brought up. I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.
That, my friend, is a rite of passage.
Along your journey to enlightenment will be the UPDATE statement with the missing WHERE clause, the EAV model, the one true lookup table and a few that my learned friends will shuffle their feet and look sheepish about
January 17, 2017 at 10:55 am
David.Poole - Tuesday, January 17, 2017 10:50 AMRod at work - Tuesday, January 17, 2017 9:00 AMGood point that you brought up. I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.That, my friend, is a rite of passage.
Along your journey to enlightenment will be the UPDATE statement with the missing WHERE clause, the EAV model, the one true lookup table and a few that my learned friends will shuffle their feet and look sheepish about
Agreed. Let's not forget the DELETE statement with the missing WHERE clause and the first realization that you need to go to the DBA and ask for a database restore. We've all done it at least once.
January 17, 2017 at 11:33 am
Or the uninstall of SQL Server from the wrong server.
January 17, 2017 at 2:14 pm
Gary Varga - Tuesday, January 17, 2017 9:52 AMRod at work - Tuesday, January 17, 2017 9:00 AM...I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.Anyone who has written a SQL statement has written an incorrect SQL statement. The embarrasment is in releasing it (again, guilty as charged).
Especially when it security related and you have to shut down the web server, (guilty)
January 17, 2017 at 2:18 pm
David.Poole - Tuesday, January 17, 2017 10:50 AMRod at work - Tuesday, January 17, 2017 9:00 AMGood point that you brought up. I know I've written queries which didn't return what was expected. I've corrected them, but it certainly was embarrassing to have written such a query.That, my friend, is a rite of passage.
Along your journey to enlightenment will be the UPDATE statement with the missing WHERE clause, the EAV model, the one true lookup table and a few that my learned friends will shuffle their feet and look sheepish about
Once I just clicked on the sql editor and hit execute, then wondered why so many records were deleted. Somehow my click had perfectly selected a delete statement without the where clause.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply