August 13, 2015 at 9:28 am
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
I like how his first reply to your request for more information is just "yes...". 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2015 at 9:30 am
Eirikur Eiriksson (8/13/2015)
Jacob Wilkins (8/13/2015)
Heh, things like that with names always make me shake my head. I recently ran across a database that had table in the dbo schema that also had 'dbo.' as the beginning of the table name. A tad annoying, but the same instance also has a database with 90,000 tables, so I really should expect such things 🙂IIRC it was common on SQL 2000 when trying to assign schema names to tables via database diagram.
😎
Ah, interesting. Thanks Erikur! I'll have to look that up, since I haven't heard of that before. I was just assuming someone tried to dynamically generate some CREATE TABLE statements and got the string construction wrong.
August 13, 2015 at 9:30 am
Koen Verbeeck (8/13/2015)
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
I like how his first reply to your request for more information is just "yes...". 😀
https://www.youtube.com/watch?v=FJbmB9k2Y88
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 9:30 am
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
"I need a script"?
😎
August 13, 2015 at 9:32 am
Eirikur Eiriksson (8/13/2015)
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
"I need a script"?
😎
"I need help to do what they asked me to do"?
August 13, 2015 at 9:33 am
Sean Lange (8/13/2015)
Ed Wagner (8/13/2015)
Sean Lange (8/13/2015)
Every table name and every column name are exactly 6 UPPER case letters.I feel your pain. Baan's tables were named using a multi-part naming convention that made it a bit silly to try and remember. Example:
t$ttdsls040400
t$ttdsls041400
t$ = Prefix necessary for all tables and columns.
t = Prefix that does nothing except add another "t" to the table name.
td = Package the table is a part of. TD, logically, means Triton Distribution.
sls = Module the table is a part of. SLS means Sales.
040 = Which table it is. Naturally, 040 is Sales Orders and 041 is Sales Orders Lines. Not Sales Order Lines, but Sales Orders Lines.
400 = The company number. You can have multiple companies set up in one database, each with their own set of tables. The company number is the suffix for everything.
The columns where not quite as bad, but almost:
t$orno
t$pono
t$txta
t$txtb
t$ = Prefix necessary for all tables and columns.
next 4 = Field name. Clearly, orno = Order Number, pono = Position Number, txta = pointer to the text table for the this text entry. Note I didn't say foreign key, because where were none.
The text table was: t$tttxt001400.
Obviously, the package "tt" is Triton Text, the module "txt" is Text and the table was 001. There was no 002.
And yes, the whole thing was case sensitive in both object names and values.
See why it gave me nightmares?
I know what you mean. All the columns in a table have a 2 character prefix. These prefixes have no meaning as it relates to the table. So you end up with names like PHTXID and PHSCCD.
It is kind of scary I am starting to actually be able to identify some of the more common columns. I know that a column with the last 4 of RGDT is the integer version of the date the row was created. And RGTM is the integer version of the time the row was created. This creates some really interesting words to be echoed through the dev team as something as simple as getting rows created since yesterday require lots of effort to convert those integers into something you can convert to a datetime so it is actually usable.
Yes it does. The 040orno, 041orno and 040pono porn were tossed around and spoken like they were real words. The 045porn raised a few eyebrows at first and sparked some uncomfortable conversations.
August 13, 2015 at 9:35 am
Luis Cazares (8/13/2015)
Eirikur Eiriksson (8/13/2015)
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
"I need a script"?
😎
"I need help to do what they asked me to do"?
That of course and the other 25000 details implied in the question:-D
😎
August 13, 2015 at 9:35 am
Grant Fritchey (8/13/2015)
Lynn Pettis (8/13/2015)
Ed Wagner (8/13/2015)
I've worked in both environments and really prefer case-insensitive. As a habit, though, I try to write everything as case-sensitive.Having been working in case sensitive environments, I learned to write that way. I want my code to be able to work in both case sensitive and case insensitive environments without having to make any changes.
If a table is called MYTABLE, that's how I write it in my queries. If it is called MyTable, same thing. I hate seeing code where people use MyCol, MYCOL, mycol interchangeably because it happens to work because the system is using a case insensitive collation.
Not to be too marketing focused, but, I have a solution [/url]for the case sensitive issue... Just sayin'.
This works great for the t-sql side of things but where predicates are a serious challenge to deal with when the entry is a textbox and there is nothing to enforce a given case. I know you can add a collation in your query but it is easy to forget and seems completely pointless to constantly defeat the collation to get data back out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 9:43 am
Luis Cazares (8/13/2015)
Eirikur Eiriksson (8/13/2015)
Grant Fritchey (8/13/2015)
WayneS (8/13/2015)
Crystal Ball, report to Aisle 5I'm sure that a large part of this is English as a second language. But lordy, can the questions be worded more vaguely?
"I need a script"?
😎
"I need help to do what they asked me to do"?
<sigh> winner
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 9:44 am
Sean Lange (8/13/2015)
Grant Fritchey (8/13/2015)
Lynn Pettis (8/13/2015)
Ed Wagner (8/13/2015)
I've worked in both environments and really prefer case-insensitive. As a habit, though, I try to write everything as case-sensitive.Having been working in case sensitive environments, I learned to write that way. I want my code to be able to work in both case sensitive and case insensitive environments without having to make any changes.
If a table is called MYTABLE, that's how I write it in my queries. If it is called MyTable, same thing. I hate seeing code where people use MyCol, MYCOL, mycol interchangeably because it happens to work because the system is using a case insensitive collation.
Not to be too marketing focused, but, I have a solution [/url]for the case sensitive issue... Just sayin'.
This works great for the t-sql side of things but where predicates are a serious challenge to deal with when the entry is a textbox and there is nothing to enforce a given case. I know you can add a collation in your query but it is easy to forget and seems completely pointless to constantly defeat the collation to get data back out.
Shhhhhhhh....
Just go spend money with Redgate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 9:46 am
Ed Wagner (8/13/2015)
Sean Lange (8/13/2015)
Ed Wagner (8/13/2015)
Sean Lange (8/13/2015)
Every table name and every column name are exactly 6 UPPER case letters.I feel your pain. Baan's tables were named using a multi-part naming convention that made it a bit silly to try and remember. Example:
t$ttdsls040400
t$ttdsls041400
t$ = Prefix necessary for all tables and columns.
t = Prefix that does nothing except add another "t" to the table name.
td = Package the table is a part of. TD, logically, means Triton Distribution.
sls = Module the table is a part of. SLS means Sales.
040 = Which table it is. Naturally, 040 is Sales Orders and 041 is Sales Orders Lines. Not Sales Order Lines, but Sales Orders Lines.
400 = The company number. You can have multiple companies set up in one database, each with their own set of tables. The company number is the suffix for everything.
The columns where not quite as bad, but almost:
t$orno
t$pono
t$txta
t$txtb
t$ = Prefix necessary for all tables and columns.
next 4 = Field name. Clearly, orno = Order Number, pono = Position Number, txta = pointer to the text table for the this text entry. Note I didn't say foreign key, because where were none.
The text table was: t$tttxt001400.
Obviously, the package "tt" is Triton Text, the module "txt" is Text and the table was 001. There was no 002.
And yes, the whole thing was case sensitive in both object names and values.
See why it gave me nightmares?
I know what you mean. All the columns in a table have a 2 character prefix. These prefixes have no meaning as it relates to the table. So you end up with names like PHTXID and PHSCCD.
It is kind of scary I am starting to actually be able to identify some of the more common columns. I know that a column with the last 4 of RGDT is the integer version of the date the row was created. And RGTM is the integer version of the time the row was created. This creates some really interesting words to be echoed through the dev team as something as simple as getting rows created since yesterday require lots of effort to convert those integers into something you can convert to a datetime so it is actually usable.
Yes it does. The 040orno, 041orno and 040pono porn were tossed around and spoken like they were real words. The 045porn raised a few eyebrows at first and sparked some uncomfortable conversations.
Even if porn were not a case sensitive publication in that database, I am sure the mistake would have happened with pono and orno.
You know the designer of the database was giggling in some dark closet as s/he came up with the conventions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 9:52 am
Views upon views upon #$%$%^%$#$ views! The same damn table is getting queried 8 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2015 at 9:53 am
SQLRNNR (8/13/2015)
Ed Wagner (8/13/2015)
Sean Lange (8/13/2015)
Ed Wagner (8/13/2015)
Sean Lange (8/13/2015)
Every table name and every column name are exactly 6 UPPER case letters.I feel your pain. Baan's tables were named using a multi-part naming convention that made it a bit silly to try and remember. Example:
t$ttdsls040400
t$ttdsls041400
t$ = Prefix necessary for all tables and columns.
t = Prefix that does nothing except add another "t" to the table name.
td = Package the table is a part of. TD, logically, means Triton Distribution.
sls = Module the table is a part of. SLS means Sales.
040 = Which table it is. Naturally, 040 is Sales Orders and 041 is Sales Orders Lines. Not Sales Order Lines, but Sales Orders Lines.
400 = The company number. You can have multiple companies set up in one database, each with their own set of tables. The company number is the suffix for everything.
The columns where not quite as bad, but almost:
t$orno
t$pono
t$txta
t$txtb
t$ = Prefix necessary for all tables and columns.
next 4 = Field name. Clearly, orno = Order Number, pono = Position Number, txta = pointer to the text table for the this text entry. Note I didn't say foreign key, because where were none.
The text table was: t$tttxt001400.
Obviously, the package "tt" is Triton Text, the module "txt" is Text and the table was 001. There was no 002.
And yes, the whole thing was case sensitive in both object names and values.
See why it gave me nightmares?
I know what you mean. All the columns in a table have a 2 character prefix. These prefixes have no meaning as it relates to the table. So you end up with names like PHTXID and PHSCCD.
It is kind of scary I am starting to actually be able to identify some of the more common columns. I know that a column with the last 4 of RGDT is the integer version of the date the row was created. And RGTM is the integer version of the time the row was created. This creates some really interesting words to be echoed through the dev team as something as simple as getting rows created since yesterday require lots of effort to convert those integers into something you can convert to a datetime so it is actually usable.
Yes it does. The 040orno, 041orno and 040pono porn were tossed around and spoken like they were real words. The 045porn raised a few eyebrows at first and sparked some uncomfortable conversations.
Even if porn were not a case sensitive publication in that database, I am sure the mistake would have happened with pono and orno.
You know the designer of the database was giggling in some dark closet as s/he came up with the conventions.
Not to mention some of the other common names that make you think twice.
CONO, CUNO, CUNM
There are plenty of column names that are far more HR hostile than these.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 9:56 am
GilaMonster (8/13/2015)
Views upon views upon #$%$%^%$#$ views! The same damn table is getting queried 8 times.
That reminds me of a job posting I recently received on the mail. Even if looking for a job, I'd try to avoid this place.
JOB DETAILS:
Role: Performance Tuner
Expertise in SQL Database Performance tuning.
Database Server profiling and identifying necessary indexes to be created/dropped to improve performance.
Ability to analyze existing database views that cross databases for performance improvements.
Ability to analyze database activity and identify potential for creating additional views to decrease the number of database calls.
Technologies Expert on Microsoft SQL Profiler and Microsoft SQL Server 2008 SQL Stored Procedures Cross Database Views
August 13, 2015 at 9:56 am
Ed Wagner (8/13/2015)
Yes it does. The 040orno, 041orno and 040pono porn were tossed around and spoken like they were real words. The 045porn raised a few eyebrows at first and sparked some uncomfortable conversations.
I like my TMSHT table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 50,176 through 50,190 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply