Does SQL uses index in the following select statement

I have the following table structure:
PK_Column1
PK_Column2
IndexedColumn
Column_ABC
Column_XYZ
Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
values in the following select statement:
SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
PK_Column1=@.MyParam
When I run this statement it works too slow and I see alot of reads in SQL
Server Profiler.
Is there any way to improve the performance in this case?
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
If Indexed column is clustered, it probably would as it can do a clustered
index range scan.
Else, it may not. It all depends on how up to date the statistics are, the
data types of the columns, how selective the indexes are and the number of
rows.
Show the query plan and we can tell.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via webservertalk.com" <no@.spam.pls> wrote in message
news:58e799d08a0ce@.uwe...
>I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||Thank you for your answer.
Here are more details:
PK_Column1 smallint
PK_Column2 int
IndexedColumn DateTime (NON-CLUSTERED and not unique)
Column_ABC varchar
Column_XYZ varbinary(BLOB)
The table has about 4M rows.

>Show the query plan and we can tell.
How can I get it? I am using the Standard edition of SQL Server 2005.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||If there is a clustered index on PK_Column1, then it might do an index
scan on the index of IndexedColumn. However, the query would benefit
more from an index on (PK_Column1, IndexedColumn).
I am not running SQL2K5, but I guess that SET SHOWPLAN_TEXT ON will
probably still work...
HTH,
Gert-Jaqn
"Alex via webservertalk.com" wrote:
> I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1

Does SQL Server support XML Functions

Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
sNot sure about Oracle counterparts, but have you checked out the FOR XML
clause in SQL Server 2000 Books Online?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s|||See BOL for OPENXML and FOR XML
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s|||In addition, SQL 2005 will make XML a first-class citizen in the server, XML
data types, XQUERY, the full ride!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s|||Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>|||FOR XML is SQL Server's T-SQL specific.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408022328.464b6cae@.posting.google.com...
Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>|||These functions are part of the ANSI/ISO SQL-2003 standard and are not
supported in SQLServer 2005 for a variety of reasons (ISO spec was released
too late for us to add them, they are less performing and more verbose than
a rowset mapping approach such as FOR XML).
We may consider them in the next release, if enough users request them. So
please let me know if I should count you :-).
Best regards
Michael
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s

Does SQL Server support XML Functions

Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s
Not sure about Oracle counterparts, but have you checked out the FOR XML
clause in SQL Server 2000 Books Online?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s
|||See BOL for OPENXML and FOR XML
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s
|||In addition, SQL 2005 will make XML a first-class citizen in the server, XML
data types, XQUERY, the full ride!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s
|||Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
|||FOR XML is SQL Server's T-SQL specific.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408022328.464b6cae@.posting.google.c om...
Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
|||These functions are part of the ANSI/ISO SQL-2003 standard and are not
supported in SQLServer 2005 for a variety of reasons (ISO spec was released
too late for us to add them, they are less performing and more verbose than
a rowset mapping approach such as FOR XML).
We may consider them in the next release, if enough users request them. So
please let me know if I should count you :-).
Best regards
Michael
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s

Does SQL Server support XML Functions

Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s
Not sure about Oracle counterparts, but have you checked out the FOR XML
clause in SQL Server 2000 Books Online?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s
|||See BOL for OPENXML and FOR XML
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s
|||In addition, SQL 2005 will make XML a first-class citizen in the server, XML
data types, XQUERY, the full ride!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s
|||Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
|||FOR XML is SQL Server's T-SQL specific.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408022328.464b6cae@.posting.google.c om...
Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
|||These functions are part of the ANSI/ISO SQL-2003 standard and are not
supported in SQLServer 2005 for a variety of reasons (ISO spec was released
too late for us to add them, they are less performing and more verbose than
a rowset mapping approach such as FOR XML).
We may consider them in the next release, if enough users request them. So
please let me know if I should count you :-).
Best regards
Michael
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.c om...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s

