Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Model DB Expand / Collapse
Author
Message
Posted Thursday, June 10, 2010 2:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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.....just for finding the error in BOL!!!

....my 2 cents worth......



That would be a good idea.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935729
Posted Thursday, June 10, 2010 3:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:12 AM
Points: 1,676, Visits: 1,755
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 . 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
Post #935750
Posted Thursday, June 10, 2010 3:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.....just for finding the error in BOL!!!

....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!
Post #935751
Posted Thursday, June 10, 2010 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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 . 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.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935755
Posted Thursday, June 10, 2010 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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.....just for finding the error in BOL!!!

....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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935756
Posted Thursday, June 10, 2010 10:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #935825
Posted Thursday, June 10, 2010 11:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935835
Posted Friday, June 11, 2010 2:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,008, Visits: 369
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.
Post #935874
Posted Friday, June 11, 2010 8:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #936043
Posted Friday, June 18, 2010 4:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #939481
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse