August 11, 2015 at 9:09 am
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2015 at 9:09 am
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yeehaw!
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 11, 2015 at 9:14 am
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
_______________________________________________________________
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 11, 2015 at 9:14 am
Sean Lange (8/11/2015)
Alvin Ramard (8/11/2015)
Sean Lange (8/11/2015)
Alvin Ramard (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
And your point is? 😀
Depending on the intended use, it may not be so bad, but I see a few problem with the design, such as missing key and use of nvarchar.
It's presence does raise a few warning flags.
I guess I left off that they are also NOT NULL. Why make them variable length when they can't be more or less than 1 character? And repeating columns like that is just awful. Would make more sense to spin them off to another table.
I'm guessing the table is intended for clients to implement some sore of customization. One character fields might be ok, but nvarchar instead of varchar? hmmm
Unless the table is only going to have 1 row, where's the key column?
Those 50 columns aren't the whole table. There is a perfectly solid looking table with these 50 columns slapped onto the end. Just seems like such an afterthought, or perhaps a non-thought, that these exist in this table. Sure hope they don't need a ATTRIBUTE51 anytime soon.
Aaaahhhhh, ok.
I'm at a client that's using SAP. A few tables have 64 columns of data type char(1) null, named QryGroup1 to QryGroup64, for user defined usage. These typically have Y or N as data, if used.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 9:18 am
Sean Lange (8/11/2015)
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
This is starting to sound eerily like a wretched, nasty ERP system I used to work with, but I'm happy to say that I don't work with it any longer. It was a complete PITA. Thank goodness for small favors.
August 11, 2015 at 9:24 am
Sean Lange (8/11/2015)
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
SAP, CRM, shuuuuuudder
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 11, 2015 at 9:28 am
SQLRNNR (8/11/2015)
Sean Lange (8/11/2015)
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
SAP, CRM, shuuuuuudder
I'll see that SAP and raise you a Baan. I don't recommend it to anyone. On the bright side, the nightmares have stopped. 😛
August 11, 2015 at 9:34 am
Ed Wagner (8/11/2015)
SQLRNNR (8/11/2015)
Sean Lange (8/11/2015)
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
SAP, CRM, shuuuuuudder
I'll see that SAP and raise you a Baan. I don't recommend it to anyone. On the bright side, the nightmares have stopped. 😛
Baan doesn't exist anymore. It has been assimilated into Infor M3 which is the nightmare we are migrating to. Kind of sad really...was thinking this would be the company I retire from but now thinking this isn't as likely. :angry:
_______________________________________________________________
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 11, 2015 at 9:39 am
Sean Lange (8/11/2015)
Ed Wagner (8/11/2015)
SQLRNNR (8/11/2015)
Sean Lange (8/11/2015)
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
There are only about 2,000 rows in this table so far (this is part of a bigger system we are moving to) and every single column in every single row is a single space right now. My guess is they were inserted as empty strings.
SAP, CRM, shuuuuuudder
I'll see that SAP and raise you a Baan. I don't recommend it to anyone. On the bright side, the nightmares have stopped. 😛
Baan doesn't exist anymore. It has been assimilated into Infor M3 which is the nightmare we are migrating to. Kind of sad really...was thinking this would be the company I retire from but now thinking this isn't as likely. :angry:
I do not envy you.
It is good to hear that Baan is no more, but I'm not surprised. I guess their miserable product finally caught up with them.
August 11, 2015 at 9:42 am
Jack Corbett (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
Yuck. That's even worse than one of the 3rd-party apps I support. At least my app doesn't use unicode and variable length for short hings. My app has a little too much CHAR(78) type of columns.
I can't imagine a use of nvarchar(1). Are the values either 0 and 1 or 'Y' or 'N'? So it could be a bit column?
We converted almost all of our VARCHAR columns to NVARCHAR columns since we now need to support languages like Hangol (spelling?).
August 11, 2015 at 9:43 am
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
VARCHAR(1) and NVARCHAR(1) are my absolute favorite "stupid database design" problems. Seriously. Love them. I point and laugh and laugh. Weird how the database design people don't appreciate the humor.
"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 11, 2015 at 9:46 am
Grant Fritchey (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
VARCHAR(1) and NVARCHAR(1) are my absolute favorite "stupid database design" problems. Seriously. Love them. I point and laugh and laugh. Weird how the database design people don't appreciate the humor.
I sort of get it when they allow NULL. I wouldn't design anything like that but I can understand where that comes from. But to allow variable length and force a single character just kills me. :-D:-D:-D
_______________________________________________________________
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 11, 2015 at 9:49 am
Sean Lange (8/11/2015)
Grant Fritchey (8/11/2015)
Sean Lange (8/11/2015)
OMG. I just ran across this in a database from a 3rd party. There are actually 50 nvarchar(1) columns with names 1 - 50. :w00t:
ATTRIBUTE1 nvarchar(1)
ATTRIBUTE2 nvarchar(1)
.
.
.
ATTRIBUTE50 nvarchar(1)
VARCHAR(1) and NVARCHAR(1) are my absolute favorite "stupid database design" problems. Seriously. Love them. I point and laugh and laugh. Weird how the database design people don't appreciate the humor.
I sort of get it when they allow NULL. I wouldn't design anything like that but I can understand where that comes from. But to allow variable length and force a single character just kills me. :-D:-D:-D
I especially love it when they use an NVARCHAR(1) to store a 0 or 1. Unfortunately, that's a real-life example.
August 11, 2015 at 9:50 am
Jack Corbett (8/10/2015)
Steve Jones - SSC Editor (8/10/2015)
Query query, sql sql, should we debate tomatoes and potatoes as well?How about "daytah" vs. "dahtah"?
I thought Patrick Steward sorted that one once and for all?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 11, 2015 at 10:06 am
andrew gothard (8/11/2015)
Jack Corbett (8/10/2015)
Steve Jones - SSC Editor (8/10/2015)
Query query, sql sql, should we debate tomatoes and potatoes as well?How about "daytah" vs. "dahtah"?
I thought Patrick Steward sorted that one once and for all?
He might have, but Dr. Pulaski didn't know when she first join the crew. Data had to correct her when she asked: "What's the difference?"
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 50,041 through 50,055 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply