June 10, 2010 at 2:48 pm
nelsonj-902869 (6/10/2010)
michael.kaufmann (6/10/2010)
Just verified the finding in SQL Server 2005:Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
I found the same statement in BOL....and missed my point as well. I think SQLCentral should give those of us who answered script one as producing an error should get 15 extra points:w00t:.....just for finding the error in BOL!!!:-D
....my 2 cents worth......
That would be a good idea.
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
June 10, 2010 at 3:23 pm
Very good question, thank you Jason! I answered it correctly because I remembered there was a discussion some time in the past on this site about the read_only nuances of the model database. When the high quality question like this is also combined with Hugo's comments then it is truly enjoyable.
Until today I was not sure about how them 0.75 points get converted to 0 :hehe:. I thought before that it was because cast(0.75 as int) * @qod_points = 0, but today someone suggested that it is because of floor(0.75) * @qod_points = 0. I tested both in 10 mln iteration loop and both take about same time to execute. Then it hit me that none of the above is used because simple (3/4) * @qod_points just does it because of the implicit conversion to int takes care of it.
Oleg
June 10, 2010 at 3:25 pm
CirquedeSQLeil (6/10/2010)
nelsonj-902869 (6/10/2010)
michael.kaufmann (6/10/2010)
Just verified the finding in SQL Server 2005:Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
I found the same statement in BOL....and missed my point as well. I think SQLCentral should give those of us who answered script one as producing an error should get 15 extra points:w00t:.....just for finding the error in BOL!!!:-D
....my 2 cents worth......
That would be a good idea.
Then I think those of use that new that BOL was wrong, and still answered correctly should get an extra 30 points! 😀
June 10, 2010 at 3:30 pm
Oleg Netchaev (6/10/2010)
Very good question, thank you Jason! I answered it correctly because I remembered there was a discussion some time in the past on this site about the read_only nuances of the model database. When the high quality question like this is also combined with Hugo's comments then it is truly enjoyable.Until today I was not sure about how them 0.75 points get converted to 0 :hehe:. I thought before that it was because cast(0.75 as int) * @qod_points = 0, but today someone suggested that it is because of floor(0.75) * @qod_points = 0. I tested both in 10 mln iteration loop and both take about same time to execute. Then it hit me that none of the above is used because simple (3/4) * @qod_points just does it because of the implicit conversion to int takes care of it.
Oleg
Thanks. It is due to that prior question that I thought of this question. I thought it would be a good idea to run through the entire scenario, step by step as displayed.
Implicit convert to int must be the case. Much more efficient. Thanks for pointing that out.:w00t::w00t:
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
June 10, 2010 at 3:30 pm
UMG Developer (6/10/2010)
CirquedeSQLeil (6/10/2010)
nelsonj-902869 (6/10/2010)
michael.kaufmann (6/10/2010)
Just verified the finding in SQL Server 2005:Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
I found the same statement in BOL....and missed my point as well. I think SQLCentral should give those of us who answered script one as producing an error should get 15 extra points:w00t:.....just for finding the error in BOL!!!:-D
....my 2 cents worth......
That would be a good idea.
Then I think those of use that new that BOL was wrong, and still answered correctly should get an extra 30 points! 😀
I could hang with that too 😉
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
June 10, 2010 at 10:29 pm
CirquedeSQLeil (6/9/2010)
malleswarareddy_m (6/9/2010)
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.
Interesting.
If I understand correctly, you:
1) Set the Model DB to readonly
2) Use the wizard to create a new database
3) Switch context to the new database
4) execute a create table statement - which fails
What version and SP of SQL server? When I do those steps I see the same results as the script (sql 2005 and 2008).
I used SQL server developer edition 2008
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 10, 2010 at 11:11 pm
malleswarareddy_m (6/10/2010)
CirquedeSQLeil (6/9/2010)
malleswarareddy_m (6/9/2010)
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.
Interesting.
If I understand correctly, you:
1) Set the Model DB to readonly
2) Use the wizard to create a new database
3) Switch context to the new database
4) execute a create table statement - which fails
What version and SP of SQL server? When I do those steps I see the same results as the script (sql 2005 and 2008).
I used SQL server developer edition 2008
Cool. I think Hugo explained the reason this is happening quite nicely.
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
June 11, 2010 at 2:21 am
this question should have been worth 4 points :-)... so that atleast a person can get something for his correct choice... no rounding off required... 😀
CirquedeSQLeil (6/10/2010)
johnf_amic (6/10/2010)
I got 3 out of 4 right. Don't I get partial credit? 😀You did. You got .75 pts and then it is rounded down for your final score. 😉
June 11, 2010 at 8:04 am
michael.kaufmann (6/10/2010)
Just verified the finding in SQL Server 2005:- With MODEL being in READ_ONLY mode, when creating a database using the wizard, it is created read_only. No more changes are possible.
- With MODEL still in READ_ONLY, when creating a database via a SQL script, it is created in read/write mode and tables etc. can be added.
Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
Agreed Mike. Never knew this. Just checked by creating DB with wizard and also script.
But still, Model is an empty shell and all new databases created should copy exactly the properties Model DB had if created by default script - Create database Testmenow ?
SQL DBA.
June 18, 2010 at 4:46 am
Very interesting question… It initiates lot of investigations 🙂
-Samji
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
January 18, 2012 at 7:49 am
This was an excellent question. Very odd behavior when using the wizard. These things are always good to know.
---------------
Mel. 😎
January 18, 2012 at 10:21 am
SqlMel (1/18/2012)
This was an excellent question. Very odd behavior when using the wizard. These things are always good to know.
Glad you liked it.
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
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply