November 6, 2015 at 9:06 am
it would have been a nice question if the right answer had been correct, but with the nonsensical and simply applling "correct" answer it must be in the running for the worst QotD ever.
What one should do is use best practise.
It can't be best practise to write code which won't even run because it's syntactically incorrect. Claiming that writing syntactically valid code is not something one should not do is just plain nonsense, obviously playing silly games to produce a disgraceful trick question.
It certainly isn't best practise to omit the schema name, since it creates performance overheads and increases the scope for future name clashes that will introduce errors. Claiming that it is best practise suggests that the author doesn't know what he is talking about.
The only thing that's right about this is that the sp_ prefix is to be avoided (unless one is writing a system stored procedure as part of the SQL Server product, of course).
Tom
November 6, 2015 at 10:18 am
I've changed the question to removed the syntax errors.
All points awarded back.
I didn't think this was as misleading as others, but I can see the issues.
November 7, 2015 at 10:27 am
paul 25096 (11/4/2015)Suggest this guy doesn't post more questions.
Got it shan't ask any more question, take this to mean support questions too, so will try google in future! Thought this was a supportive community. apparently not! Sorry it is so full of sychophants.
Cheers
...
November 9, 2015 at 7:05 am
.... (11/7/2015)
paul 25096 (11/4/2015)Suggest this guy doesn't post more questions.
Got it shan't ask any more question, take this to mean support questions too, so will try google in future! Thought this was a supportive community. apparently not! Sorry it is so full of sychophants.
Cheers
I'm afraid your attribution is wrong: it wasn't me (but paul s-306273) who suggested you didn't post any more questions.
My post observed why my answer of 1,2,3 was marked wrong when I thought I'd got it right, and was my first ever on the site. I therefore can't claim to be part of the community yet, but I don't see the comments in this conversation as unsupportive on the whole, just the reaction of people who felt unfairly tricked. The feedback that the question could have been clearer was given in good faith and I hope you take it as such.
November 9, 2015 at 8:41 am
g.britton (11/4/2015)
Carlo Romagnano (11/4/2015)
If I want to be pedant, the right answer is "All of the above".Because of lacking of batch separator "GO".
If you run the script no procedure is created.
+1. Also because we should really always specify the schema.
+1
For a second I though no one would say about not specifying the schema... that's also best practice.
all the above is my favorite one for sure
November 9, 2015 at 1:06 pm
Got it shan't ask any more question, take this to mean support questions too, so will try google in future! Thought this was a supportive community. apparently not! Sorry it is so full of sychophants.
Cheers[/quote]
Don't take it hard that people think your question bad; if everyone who got a lot of complaints about a question stopped producing questions QotD would be dead and gone - no new questions (maybe we could have QotY instead).
Tom
November 9, 2015 at 1:11 pm
TomThomson (11/9/2015)
Got it shan't ask any more question, take this to mean support questions too, so will try google in future! Thought this was a supportive community. apparently not! Sorry it is so full of sychophants.
Cheers
Don't take it hard that people think your question bad; if everyone who got a lot of complaints about a question stopped producing questions QotD would be dead and gone - no new questions (maybe we could have QotY instead).
But only on leap years 😀
November 9, 2015 at 2:53 pm
Thanks for awarding me the point! It was an interesting question. Thanks again!
November 20, 2015 at 11:31 am
What do you think about this old blog ?
Do you agree with the explanations given by the author to reject the sp_ prefix at the beginning of the name of an "user" stored procedure ?
I think that this post is related to SQL Server 2005 and 2008 , but I don't know whether it is correct for the last versions ( 2012 , 2014 and 2016 ).
I am far to be a specialist of query plans , so I take advantage of this QOD to try to be less ignorant about this topic.
Thanks beforehand
November 20, 2015 at 11:51 am
patricklambin (11/20/2015)
What do you think about this old blog ?Do you agree with the explanations given by the author to reject the sp_ prefix at the beginning of the name of an "user" stored procedure ?
I think that this post is related to SQL Server 2005 and 2008 , but I don't know whether it is correct for the last versions ( 2012 , 2014 and 2016 ).
I am far to be a specialist of query plans , so I take advantage of this QOD to try to be less ignorant about this topic.
Thanks beforehand
The advice to not start stored procedure names with sp_ is still current and valid.
You should consider this a reserved prefix. Microsoft can at any time introduce a stored procedure with the same name, and then your code will not work anymore. Also, SQL Server will always have to do extra work when searching for the stored procedure (because it will search in the master database first).
November 20, 2015 at 2:58 pm
Hi Hugo ,
Thanks for your quick reply.
It means that I have understood the Jens's blog who helped me so much when I started on the forums.
Without him , I would not be now a moderator and I provided the link towards this blog as a tiny way to say him : thanks. Moreover , I have discovered that you are ( were ) following him.
Maybe , this blog does not give all the good choices to your QOD but at least it permits me to understand a little more the behavior of the SQL Server engine.
Have a nice day.
November 23, 2015 at 12:29 am
Hugo Kornelis (11/20/2015)
patricklambin (11/20/2015)
What do you think about this old blog ?Do you agree with the explanations given by the author to reject the sp_ prefix at the beginning of the name of an "user" stored procedure ?
I think that this post is related to SQL Server 2005 and 2008 , but I don't know whether it is correct for the last versions ( 2012 , 2014 and 2016 ).
I am far to be a specialist of query plans , so I take advantage of this QOD to try to be less ignorant about this topic.
Thanks beforehand
The advice to not start stored procedure names with sp_ is still current and valid.
You should consider this a reserved prefix. Microsoft can at any time introduce a stored procedure with the same name, and then your code will not work anymore. Also, SQL Server will always have to do extra work when searching for the stored procedure (because it will search in the master database first).
Is there a naming convention that you can recommend ?
i.e. for user tables tbl_
for user's views vw_
for user's functions f_
for procedures p_
or something like this ...
T.a.
Iulian
November 23, 2015 at 2:01 am
Iulian -207023 (11/23/2015)
Is there a naming convention that you can recommend ?i.e. for user tables tbl_
for user's views vw_
for user's functions f_
for procedures p_
or something like this ...
If you are working in an existing environment, I would stick to whatever naming convention is already in place (except that I would still not use sp_ and try to rename existing procedures that use that prefix).
When you start on a new project and have the luxury of proposing the ideal naming convention, I would firmly recommend not using any prefixes at all. For most object names, the context of their usage is sufficient to determine the type (e.g. "EXEC dbo.Xyz;" - in this context, I know that Xyz is a stored procedure and I do not need a p_ or usp_ prefix to be reminded of that).
The only place where there is confusion is in a FROM clause: "SELECT something FROM dbo.Abc WHERE (...)" - in this case, Abc can be either a table or a view (not a function, they always have parentheses). Some people say that using a prefix saves them time when looking up the definition of Abc in the object explorer. There is some merit to that, but not very much - how much time can it take to look in two places? I personally gladly accept this tiny bit of extra work for the flexibility it gives me.
To understand this flexibility, one of the great things you can do with a view is to make changes to the database without affecting existing code. Let's say we have a table "Employees" with a column "HireDate". One day we find that we occassionally rehire someone, and we need to keep botht he first and the second hire date - and the third if we ever fire and then rehire the same person again. So we remove HireDate from the Employees table, ad we create a new table EmploymentPeriods with EmployeeId, HireDate and (nullable) TerminationDate, with a foreign key to the Employees table. But now, a lot of the existing code does not work anymore - all the code has to be changed, and all the changed code has to be tested.
Instead, we could also decide that 99% of the application logic only requires the HireDate of the current employment period, leave that column in the table, and set up logic to try to prevent inconsistencies between the HireDate in the Employees table and the data in the EmploymentPeriods table. My experience is that sooner or later, you will find an inconsistency and need to figure out which version of the truth to use.
So my choice would be to go about it differently. I would rename the Employees table to, e.g. EmployeeData (okay, not the best name). I would then still create the normalized EmploymentPeriods table and remove the HireDate from the EmployeeData table. And I would create a view that joins EmployeeData and EmploymentPeriods to present the user with the data that was in the original Employees table, with the HireDate of the most recent employment period. The benefit of this is that the 99% of the logic that does not need to use the older employment periods can continue to run unchanged - they still reference Employees, which was a table and now is a view; and they receive the same data as before. The change is completely masked. If needed/wanted, they can be scheduled for later change, or they can simply keep using the view indefinitely.
Now imagine a naming standard that uses t_ of tbl_ for tables and v_ or vw_ for views. Using the same method as described above, I now have to choose between either creating a view called t_Employee (which defies the purpose of the prefixes and in fact introduces a heinous layer of complexity), or changing the name from tbl_Employee to vw_Employee (but then I still need to touch that extra 99% of the existing code, and hence need to re-test it as well).
So long story short, if I can choose a naming standard my rules (in random order) would be:
1. No prefixes. (But: See below)
2. Do not use spaces, reserved words, special characters, or anything else that results in having to escape object names with [] or "".
3. For table and view names, use a name that describes the contents. And since a table/view is a set of data, use plural (that also avoids most conflicts with reserved words). (In some cases, a word for the set is okay - e.g. Personnel instead of Employees).
4. I prefer to start with an upper case letter and have the rest in lower case; when an object name combines words, I prefer "PascalCase" - e.g. EmploymentPeriods. I make an exception for standard abbreviations, so I would use "ISOStandards" instead of "IsoStandards".
However, full lowercase and underscores (employment_periods) is also okay. Not sure how to handle abbreviations for this, though.
5. No artificial names created by smashing other names together, or by describing the relationship between two tables. So if you have tables "Employees" and "Projects", the many-to-many relationship between them would not be "EmployeeProjects" or "ProjectEmployees" or "ProjectXrefEmployees" (Yes, I have seen this). Instead, think of what rea-world entities are described by the entries in this table, then use that as the name - so it would probably be "WorkAssignments" or "ProjectAssignments".
6. Adapt to your environment. If you are used to having a table "Employees" but everyone in the organization prefers to call employees "workers", then call the table "Workers". That makes it easier for others in the organization to understand the table and the data.
There is one exception to the "no prefixes" rule. For constraints and indexes I like to use standard prefixes. The primary reason for this is that indexes created for a primary key or unique constraint get the name of the constraint, and when I look at the indexes of a table it helps to know that indexes starting with UQ_ or PK_ are automatically created for a constraint (and hence non-negotiable), whereas indexes starting with ix_ are created for performance and should be periodically reconsidered. Some people like to use different prefixes for different index types, e.g. cix/ncix for clustered/nonclustered, csix for columnstore index, etc. I am still undecided on that one.
There is actually not much reason to also use prefixes FK_, CK_ and DF_ for foreign key constraints, check constraints and defaults - other than consistency between all constraint types.
November 23, 2015 at 7:18 am
Hugo Kornelis (11/23/2015)
Iulian -207023 (11/23/2015)
Is there a naming convention that you can recommend ?i.e. for user tables tbl_
for user's views vw_
for user's functions f_
for procedures p_
or something like this ...
If you are working in an existing environment, I would stick to whatever naming convention is already in place (except that I would still not use sp_ and try to rename existing procedures that use that prefix).
When you start on a new project and have the luxury of proposing the ideal naming convention, I would firmly recommend not using any prefixes at all. For most object names, the context of their usage is sufficient to determine the type (e.g. "EXEC dbo.Xyz;" - in this context, I know that Xyz is a stored procedure and I do not need a p_ or usp_ prefix to be reminded of that).
The only place where there is confusion is in a FROM clause: "SELECT something FROM dbo.Abc WHERE (...)" - in this case, Abc can be either a table or a view (not a function, they always have parentheses). Some people say that using a prefix saves them time when looking up the definition of Abc in the object explorer. There is some merit to that, but not very much - how much time can it take to look in two places? I personally gladly accept this tiny bit of extra work for the flexibility it gives me.
To understand this flexibility, one of the great things you can do with a view is to make changes to the database without affecting existing code. Let's say we have a table "Employees" with a column "HireDate". One day we find that we occassionally rehire someone, and we need to keep botht he first and the second hire date - and the third if we ever fire and then rehire the same person again. So we remove HireDate from the Employees table, ad we create a new table EmploymentPeriods with EmployeeId, HireDate and (nullable) TerminationDate, with a foreign key to the Employees table. But now, a lot of the existing code does not work anymore - all the code has to be changed, and all the changed code has to be tested.
Instead, we could also decide that 99% of the application logic only requires the HireDate of the current employment period, leave that column in the table, and set up logic to try to prevent inconsistencies between the HireDate in the Employees table and the data in the EmploymentPeriods table. My experience is that sooner or later, you will find an inconsistency and need to figure out which version of the truth to use.
So my choice would be to go about it differently. I would rename the Employees table to, e.g. EmployeeData (okay, not the best name). I would then still create the normalized EmploymentPeriods table and remove the HireDate from the EmployeeData table. And I would create a view that joins EmployeeData and EmploymentPeriods to present the user with the data that was in the original Employees table, with the HireDate of the most recent employment period. The benefit of this is that the 99% of the logic that does not need to use the older employment periods can continue to run unchanged - they still reference Employees, which was a table and now is a view; and they receive the same data as before. The change is completely masked. If needed/wanted, they can be scheduled for later change, or they can simply keep using the view indefinitely.
Now imagine a naming standard that uses t_ of tbl_ for tables and v_ or vw_ for views. Using the same method as described above, I now have to choose between either creating a view called t_Employee (which defies the purpose of the prefixes and in fact introduces a heinous layer of complexity), or changing the name from tbl_Employee to vw_Employee (but then I still need to touch that extra 99% of the existing code, and hence need to re-test it as well).
So long story short, if I can choose a naming standard my rules (in random order) would be:
1. No prefixes. (But: See below)
2. Do not use spaces, reserved words, special characters, or anything else that results in having to escape object names with [] or "".
3. For table and view names, use a name that describes the contents. And since a table/view is a set of data, use plural (that also avoids most conflicts with reserved words). (In some cases, a word for the set is okay - e.g. Personnel instead of Employees).
4. I prefer to start with an upper case letter and have the rest in lower case; when an object name combines words, I prefer "PascalCase" - e.g. EmploymentPeriods. I make an exception for standard abbreviations, so I would use "ISOStandards" instead of "IsoStandards".
However, full lowercase and underscores (employment_periods) is also okay. Not sure how to handle abbreviations for this, though.
5. No artificial names created by smashing other names together, or by describing the relationship between two tables. So if you have tables "Employees" and "Projects", the many-to-many relationship between them would not be "EmployeeProjects" or "ProjectEmployees" or "ProjectXrefEmployees" (Yes, I have seen this). Instead, think of what rea-world entities are described by the entries in this table, then use that as the name - so it would probably be "WorkAssignments" or "ProjectAssignments".
6. Adapt to your environment. If you are used to having a table "Employees" but everyone in the organization prefers to call employees "workers", then call the table "Workers". That makes it easier for others in the organization to understand the table and the data.
There is one exception to the "no prefixes" rule. For constraints and indexes I like to use standard prefixes. The primary reason for this is that indexes created for a primary key or unique constraint get the name of the constraint, and when I look at the indexes of a table it helps to know that indexes starting with UQ_ or PK_ are automatically created for a constraint (and hence non-negotiable), whereas indexes starting with ix_ are created for performance and should be periodically reconsidered. Some people like to use different prefixes for different index types, e.g. cix/ncix for clustered/nonclustered, csix for columnstore index, etc. I am still undecided on that one.
There is actually not much reason to also use prefixes FK_, CK_ and DF_ for foreign key constraints, check constraints and defaults - other than consistency between all constraint types.
That sounds excellent , thanks a lot.
Iulian
November 23, 2015 at 7:58 am
Hugo Kornelis (11/23/2015)
Iulian -207023 (11/23/2015)
Is there a naming convention that you can recommend ?i.e. for user tables tbl_
for user's views vw_
for user's functions f_
for procedures p_
or something like this ...
If you are working in an existing environment, I would stick to whatever naming convention is already in place (except that I would still not use sp_ and try to rename existing procedures that use that prefix).
When you start on a new project and have the luxury of proposing the ideal naming convention, I would firmly recommend not using any prefixes at all. For most object names, the context of their usage is sufficient to determine the type (e.g. "EXEC dbo.Xyz;" - in this context, I know that Xyz is a stored procedure and I do not need a p_ or usp_ prefix to be reminded of that).
The only place where there is confusion is in a FROM clause: "SELECT something FROM dbo.Abc WHERE (...)" - in this case, Abc can be either a table or a view (not a function, they always have parentheses). Some people say that using a prefix saves them time when looking up the definition of Abc in the object explorer. There is some merit to that, but not very much - how much time can it take to look in two places? I personally gladly accept this tiny bit of extra work for the flexibility it gives me.
To understand this flexibility, one of the great things you can do with a view is to make changes to the database without affecting existing code. Let's say we have a table "Employees" with a column "HireDate". One day we find that we occassionally rehire someone, and we need to keep botht he first and the second hire date - and the third if we ever fire and then rehire the same person again. So we remove HireDate from the Employees table, ad we create a new table EmploymentPeriods with EmployeeId, HireDate and (nullable) TerminationDate, with a foreign key to the Employees table. But now, a lot of the existing code does not work anymore - all the code has to be changed, and all the changed code has to be tested.
Instead, we could also decide that 99% of the application logic only requires the HireDate of the current employment period, leave that column in the table, and set up logic to try to prevent inconsistencies between the HireDate in the Employees table and the data in the EmploymentPeriods table. My experience is that sooner or later, you will find an inconsistency and need to figure out which version of the truth to use.
So my choice would be to go about it differently. I would rename the Employees table to, e.g. EmployeeData (okay, not the best name). I would then still create the normalized EmploymentPeriods table and remove the HireDate from the EmployeeData table. And I would create a view that joins EmployeeData and EmploymentPeriods to present the user with the data that was in the original Employees table, with the HireDate of the most recent employment period. The benefit of this is that the 99% of the logic that does not need to use the older employment periods can continue to run unchanged - they still reference Employees, which was a table and now is a view; and they receive the same data as before. The change is completely masked. If needed/wanted, they can be scheduled for later change, or they can simply keep using the view indefinitely.
Now imagine a naming standard that uses t_ of tbl_ for tables and v_ or vw_ for views. Using the same method as described above, I now have to choose between either creating a view called t_Employee (which defies the purpose of the prefixes and in fact introduces a heinous layer of complexity), or changing the name from tbl_Employee to vw_Employee (but then I still need to touch that extra 99% of the existing code, and hence need to re-test it as well).
So long story short, if I can choose a naming standard my rules (in random order) would be:
1. No prefixes. (But: See below)
2. Do not use spaces, reserved words, special characters, or anything else that results in having to escape object names with [] or "".
3. For table and view names, use a name that describes the contents. And since a table/view is a set of data, use plural (that also avoids most conflicts with reserved words). (In some cases, a word for the set is okay - e.g. Personnel instead of Employees).
4. I prefer to start with an upper case letter and have the rest in lower case; when an object name combines words, I prefer "PascalCase" - e.g. EmploymentPeriods. I make an exception for standard abbreviations, so I would use "ISOStandards" instead of "IsoStandards".
However, full lowercase and underscores (employment_periods) is also okay. Not sure how to handle abbreviations for this, though.
5. No artificial names created by smashing other names together, or by describing the relationship between two tables. So if you have tables "Employees" and "Projects", the many-to-many relationship between them would not be "EmployeeProjects" or "ProjectEmployees" or "ProjectXrefEmployees" (Yes, I have seen this). Instead, think of what rea-world entities are described by the entries in this table, then use that as the name - so it would probably be "WorkAssignments" or "ProjectAssignments".
6. Adapt to your environment. If you are used to having a table "Employees" but everyone in the organization prefers to call employees "workers", then call the table "Workers". That makes it easier for others in the organization to understand the table and the data.
There is one exception to the "no prefixes" rule. For constraints and indexes I like to use standard prefixes. The primary reason for this is that indexes created for a primary key or unique constraint get the name of the constraint, and when I look at the indexes of a table it helps to know that indexes starting with UQ_ or PK_ are automatically created for a constraint (and hence non-negotiable), whereas indexes starting with ix_ are created for performance and should be periodically reconsidered. Some people like to use different prefixes for different index types, e.g. cix/ncix for clustered/nonclustered, csix for columnstore index, etc. I am still undecided on that one.
There is actually not much reason to also use prefixes FK_, CK_ and DF_ for foreign key constraints, check constraints and defaults - other than consistency between all constraint types.
Good thoughts. In my shop we use singular for entity names. I use FK_, CK_, and DF_ prefixes because I name them after the child table and involved column(s). This ensures that I don't duplicate constraints. Yes it was happening when we had a more random scheme. Thus I can have a check constraint named CK_TBL_col and a default constraint also named DF_TBL_col. A multicolumn foreign key would be named FK_TBL_col1_col2. Yes they can get long and it is not always immediately obvious where the word boundaries are but it works for me. For legacy reasons we use Y/N values in char(1) columns instead of bit which I control with a check constraint rather than an FK to a two row table. I prefer the use of underscores rather than camel case as it makes it easier to break things up for better wrapping in column headers in Excel.
Viewing 15 posts - 61 through 74 (of 74 total)
You must be logged in to reply to this topic. Login to reply