|
Just discovered this in 2016 (CTP3):
DROP IF EXISTS – new thing in SQL Server 2016 | SQL Database Engine Blog
DROP TABLE IF EXISTS SomeSchema.SomeTable;
is so much nicer than:
IF OBJECT_ID('SomeSchema.SomeTable', 'U') IS NOT NULL
DROP TABLE SomeSchema.SomeTable;
Not world changing but nice.
Whenever you find yourself on the side of the majority, it is time to pause and reflect. - Mark Twain
|
|
|
|
|
|
I rather suspect that most RDBMS systems have had it (or something very similar) for years, but an improvement's an improvement all the same and I remain in a good mood with Microsoft until something comes along to spoil that (give it 5 minutes!)
Whenever you find yourself on the side of the majority, it is time to pause and reflect. - Mark Twain
|
|
|
|
|
MySQL has it since 1998 (way before PostgreSQL), but still it is good to see that Microsoft moves... SQL is an important (and good) software, especially since the free version for Linux...
(By the way, me think the reason of moving is that now there is a serious competition on the free market)
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge". Stephen Hawking, 1942- 2018
|
|
|
|
|
And in the doc you can read... Quote: Conditionally drops the table only if it already exists.
... this to prevent from deleting a table which does not exists 
It does not solve my Problem, but it answers my question
modified 19-Jan-21 21:04pm.
|
|
|
|
|
No. It is to preventing error if the table does not exists... 
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge". Stephen Hawking, 1942- 2018
|
|
|
|
|
I know, I know 
It does not solve my Problem, but it answers my question
modified 19-Jan-21 21:04pm.
|
|
|
|
|
|
|
I use this all the time with our deployment scripts.
However, you have to be careful if your scripts have the remote chance of being executed in a non 2016+ db server. For us, this is an issue with some of our client dbs. For most shops this should not be an issue, I would think.
|
|
|
|
|
You are lucky if pre 2016 is not a problem for you... We still must support 2008 (and 6 months before 2005 too)...
I can count on one hand the customers upgraded to 2014 - and they all have academic price...
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge". Stephen Hawking, 1942- 2018
|
|
|
|
|
I mention in my post that we have certain clients that are pre-2016. So, yes we too must support dbs that have not been upgraded.
|
|
|
|
|
I'm still working to Microsoft SQL Server 2012 (SP1) since that's what my web hosting service provides ... hence my development Express edition is the same version.
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
 Which is finally the most useful thing for all that stuff
It does not solve my Problem, but it answers my question
modified 19-Jan-21 21:04pm.
|
|
|
|
|
so when do we get an ELSE clause on that?
Message Signature
(Click to edit ->)
|
|
|
|
|
I second this, to let us make it finally clear:
DROP TABLE IF EXISTS SomeSchema.SomeTable ELSE DO NOT DROP TABLE SomeSchema.SomeTable 
It does not solve my Problem, but it answers my question
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Nah, you're missing the point:
DROP TABLE IF EXISTS SomeSchema.SomeTable
ELSE CREATE TABLE SomeSchema.SomeTable(...)
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I don't recall ever needing something like that. The more usual requirement would be to drop the table if it exists, and then always create it.
Which is basically a sticking-plaster for the fact that CREATE OR ALTER TABLE doesn't exist.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm thinking of recommending it as a global boolean in QA to see how many times it comes up in code samples over the next few years... 
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
DROP TABLE IF EXISTS SomeSchema.SomeTable
ELSE DROP SCHEMA SomeSchema; # clearly schema in worse shape then we were told, start over time
...
# or maybe they chose the wrong database??? ooooops
SELECT "Time to run away";
Message Signature
(Click to edit ->)
|
|
|
|
|
Like all the new features, it's nice when you can dictate the db server version. Don't forget about the compatibility level setting of the database as I found out last month! 
Now, if they could just get the query designer to remember the layout (x,y) of objects when I'm working on a query....every time, scroll down, drag tables/views up where I can see them all, and reposition so it makes sense...run query, if anything is wrong, repeat. Also, if it could remember it's last window state/position/screen that would convince me to start using newer versions of SSMS.
For me, SSMS 2016 fails with some MEF error every other time I need to use it, so I still do most work with 2014. I have 2017 which is OK but still slower to start than 2014. I really only use it to import Azure dbs.
"Go forth into the source" - Neal Morse
|
|
|
|
|
My only question is, why are you using a CTP of SQL 2016? It's already got 2 service packs, and the latest one's already at its fifth cumulative update.
|
|
|
|
|
I'm actually using CU3, it was CTP3 that introduced this feature.
Whenever you find yourself on the side of the majority, it is time to pause and reflect. - Mark Twain
|
|
|
|
|
Take a stroll for each morning letter you delay! (11)
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