Does SQL Server support XML Functions

Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
sNot sure about Oracle counterparts, but have you checked out the FOR XML
clause in SQL Server 2000 Books Online?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-
1) XMLElement
2) XMLAttributes
3) XMLForest
Thanks in Advance for your reply
s|||See BOL for OPENXML and FOR XML
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s|||In addition, SQL 2005 will make XML a first-class citizen in the server, XML
data types, XQUERY, the full ride!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> s|||Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<uldgTsGeEHA.1764@.TK
2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>|||FOR XML is SQL Server's T-SQL specific.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408022328.464b6cae@.posting.google.com...
Hi,
Yep the SQL Server supports the FOR XML clause which returns the
Records/Rows as XML. However Oracle does not support FOR XML clause.
(oracle 9i). To get the output in XML format the functions that i have
mentioned have to be used.
Now before I end up using the above mentioned functions, i wanted
to know whether these functions are supported by other Databases also
or not.
Bye
Amardeep Verma
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:<uldgTsGeEHA.1764@.TK2MSFTNGP10.phx.gbl>...
> Not sure about Oracle counterparts, but have you checked out the FOR XML
> clause in SQL Server 2000 Books Online?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>|||These functions are part of the ANSI/ISO SQL-2003 standard and are not
supported in SQLServer 2005 for a variety of reasons (ISO spec was released
too late for us to add them, they are less performing and more verbose than
a rowset mapping approach such as FOR XML).
We may consider them in the next release, if enough users request them. So
please let me know if I should count you :-).
Best regards
Michael
"Amardeep Verma" <addverma@.netscape.net> wrote in message
news:45d3f402.0408012353.49b1ab71@.posting.google.com...
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply
> ssql

Does SQL Server support XML Functions

Hi,
Do any versions of SQL Server support the following functions, as
they appear in the Oracle Database:-

1) XMLElement
2) XMLAttributes
3) XMLForest

Thanks in Advance for your reply
Bye
Amardeep VermaAmardeep Verma (addverma@.netscape.net) writes:
> Do any versions of SQL Server support the following functions, as
> they appear in the Oracle Database:-
> 1) XMLElement
> 2) XMLAttributes
> 3) XMLForest
> Thanks in Advance for your reply

Since I don't know Oracle, I don't know what these functions do. It is
not likely that SQL Server supports these functions directly, but it
may support the same functionality. You would need to explain what these
functions do to get answer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Thanks Erland for your Response. These Functions take simple
SELECT statement and return the result in a XML format. With each
Column name as a tag and the value in that column as the data.
For Example the Employee Table of NorthWind Database has 3
columns:-
1) EmpNo
2) EmpLastName
3) EmpFirstName
Then using the below mentioned functions we can get the output
<EMP>
<EMPNo>1</EMPNo>
<EMPFirstName>ABC</EMPFirstName>
<EMPLastName>XYZ</EMPLastName>
</EMP
Hope this helps
Amardeep Verma

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9536EC50E4C4FYazorman@.127.0.0.1>...
> Since I don't know Oracle, I don't know what these functions do. It is
> not likely that SQL Server supports these functions directly, but it
> may support the same functionality. You would need to explain what these
> functions do to get answer.|||Amardeep Verma (addverma@.netscape.net) writes:
> Thanks Erland for your Response. These Functions take simple
> SELECT statement and return the result in a XML format. With each
> Column name as a tag and the value in that column as the data.
> For Example the Employee Table of NorthWind Database has 3
> columns:-
> 1) EmpNo
> 2) EmpLastName
> 3) EmpFirstName
> Then using the below mentioned functions we can get the output
><EMP>
> <EMPNo>1</EMPNo>
> <EMPFirstName>ABC</EMPFirstName>
> <EMPLastName>XYZ</EMPLastName>
></EMP
It looks like you should study the FOR XML clause in the SELECT statement.
It gives you several possibilities. Rather than typing examples here,
I refer you to Books Online myself, since I am no XML wizard myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL sever has a few ways to generate xml. An example below that
conforms to your spec(ie columns as elements) can be viewed in QA

select * from employees
where employeeid=1 for xml auto, elements

NOTE: it is best run when the query is set to 'Results in text' and
also
'DBCC TRACEON(257)' has been run first

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hans
No, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans
|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
>
>

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hansNo, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> > Hello,
> >
> > I'm trying to do this:
> > alter table maquinas add marca varchar(50) after modelo
> >
> > and I get the following error:
> > Incorrect syntax near 'after'.
> >
> > What is going on?
> >
> > hans
>
>

Does SQL SERVER MANAGEMENT come with Visual Studio Prof 2005?

I'm following a video tutorial about SQL wherein I'm told to download SQL SERVER MANAGEMENT STUDIO EXPRESS. However I have just bought and installed the whole MS visual studio pro 2005 along with the SQL that comes with it.

I'm afraid to download and install the SQL SERVER MANAGEMENT STUDIO in case it botches up my prg already installed. Can anyone tell me if this management tool is already installed along with the package I have (if so, where is it - not listed in programs)

Thank you for your time.

SQL Server 2005 Developer is included with the Pro VS, this includes the full version of the SQL Server Management Tools. In regards to the tutorial you are using the functions are exactly the same... you actually have more functions in the Developer Version.

|||

Thanks Glenn

Sorry to be so thick but can you tell me how to open the management console? (I'm totally new to VS and SQL and it's not obviously listed on the programs start menu) All there is under SQL Server on the start menu is: SQL Server configuration tools (and none of these mention a server management area).

