SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Model DB


Model DB

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68719 Visits: 18570
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 pointsw00t.....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

Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2249 Visits: 1817
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
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4144 Visits: 2204
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 pointsw00t.....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! :-D
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68719 Visits: 18570
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.w00tw00t



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68719 Visits: 18570
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 pointsw00t.....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! :-D


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

malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 1189
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)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68719 Visits: 18570
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

ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 377
this question should have been worth 4 points :-)... so that atleast a person can get something for his correct choice... no rounding off required... :-D

CirquedeSQLeil (6/10/2010)
johnf_amic (6/10/2010)
I got 3 out of 4 right. Don't I get partial credit? :-D


You did. You got .75 pts and then it is rounded down for your final score. ;-)

SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5797 Visits: 1619
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.
COOL_ICE
COOL_ICE
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 431
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search