March 27, 2015 at 8:19 am
Steve,
You mention "extra columns in a result set" as being a downside to deploying database changes prior to application code changes. That would only occur with a "select *" type of query, which is poor coding practice. As long as all columns are named in a query, there will be no impact from additional columns that are not referenced.
Eric
March 27, 2015 at 8:35 am
You can use the following to identify what procedures or queries are using "SELECT *".
SELECT DISTINCT objtype, db_name(dbid)dbname
, object_name(objectid,dbid)objname, usecounts, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE [text] LIKE '%SELECT *%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 27, 2015 at 9:24 am
Eric JOATMON (3/27/2015)
Steve,You mention "extra columns in a result set" as being a downside to deploying database changes prior to application code changes. That would only occur with a "select *" type of query, which is poor coding practice. As long as all columns are named in a query, there will be no impact from additional columns that are not referenced.
Eric
Inserts as well. I can have issues if I haven't specified defaults.
March 27, 2015 at 9:35 am
Steve Jones - SSC Editor (3/27/2015)
Eric JOATMON (3/27/2015)
Steve,You mention "extra columns in a result set" as being a downside to deploying database changes prior to application code changes. That would only occur with a "select *" type of query, which is poor coding practice. As long as all columns are named in a query, there will be no impact from additional columns that are not referenced.
Eric
Inserts as well. I can have issues if I haven't specified defaults
I cannot remember how SQL Server handles this but recently I have seen unnamed column inserts and ORDER BY using the column numbers. I have to say that I hate that. As much for poor maintainability and understandability as for deployment issues.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 27, 2015 at 9:49 am
Steve Jones - SSC Editor (3/27/2015)
Eric JOATMON (3/27/2015)
Steve,You mention "extra columns in a result set" as being a downside to deploying database changes prior to application code changes. That would only occur with a "select *" type of query, which is poor coding practice. As long as all columns are named in a query, there will be no impact from additional columns that are not referenced.
Eric
Inserts as well. I can have issues if I haven't specified defaults.
Are you talking about this kind of shite?
insert into MyTable
values ( 1, 9, 9 );
insert into MyTable
select 1, 9, 9;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 27, 2015 at 10:37 am
Order by column number? Initially my application did not order the results in any way. My beta team complained that when looking at a list of 150 or so table names and descriptions not having them sorted was a problem. Same for columns within a table.
Finally we came to the conclusion that all results returned were to be sorted by the leftmost column in all cases. So since I had over 50 queries to revise adding ORDER BY 1 to all of them saved a lot of time.
Yes seeing ORDER BY 1, 17, 42 can be hard to understand and difficult to maintain.
There seems to be a problem with ORDER BY anyway. If I have aliased a column I can't order by the alias name. That's nuts (IMHO).
ATBCharles Kincaid
March 27, 2015 at 10:40 am
Charles Kincaid (3/27/2015)
...If I have aliased a column I can't order by the alias name. That's nuts (IMHO).
Agreed.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 27, 2015 at 3:30 pm
PHYData DBA (1/26/2015)
mike.gallamore (1/26/2015)
Or applications that sit on top of someone else's database: say health care systems, CRM etc. Often it becomes the job of the front end guys to quickly re-wire their application when the users want a new version of the main system and the schema changes all over the place. At my work my team are both the db and the webservice guys. Another team handles UI (and we support a few different products that hit our database). Becomes a bit of a pain negotiating between teams when you want to refactor things but better than becoming a javascript monkey 😉Anything is better than becoming a javascript monkey.
Being banished to an undocumented and uncharted area of abstracted machine code that executes in a 4 bit DLL running between the 5th and 6th levels of Hell. Forced to write code that works the same for every user on every device connected everywhere for anytime and all time.
Dante did not a clue that there would be such a Hell so extremely cruel....
yeah... I don't like javascript.
If you think JavaScript is painful you've clearly never attempted to write anything serious C++ or in VB or in JAVA.
Yes it would be nice to be able to use Haskell all the time, or ML, or Prolog, or Hope+ or even at a push F# but only the last of those is yet on offer from Microsoft (although they are working on the first) and people are not taking it up much because it's too much unlike the badly designed languages (like C++) that they've been used to.
And the term "javascript monkey" is the sort of insult that most people would prefer not to see in these forums.
Tom
March 27, 2015 at 3:37 pm
PHYData DBA (1/26/2015)
Another reason why SELECT * from <any object> should never be used in application execution code.
Nonsense. The only reason you ever need to ban SELECT * from application code is that you ban SELECT from application code in the first place. Don't let the app do anything at all but call stored procedures. That's the only way to keep deployment simple.
Tom
March 27, 2015 at 4:19 pm
I'm a bit surprised that the editorial seems to suggest that separating database and app releases is something new, because in fact it's something that has been around and recognised as good practice since at least 2000, probably since earlier. It's one of the reasons that I advocate building a system in which no access is granted to principals under which applications can run other than the ability to call stored procedures (the procedures can provide their own permssions to the underlying data, the app needs only permissions to call the procs designated for it).
I had trouble getting this concept over to some developers some time ago, but I'd already had trouble persuading them that the applications should all use principals other than SA and before that persuading them that SA shouldn't have a blank password (other developers were instantly in favour of proper security and strong modularity when the idea was suggested, as were all the sysadmins). But once it got going it made life so much less stressful that no-one complained. It made it possible to support customers around the world on different sets of apps and different versions of the apps they shared all using the same schemata (and of course with some different data sets as well as some common data) while adding both new capabilities which all customers could choose and special features for individual customers. So I know that this approach works pretty effectively. I was also very favorably impressed by how rapidly and effectively a new team team could take over from me and my team when we decided to move all R&D and development from London to Beirut, which suggests to me that it makes for a system which is easy to understand.
Tom
March 27, 2015 at 4:31 pm
Eric M Russell (3/27/2015)
Steve Jones - SSC Editor (3/27/2015)
Eric JOATMON (3/27/2015)
Steve,You mention "extra columns in a result set" as being a downside to deploying database changes prior to application code changes. That would only occur with a "select *" type of query, which is poor coding practice. As long as all columns are named in a query, there will be no impact from additional columns that are not referenced.
Eric
Inserts as well. I can have issues if I haven't specified defaults.
Are you talking about this kind of shite?
insert into MyTablevalues ( 1, 9, 9 );
select 1, 9, 9;[/code]
yes
March 27, 2015 at 4:32 pm
TomThomson (3/27/2015)
I'm a bit surprised that the editorial seems to suggest that separating database and app releases is something new, because in fact it's something that has been around and recognised as good practice since at least 2000, probably since earlier....
Not suggesting it's new, just bringing light to a practice. Lots of people do this already. Many more do not.
March 27, 2015 at 8:26 pm
TomThomson (3/27/2015)
...
If you think JavaScript is painful you've clearly never attempted to write anything serious C++ or in VB or in JAVA.
The JavaScript ghat I've seen uses type VAR for everything. :w00t: PHP is just about as bad. I think that the concept is that types are "mutable". The compiler, or run time, changes the type on the fly to suite itself. Kind of the same thing in SQLite.
I know that VB gets a lot of bashing. I'm OK with that. Yet we did a lot of "serious" code in VB .Net. That includes Lambda expressions. We switched to C# not because it was better but because it was more currently supported and popular.
If you are going to bash something make sure that you have a valid reason. You want real hell? Go back to VB 4. That was hell.
ATBCharles Kincaid
March 29, 2015 at 8:39 am
TomThomson (3/27/2015)
...If you think JavaScript is painful you've clearly never attempted to write anything serious C++ or in VB or in JAVA...And the term "javascript monkey" is the sort of insult that most people would prefer not to see in these forums.
I have developed some serious code in C++, albeit not in the last 10 years, and in my opinion it was at that time a far better language than JavaScript is but unfortunately too few people bothered to understand it.
Spot on about monkey comments unless it is self-deprecating humour.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 30, 2015 at 8:48 am
TomThomson (3/27/2015)
And the term "javascript monkey" is the sort of insult that most people would prefer not to see in these forums.
Spot on, Tom. Thanks for that.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply