Introducing The Poorly Designed Management Studio

Since Microsoft has remove the ability for developers to design in
Access through ADPs in SQLS2005, I am going to begin my own discussion
on why the Management Studio is poorly designed for developers. If I
am in error on any of these points, please let me know because it could
greatly improve my shop's productivity.
#1. Let's look a the screen when creating a new table. Now I know
very few screens that are longer than they are wide. As a matter of
fact, I believe the industry is trending toward wide screens. When you
create a new table in Management Studio there are three columns,
"Column Name", "Data Type", and "Allow Nulls." Hmm, OK, three columns.
Let's look at all the wasted space on the screen to the right of the
three columns. Unless you are creating very very very very long column
names, this would be an ideal place to the "Column Properties." But
instead, it's stuck at the bottom, in a very small vertical scroll box
with huge column widths. Nice.
Many more to come...> #1. Let's look a the screen when creating a new table. Now I know
> very few screens that are longer than they are wide. As a matter of
> fact, I believe the industry is trending toward wide screens. When
> you
Real developers know the create table syntax and don't need A GUI. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Just reset your video display to 640x480 and you will see that the dialog
fills the screen quite nicely. :-P
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1142956268.004514.272830@.i39g2000cwa.googlegroups.com...
> Since Microsoft has remove the ability for developers to design in
> Access through ADPs in SQLS2005, I am going to begin my own discussion
> on why the Management Studio is poorly designed for developers. If I
> am in error on any of these points, please let me know because it could
> greatly improve my shop's productivity.
> #1. Let's look a the screen when creating a new table. Now I know
> very few screens that are longer than they are wide. As a matter of
> fact, I believe the industry is trending toward wide screens. When you
> create a new table in Management Studio there are three columns,
> "Column Name", "Data Type", and "Allow Nulls." Hmm, OK, three columns.
> Let's look at all the wasted space on the screen to the right of the
> three columns. Unless you are creating very very very very long column
> names, this would be an ideal place to the "Column Properties." But
> instead, it's stuck at the bottom, in a very small vertical scroll box
> with huge column widths. Nice.
> Many more to come...
>|||#2. Why do all 4 panes default to visible when I create a new view?
Why when I hide the results pane (because I don't need it until I
execute the view) and when I hide the SQL pane (because I'm not a "real
developer") do they re-appear the next time I create a new view? Is
there a setting I'm missing?|||#3. When designing a query and I type in "CASE WHEN fund = '0001' THEN
revenue_jan ELSE 0 END" as a column name I cannot select "Sum" from the
"Group By" drop-down list? I know it works because when I type it in
the SQL pane (like a "real developer") it works and shows up correctly
after I save the view.|||#4. Why can't I export a view or table to Excel?|||Yep, the query window is wide enough. :)
And clicking up a disaster is pretty much out of the question.
ML
http://milambda.blogspot.com/|||Real developers should be submitting schema change requests to the real
DBA(s) -- who also don't need a GUI. :-)

> Real developers know the create table syntax and don't need A GUI. :)|||If you are simply looking to grab the contents of a particular table:
select * from sometable
in the results pane, click the upper left shaded block (this will highlight
the entire result. ctrl+c
open a new xls and select a single cell on the sheet.
ctrl+v
even brings over the column headers (which I don't remember if it did in the
old QA or not).
HTH
--Tony
"The Cornjerker" wrote:

> #4. Why can't I export a view or table to Excel?
>|||I don't think that there is a persisted preference for these. I don't reall
y
use this feature, but after poking around with it a bit, having all the pane
s
there seemed kind of useful given the task at hand. I could also see how
being able to switch this to a tabbed configuration like you can with the
standard query windows might be preferrable.
--Tony
"The Cornjerker" wrote:

> #2. Why do all 4 panes default to visible when I create a new view?
> Why when I hide the results pane (because I don't need it until I
> execute the view) and when I hide the SQL pane (because I'm not a "real
> developer") do they re-appear the next time I create a new view? Is
> there a setting I'm missing?
>

Introducing FOREIGN KEY constraint

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?Originally posted by baburajv
"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?

hi,
this link below explains it all:
http://lists.evolt.org/archive/Week-of-Mon-20030421/139403.html
harshal.|||Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info|||Originally posted by baburajv
Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info
when do u get this message while delete or while update??
while updation there is a cascade from applicant master to applicant to applicant sex.|||I GET THIS MESSAGE WHILE RUNNING MY SCRIPT FILE (.SQL FILE) FROM THE QUERY ANALYZER.

ONE MORE THING,

"while updation there is a cascade from applicant master to applicant to applicant sex."

i need to know one thing, the "applicantmaster" referes to sexcode in "applicantsex" and

" ON UPDATE CASCADE" means any updation in applicant sex must be cascaded
and not the reverse..(am i correct?)

i hope i made my point clear

thanks for the advice

Introducing (NOLOCK) into production code for Selects

I have a large application that is multi-user and quite transactional,
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.
We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).
I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:
SELECT <field list> FROM <table> (NOLOCK) WHERE .......
This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.
My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.
Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)
If @.Account_Type = 'P'
Select <some fields> from <view>
Else
Select <other fields> from <view>
Which begs another question, can NOLOCK be used when selecting from a view?
As in:
If @.Account_Type = 'P'
Select <some fields> from <view> (NOLOCK)
Else
Select <other fields> from <view> (NOLOCK)
Thanks for your input.On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkotuby@.snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?
Yes.
What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!
I've seen tons of production code done the way you want.
Not sure I approve of it, but it does what it does.
J.|||As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>|||Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:
SELECT * FROM Table WITH (NOLOCK)
Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.
Andrew J. Kelly SQL MVP
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>