here is the list of available options under that menu:
'Notifications Services command prompt'
''SQL Server configuration manager' and
''SQL Server error usage and report' and
'SQL Server surface area configuration'

Thanks again for your help

|||

You might have to use the install disks re run the setup amd make sure that the client tools are installed.

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanksHi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:
> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanks
Hi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:

> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanksHi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:

> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>sql

Does CLNG work in Sql Compact?

Hi,

When I execute following query against to SqlCE 3.0.53, I am getting an error, could someone guide where it is wrong.

SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

Error:

Major Error 0x80040E14, Minor Error 25921

> SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

The function is not recognized by SQL Server Compact Edition. [ Name of function = CLNG,Data type (if known) = ]

CLNG is not availabel in SQL CE. For documention on the SQL CE SQL syntax, see http://msdn2.microsoft.com/en-us/library/ms173372.aspx. You could use CONVERT(int, SLCTD_MENUENTRYID) instead.

|||I feel the above CONVERT function always returns non fractional value.

For ex:
select CONVERT(int, 4.7)= 4
select CONVERT(int, 4.4)=4
select CONVERT(int, -4.7) =-4
select CONVERT(int, -4.4) =-4

But where as CLNG function does the following:
select CLNG(4.7)=5
select CLNG(4.4)=4
select CLNG(-4.7)=-5
select CLNG(-4.4)=-4

I feel the following line will work for us:

select CONVERT(int, round(4.7,0)) =5
select CONVERT(int, round(4.4,0)) =4
select CONVERT(int, round(-4.7,0)) =-5
select CONVERT(int, round(-4.4,0)) =-4

Please let me know if I am wrong.

Thanks|||If that works for you, you are of course right. CLNG is not available in SQL CE, but other conversion functions are.

Does CLNG work in Sql Compact?

Hi,

When I execute following query against to SqlCE 3.0.53, I am getting an error, could someone guide where it is wrong.

SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

Error:

Major Error 0x80040E14, Minor Error 25921

> SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

The function is not recognized by SQL Server Compact Edition. [ Name of function = CLNG,Data type (if known) = ]

CLNG is not availabel in SQL CE. For documention on the SQL CE SQL syntax, see http://msdn2.microsoft.com/en-us/library/ms173372.aspx. You could use CONVERT(int, SLCTD_MENUENTRYID) instead.

|||I feel the above CONVERT function always returns non fractional value.

For ex:
select CONVERT(int, 4.7)= 4
select CONVERT(int, 4.4)=4
select CONVERT(int, -4.7) =-4
select CONVERT(int, -4.4) =-4

But where as CLNG function does the following:
select CLNG(4.7)=5
select CLNG(4.4)=4
select CLNG(-4.7)=-5
select CLNG(-4.4)=-4

I feel the following line will work for us:

select CONVERT(int, round(4.7,0)) =5
select CONVERT(int, round(4.4,0)) =4
select CONVERT(int, round(-4.7,0)) =-5
select CONVERT(int, round(-4.4,0)) =-4

Please let me know if I am wrong.

Thanks|||If that works for you, you are of course right. CLNG is not available in SQL CE, but other conversion functions are.

Does any one have similar problem with nvarchar column storing chinese characters?

I am running into some wired problem with nvarchar(4000). When I insert the
chinese characters, the text got cut off. The following two sections shows
what happen, the first chinese section is what is ended up in the database,
the second section (separate by "--") show the complete
parameter value before IDbCommand.ExecuateQuery() is called. It is less
than 4000 bytes. It seems a chunk of text got loss somewhere.
No sure what happen here. But it seems to be a serious problem. My full
text search certainlly wouldn't work if I can't get the data into the
column. Is this a bug in 2005 CTP or something I did wrong ? Thanks
--Xin Chen
?...?? - ? - ? - ?... ? - ?Q - ?... ?
- ? - ?... ? - ? ? > ? > ? ??
?:2005?03?23?03:56?:????
?
????
???
??1937?12?13?27???,??
??,?,??
"? ?...?? - ? - ? - ?... ? - ?Q - ?... ?
? - ? - ?... ? - ? ? > ? > ? ??\n
?:2005?03?23?03:56?:????
?\n???? \n \n??
? \n ??1937?12?13?27???,?
???,?,???,??
????,??30??
?,???,??,??,??,?
???1938?3?24?,????,?
??2?31? ??,?27?,?
??,?31??,??28?,?
?31?,??29?,?2??,?
??,?2???31?,???
?4?3?,????,?
???,??,???
6?,???7?,???
?,???????
?,???,?4.6?,?7500?
??,??1????
??,??? ?\n ??
??\n ?? ????
???\n??? ??
\n?? ??\n??3-26
? ??\n?? ??\n?
?? ??\n?:? ?<?
?> \n ? ??"?"??:? ?
? ? \n -- ?? ChinaRen - ? - ? - ?
? - ? - ? - ? - ? - ?? - About SOHU -
?\nCopyright 2005 Sohu.com Inc.All rights reserved."
how are you inserting these characters? OpenRowset?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:ulgv5WANFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I am running into some wired problem with nvarchar(4000). When I insert
the
> chinese characters, the text got cut off. The following two sections
shows
> what happen, the first chinese section is what is ended up in the
database,
> the second section (separate by "--") show the complete
> parameter value before IDbCommand.ExecuateQuery() is called. It is less
> than 4000 bytes. It seems a chunk of text got loss somewhere.
> No sure what happen here. But it seems to be a serious problem. My full
> text search certainlly wouldn't work if I can't get the data into the
> column. Is this a bug in 2005 CTP or something I did wrong ? Thanks
> --Xin Chen
> ?...?? - ? - ? - ?... ? - ?Q - ?... ?
> - ? - ?... ? - ? ? > ? > ? ??
> ?:2005?03?23?03:56?:????
> ?
> ????
> ???
> ??1937?12?13?27???,??
> ??,?,??
> ----
--
> --
> "? ?...?? - ? - ? - ?... ? - ?Q - ?... ?
> ? - ? - ?... ? - ? ? > ? > ? ??\n
> ?:2005?03?23?03:56?:????
> ?\n???? \n \n??
> ? \n ??1937?12?13?27???,?
> ???,?,???,??
> ????,??30??
> ?,???,??,??,??,?
> ???1938?3?24?,????,?
> ??2?31? ??,?27?,?
> ??,?31??,??28?,?
> ?31?,??29?,?2??,?
> ??,?2???31?,???
> ?4?3?,????,?
> ???,??,???
> 6?,???7?,???
> ?,???????
> ?,???,?4.6?,?7500?
> ??,??1????
> ??,??? ?\n ??
> ??\n ?? ????
> ???\n??? ??
> \n?? ??\n??3-26
> ? ??\n?? ??\n?
> ?? ??\n?:? ?<?
> ?> \n ? ??"?"??:? ?
> ? ? \n -- ?? ChinaRen - ? - ? - ?
> ? - ? - ? - ? - ? - ?? - About SOHU -
> ?\nCopyright 2005 Sohu.com Inc.All rights reserved."
>
|||no, I am using a store proc and call it via ADO.NET in my app
I store my text in the nvarchar(4000) as string, I am not using openrowset.
the stored proc use regular insert into (...) values (...)
--Xin Chen
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:exEotkANFHA.2468@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> how are you inserting these characters? OpenRowset?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Xin Chen" <xchen@.xtremework.com> wrote in message
> news:ulgv5WANFHA.1176@.TK2MSFTNGP15.phx.gbl...
> the
> shows
> database,
complete
> ----
> --
>

Does a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.

Documents Database

Hello,

I need to create a database to hold documents information.

1. Basically, I need the following information for each document:

Title, Description, LastUpdated, Category, Type, Url

Should I create tables for Category and Type?
And link them to my documents table?
What type of relationship should I use?

2. I also need to know how many downloads each document had

Should I add a column in my documents table?
Then I would increase it one by one.
Or should I create a new table which would register each download.

3. I need to let users to rate each document from 1 to 5.
How should I implement this?

Thank You Very Much,
Miguel

Hey,

1. You could; some like to use unnormal form to do it, whereas some would break it out into a one-to-many relationship. The choice is up to you.
2. You could store it in the same table, and create a stored procedure that just handled the increment. You could even index that field, which may or may not make it more efficient.
3. I've seen it done where in that table, you store the total number of times something was rated, and the total rating given, then perform the math to get the value. You can store that in the same table, and give it its own update stored procedure to perform that.

|||

1. Yes you should make seperate tables for them and store a foreign key to the category and type in your document table.

2. I would do a seperate table to store the download count. You could then store some extended information for more stats, like datetime fill was downloaded, ip, etc... Doing inserts into a new table would just be a cleaner solution in my opinion.

3. Store the ratings in a different table as well. This way you can control user ratings by user and you can get averages etc.

|||

Hey,

It depends what you want to store. Do you want to store just the total value, or an entry for each download? Do you want to store total ratings, or rating per user so you can lock them out of rating something again? If you just want to store the total, I would store these values in the existing database. If you want the additional information like posted above, then use different tables.

You have to keep in mind performance as well; if you have to calculate lots of values, this may be an added overhead.