July 25, 2019 at 4:27 am
Jeff Moden wrote:On that same note, I'd also change the datatype for "IsBillable" and "IsActive" to TINYINT and use a 1 for "Yes" and a 0 for "No" along with constraints to ensure that only 1 or 0 could ever be used in the column. I wouldn't use the BIT datatype here, either. The reason for the proposed change is it will make coding damned easy to determine how many projects are billable or active using a simple SUM aggregate. Y/N and BIT datatypes will require a conversion to aggregate them.
- You would use TINYINT with 1 or 0 so you can do a SUM aggregate. If you used Y/N, couldn't you get the same result by using some function to count the number of Y's in a column?
- Y/N and BIT data types will require a conversion. A conversion to what?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2019 at 4:43 am
Jeff, What do you mean when you say doing vertical alignment. I noticed you also mentioned that in your past last night.
To explain what I mean by vertical alignment, one picture is worth a thousand words.
Here's the original code you posted. It has virtually no vertical alignment. There is no clear "field" for the column names nor the datatypes nor the nullability. In order to review (for example) all of the datatypes in this code, you're eyes have to look at a different position in each row to find the datatype.
CREATE TABLE ProjectCodes
(
ProjectID varchar(22),
ProjectName varchar(25),
Level char(1),
[Project Classification] varchar(14),
[Project Type] varchar(11),
Billable char(1), DEFAULT ('Y'),
[Allow Charging] char(1), DEFAULT ('Y'),
Active char(1), DEFAULT ('Y'),
[Contract No] char(17),
[Task Order No] char(17),
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
Here's the code I posted... each logic part of each column definition (column name, data type, nullability) on each row are all left aligned on each row. Your eye doesn't have to look for where (for example) the datatypes are in a row because they all appear in the same column (field) and the datatype "field" has no overlap with either the column name "field" or the nullablity "field". I also call this "square code". I'll also tell you that interviewers love it when they ask if you brought any code samples with you and you show them something this "squared away". Since Windows and SSMS have also gotten better at allowing "vertical selection", it also saves me a huge amount of time when I need to do something like copy all of the column names for another section of code. Since the spaces are copied as a part of the square code, the copy is also square code.
CREATE TABLE dbo.ProjectCodes
(
ProjectID VARCHAR(22) NOT NULL,
ProjectName VARCHAR(25) NOT NULL,
Level CHAR(1) NOT NULL,
[Project Classification] VARCHAR(14) NOT NULL,
[Project Type] VARCHAR(11) NOT NULL,
Billable CHAR(1) NOT NULL DEFAULT ('Y'),
[Allow Charging] CHAR(1) NOT NULL DEFAULT ('Y'),
Active CHAR(1) NOT NULL DEFAULT ('Y'),
[Contract No] CHAR(17) NOT NULL,
[Task Order No] CHAR(17) NOT NULL,
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2019 at 8:28 am
when I drop a column name like Policy_Type into the report, SSRS is smart enough to replace the underscore with a space for the column name on the report.
Actually SSRS will do the same for Camel and Pascal case, i.e. both policyType and PolicyType will have a space inserted in the heading.
Far away is close at hand in the images of elsewhere.
Anon.
July 25, 2019 at 1:47 pm
below86 wrote:when I drop a column name like Policy_Type into the report, SSRS is smart enough to replace the underscore with a space for the column name on the report.
Actually SSRS will do the same for Camel and Pascal case, i.e. both policyType and PolicyType will have a space inserted in the heading.
Thanks for the correction, I thought yesterday when I tested it that the Pascal hadn't included the spaces. I just tested it again and now I see the spaces for the Pascal type column names, weird, not sure what happened yesterday.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 25, 2019 at 1:56 pm
below86 wrote:but I still prefer the 'AS' method instead of '='. 😀
The reason I prefer the "=" method is simply because of my habit to do vertical alignment for readability and because some of the formulas we use are terribly long. It also puts SELECTs into the same form as UPDATEs. I realized in UPDATEs that, when I'm looking for a column name, it's easier to find for me and so adopted the same method for SELECTs. I also used to use trailing commas but then found joy in leading commas for the same reason. It took a long time for me to adopt the leading comma thing until I realized that I always did leading AND's and OR's for the same reasons. After that, it was a pretty easy switch for me. Not sure how I went so long without doing both. And, just to be sure, no... I'm absolutely not criticizing anyone for using "AS" and trailing commas. The fastest way I know of to get into a fight with someone is to tell them how to format their aliases and commas.
As the saying goes "many ways around the barn". I just want it to be consistent, I hate when I see code that has some trailing commas and some leading, drives me nuts. Just to plays devils advocate, why don't you have leading commas in your Create statement example you posted? 😉 I ALWAYS do leading AND's and OR's, still not on board with the leading commas though. :-p
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 25, 2019 at 3:34 pm
I just want it to be consistent, I hate when I see code that has some trailing commas and some leading, drives me nuts.
Amen to that!!! If someone else wrote code and it's not totally obnoxious, I'll follow whatever form they're using.
Just to plays devils advocate, why don't you have leading commas in your Create statement example you posted?
See above. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2019 at 10:46 pm
Jeff Moden,
The vertical alignment of the code looks very nice. I can see how it is much easier to read.
What do you mean with SSMS doing vertical selection?
I take that to mean that you can select just the data types (for example) and leave everything else behind. I tried this first with tabs between column name and data type and NULLs, then I tried it with spaces. I was not able to select only the data types.
July 27, 2019 at 1:00 am
Jeff Moden, The vertical alignment of the code looks very nice. I can see how it is much easier to read. What do you mean with SSMS doing vertical selection? I take that to mean that you can select just the data types (for example) and leave everything else behind. I tried this first with tabs between column name and data type and NULLs, then I tried it with spaces. I was not able to select only the data types.
To do a "vertical selection" in SSMS, move the mouse pointer to one of the corners of the "rectangle" of data that you wish to select. Just out of habit, that's almost always the top left corner for me. Don't click the mouse button, yet. Press and hold the {alt} key down, click and hold the mouse button down, and then drag the mouse down and to the right until you've selected the rectangular area you want. Then release the mouse button and the {alt} key. From there, you can do just about anything that you would with a normal selection.
In the later versions of SSMS, you can actually select a thin vertical line of "no characters". Then when you type something, it will be typed on all of the lines at once.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2019 at 6:37 pm
michael.leach2015 wrote:Jeff Moden, The vertical alignment of the code looks very nice. I can see how it is much easier to read. What do you mean with SSMS doing vertical selection? I take that to mean that you can select just the data types (for example) and leave everything else behind. I tried this first with tabs between column name and data type and NULLs, then I tried it with spaces. I was not able to select only the data types.
To do a "vertical selection" in SSMS, move the mouse pointer to one of the corners of the "rectangle" of data that you wish to select. Just out of habit, that's almost always the top left corner for me. Don't click the mouse button, yet. Press and hold the {alt} key down, click and hold the mouse button down, and then drag the mouse down and to the right until you've selected the rectangular area you want. Then release the mouse button and the {alt} key. From there, you can do just about anything that you would with a normal selection. In the later versions of SSMS, you can actually select a thin vertical line of "no characters". Then when you type something, it will be typed on all of the lines at once.
I tried this and it worked. Looks like it doesn't matter if the column name and data types are separated by tabs or spaces.
Thank you very much for this tip. I really like it. I can see how this will save a lot of time.
You have many very helpful suggestions. Although I am a beginner I feel like my learning is improving exponentially. Thank you again.
July 27, 2019 at 6:47 pm
Jeff Moden wrote:michael.leach2015 wrote:Jeff Moden, The vertical alignment of the code looks very nice. I can see how it is much easier to read. What do you mean with SSMS doing vertical selection? I take that to mean that you can select just the data types (for example) and leave everything else behind. I tried this first with tabs between column name and data type and NULLs, then I tried it with spaces. I was not able to select only the data types.
To do a "vertical selection" in SSMS, move the mouse pointer to one of the corners of the "rectangle" of data that you wish to select. Just out of habit, that's almost always the top left corner for me. Don't click the mouse button, yet. Press and hold the {alt} key down, click and hold the mouse button down, and then drag the mouse down and to the right until you've selected the rectangular area you want. Then release the mouse button and the {alt} key. From there, you can do just about anything that you would with a normal selection. In the later versions of SSMS, you can actually select a thin vertical line of "no characters". Then when you type something, it will be typed on all of the lines at once.
I tried this and it worked. Looks like it doesn't matter if the column name and data types are separated by tabs or spaces. Thank you very much for this tip. I really like it. I can see how this will save a lot of time. You have many very helpful suggestions. Although I am a beginner I feel like my learning is improving exponentially. Thank you again.
Correct. It doesn't matter if it's tabs or spaces. And, if you consider the column names example I gave, now you also understand one of the reasons why I like leading commas... a lot!
You have many very helpful suggestions. Although I am a beginner I feel like my learning is improving exponentially. Thank you again.
That, good Sir, just made my day. Thank you very much for the great feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2019 at 1:07 am
"Correct. It doesn't matter if it's tabs or spaces. And, if you consider the column names example I gave, now you also understand one of the reasons why I like leading commas... a lot!"
I gave this some thought and I'm not quite seeing it. To me, leading commas make it hard to follow where a column definition terminates that's why I use commas at the end. Why do you use leading commas?
July 30, 2019 at 1:12 am
Jeff (and others),
I forgot to ask, when you posted my code formatted with vertical alignment, you used Pascal case for the column names like I did. Did you do that because that is what you prefer or because that is what I had and you were keeping the column names the same?
In short,
July 30, 2019 at 12:38 pm
Please also pick up on Phil's suggestion regarding the naming of defaults. This will save you so much pain when you put your code into source control and also when you generate scripts to deploy to other environments (eg. Dev to Test to Prod).
July 30, 2019 at 1:41 pm
"Correct. It doesn't matter if it's tabs or spaces. And, if you consider the column names example I gave, now you also understand one of the reasons why I like leading commas... a lot!"
I gave this some thought and I'm not quite seeing it. To me, leading commas make it hard to follow where a column definition terminates that's why I use commas at the end. Why do you use leading commas?
The leading or trailing comma is just a personal preference, do what you like. I too prefer the trailing.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 30, 2019 at 1:44 pm
In the later versions of SSMS, you can actually select a thin vertical line of "no characters". Then when you type something, it will be typed on all of the lines at once.
Now that is kind of cool, I may have to see if we can move up to a newer version.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply