February 25, 2015 at 8:08 pm
Comments posted to this topic are about the item Building Small Habits
February 26, 2015 at 1:29 am
Erm...this highlighted that in some areas I am a SSMS GUI SQL Developer. Basically, when creating a database schema I tend to use the SSMS GUI tools and then generate a script. As I always name primary keys (indeed the GUI does a reasonable, but in my opinion unsatisfactory, job of naming by default) I had completely forgotten that the default naming using that syntax was as unhelpful as it is.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 26, 2015 at 2:32 am
I do wish that SQL server gave sensible default names to constraints. Would also be nice if constraint names were only scoped to object they belong to so you could skip the need to put the table name in each constraint.
I'm not aware of any where in t-sql where you can refer to a constraint by name alone without needing to specify the table name the constraint belongs to, which just makes the whole table name in the constraint pointless duplication.
February 26, 2015 at 2:47 am
I'm sort of the other way round Gary. Oh I get the CREATE TABLE through the GUI but I don't add anything to it. I add each of the defaults in a block. Then the check constraints, followed by the indexes, and lastly the foreign keys. I think that it is more readable than the CREATE TABLE all in one go. It pretty much precludes anything being system named.
I'm giving serious consideration to the suggestion by Aaron to use = rather than AS for column aliases. One bad habit that I'm going to break as of right now is failing to use AS for table aliases. I have been in the habit of spacing the alias away from the table name so that all of the ONs line up in the joins. Rather than that I'm going to start using the AS.
ATBCharles Kincaid
February 26, 2015 at 3:24 am
About 4 or 5 years ago at the start of a new project we decided to start prefixing our object names with their type. For example; SomeObjectName became tblSomeObjectName for a table and PolicyNumber became iPolicyNumber if it was an integer or cPolicyNumber if it was a char. It seemed like a bit of a silly thing at the time but now, writing queries, I very rarely have implicit casting issues. Going back and working on old databases where we haven't followed a strict convention like that I find myself looking up table definitions to find out what data type a column holds so as to get around parse errors from datatype mismatches etc.
A very small change to coding practise, but a surprisingly helpful one later on!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 26, 2015 at 4:00 am
BenWard (2/26/2015)
About 4 or 5 years ago at the start of a new project we decided to start prefixing our object names with their type. For example; SomeObjectName became tblSomeObjectName for a table and PolicyNumber became iPolicyNumber if it was an integer or cPolicyNumber if it was a char. It seemed like a bit of a silly thing at the time but now, writing queries, I very rarely have implicit casting issues. Going back and working on old databases where we haven't followed a strict convention like that I find myself looking up table definitions to find out what data type a column holds so as to get around parse errors from datatype mismatches etc.A very small change to coding practise, but a surprisingly helpful one later on!
Hungarian Notation!!!..........aaaaaaaaggggggghhhhhhh.........
Generally considered bad practice, however, if it works for you.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 26, 2015 at 4:07 am
This kind of problem makes database upgrades hard, because the object names differ from database to database. This to me, is a bad practice.
I had this kind of problem with old databases, essentially with Defaults and Checks. In this case, I have two handy scripts to correct these object names and make my database upgrades much easier.
-- Rename all Checks
SELECT 'exec sp_rename ''' + SC.name + ''', ''CK_' + OBJECT_NAME(SC.parent_object_id) + '_' + C.name + ''', ''object'';'
FROM sys.check_constraints AS SC
INNER JOIN sys.all_columns AS C
ON SC.parent_object_id = C.object_id
AND C.column_id = SC.parent_column_id
ORDER BY SC.name;
-- Rename all Defaults
SELECT 'exec sp_rename ''' + SC.name + ''', ''DF_' + OBJECT_NAME(SC.parent_object_id) + '_' + C.name + ''', ''object'';'
FROM sys.default_constraints AS SC
INNER JOIN sys.all_columns AS C
ON SC.parent_object_id = C.object_id
AND C.column_id = SC.parent_column_id
ORDER BY SC.name;
February 26, 2015 at 4:18 am
Gary Varga (2/26/2015)
BenWard (2/26/2015)
About 4 or 5 years ago at the start of a new project we decided to start prefixing our object names with their type. For example; SomeObjectName became tblSomeObjectName for a table and PolicyNumber became iPolicyNumber if it was an integer or cPolicyNumber if it was a char. It seemed like a bit of a silly thing at the time but now, writing queries, I very rarely have implicit casting issues. Going back and working on old databases where we haven't followed a strict convention like that I find myself looking up table definitions to find out what data type a column holds so as to get around parse errors from datatype mismatches etc.A very small change to coding practise, but a surprisingly helpful one later on!
Hungarian Notation!!!..........aaaaaaaaggggggghhhhhhh.........
Generally considered bad practice, however, if it works for you.
lol not quite hungarian notation 😉 a much much simplified version thereof. But I get that some people hate it !
As with all things it depends on your environment, we only have 3 or 4 SQL Server databases here that we have developed ourselves, the rest of them are back-end databases to 3rd party applications which we wouldn't get to creating objects in anyway. Almost all of this particular application is within SQL Server and the bits that aren't are written in PHP which has almost no concept of a datatype. As such for our particular usage scenario where the database is largely self contained and has very little in the way of changes going on, most of our changes relate to writing some new report to extract some data or generate some statistics rather than changing the database itself or its structure. The benefits of HN are still applicable in our environment whereas the problems it can cause so far have not caused any issues at all.
I don't think I'd want to use it in OO programming though! I've recently got into Java and using HN there would make my brain hurt a lot!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 26, 2015 at 4:36 am
Hungarian Notation is one of those things that seems like a good idea. Having dealt with examples like a variable in a statically typed language called b[BusinessTerm] which suggested it was a boolean but was actually a string used to hold numeric data, I dislike HN...also as a half-Hungarian I take umbrage as it doesn't look like Hungarian to me 😉
Sometimes the problem isn't with the concept but when the implementation of the idea is not maintained.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 26, 2015 at 4:53 am
Gary Varga (2/26/2015)
Hungarian Notation is one of those things that seems like a good idea. Having dealt with examples like a variable in a statically typed language called b[BusinessTerm] which suggested it was a boolean but was actually a string used to hold numeric data, I dislike HN...also as a half-Hungarian I take umbrage as it doesn't look like Hungarian to me 😉Sometimes the problem isn't with the concept but when the implementation of the idea is not maintained.
I feel your pain! I've found the biggest issue when it comes to 'best practise' of any kind is where it's not maintained. Strict 'best practise' across 92% of your code base is, in my opinion, worse than 0% adherence because you get to expect certain things that cause a lot of pain when you get to the 8% of the code that doesn't follow. Even simple things like capitalisation of SQL keywords and code indentation, if you're used to reading it a certain way, coming across code that's not formatted the same takes longer to comprehend (at least it does for me!).
Re looking like Hungarian - Isn't that down the misconception that Hungarian is a Slavic language when it is in fact Uralic? We should probably start calling it Bulgarian notation! 😉 (or to be more politically correct; Consonant-weighted Notation)
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 26, 2015 at 7:47 am
BenWard (2/26/2015)
Gary Varga (2/26/2015)
Hungarian Notation is one of those things that seems like a good idea. Having dealt with examples like a variable in a statically typed language called b[BusinessTerm] which suggested it was a boolean but was actually a string used to hold numeric data, I dislike HN...also as a half-Hungarian I take umbrage as it doesn't look like Hungarian to me 😉Sometimes the problem isn't with the concept but when the implementation of the idea is not maintained.
I feel your pain! I've found the biggest issue when it comes to 'best practise' of any kind is where it's not maintained. Strict 'best practise' across 92% of your code base is, in my opinion, worse than 0% adherence because you get to expect certain things that cause a lot of pain when you get to the 8% of the code that doesn't follow. Even simple things like capitalisation of SQL keywords and code indentation, if you're used to reading it a certain way, coming across code that's not formatted the same takes longer to comprehend (at least it does for me!).
Re looking like Hungarian - Isn't that down the misconception that Hungarian is a Slavic language when it is in fact Uralic? We should probably start calling it Bulgarian notation! 😉 (or to be more politically correct; Consonant-weighted Notation)
My (Hungarian) Dad's 3rd wife is Bulgarian!!! ??
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 26, 2015 at 9:19 am
Of course containing "date" values in VarChar columns is a bad idea from the start, and once it's done, it's no small change to undo, because there are applications and reports all of the place that reference it. But if you inherit a database with this particular feature, the following constraint can be used to help mitigate potential issues by insuring that only values conforming to a specific format are inserted.
Here I'd suggest YYYYMMDD, because it's ISO standard and converts consistently regardless of database localization. But you can retrofit this to accept most any date/time representation.
What this constraint does is CAST the value as a Date, CONVERT that date to a string in YYYYMMDD format, and then compare that string back to the value inserted. If no match, then the constraint is false and the insertion rejected. This also prevents invalid dates like '00000000' or '20120229'.
create table foo
(
foo_date varchar(30) not null
constraint ck_foo_date_yyyymmdd
check (foo_date = convert(char(8),cast(foo_date as date),112))
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 26, 2015 at 11:00 am
Improving on and correcting bad habits, little by little, is good advice, both in coding and in life. Thanks.
February 26, 2015 at 1:26 pm
I adopted this convention about 3 years and have since managed to introduce it to 2 new companies, it makes a world of difference when deploying code in tightly controlled fashion and for doing schema comparisons
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply