Tuesday, March 6, 2012
about tsql
,not the column name
such as
select column1,column2
from...You may need to pur like this ... select col1,col2 from
table name order by 1,2
Hope, I understood the question correctly.
Suresh
>--Original Message--
>in tsql,how can select the data rely on the column order
>,not the column name
>such as
>select column1,column2
>from...
>.
>|||Why would you want to do this' If you could state your problem, maybe we
can give you an alternate solution.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> in tsql,how can select the data rely on the column order
> ,not the column name
> such as
> select column1,column2
> from...
>|||i want to get several tables' first column's data
and the table name store in one table, and of course the
table's first column's name is different
in short
i want use a loop update several table's first column
>--Original Message--
>Why would you want to do this' If you could state your
problem, maybe we
>can give you an alternate solution.
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> in tsql,how can select the data rely on the column order
>> ,not the column name
>> such as
>> select column1,column2
>> from...
>
>.
>|||In the SQL language, you refer to columns by name, not position. There are two exceptions: "SELECT
*" and INSERT without a column name list.
You can use the INFORMATION_SCHEMA views to read each table name and the name of the first column
name. And based on that, you can construct your SQL statement and run that through dynamic SQL (EXEC
@.sql). The info schema views are documented in Books Online and I've done a diagram of them at
http://www.dbmaint.com/info_schema.asp.
You can find a lot of info on dynamic SQL at:
http://www.algonet.se/~sommar/
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
> i want to get several tables' first column's data
> and the table name store in one table, and of course the
> table's first column's name is different
> in short
> i want use a loop update several table's first column
>
>
> >--Original Message--
> >Why would you want to do this' If you could state your
> problem, maybe we
> >can give you an alternate solution.
> >--
> >HTH,
> >SriSamp
> >Please reply to the whole group only!
> >http://www32.brinkster.com/srisamp
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> >> in tsql,how can select the data rely on the column order
> >> ,not the column name
> >>
> >> such as
> >> select column1,column2
> >> from...
> >>
> >
> >
> >.
> >|||>--Original Message--
>In the SQL language, you refer to columns by name, not
position. There are two exceptions: "SELECT
>*" and INSERT without a column name list.
thanks a lot
>You can use the INFORMATION_SCHEMA views to read each
table name and the name of the first column
>name. And based on that, you can construct your SQL
statement and run that through dynamic SQL (EXEC
>@.sql). The info schema views are documented in Books
Online and I've done a diagram of them at
>http://www.dbmaint.com/info_schema.asp.
>You can find a lot of info on dynamic SQL at:
>http://www.algonet.se/~sommar/
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
>> i want to get several tables' first column's data
>> and the table name store in one table, and of course the
>> table's first column's name is different
>> in short
>> i want use a loop update several table's first column
>>
>>
>> >--Original Message--
>> >Why would you want to do this' If you could state your
>> problem, maybe we
>> >can give you an alternate solution.
>> >--
>> >HTH,
>> >SriSamp
>> >Please reply to the whole group only!
>> >http://www32.brinkster.com/srisamp
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> >> in tsql,how can select the data rely on the column
order
>> >> ,not the column name
>> >>
>> >> such as
>> >> select column1,column2
>> >> from...
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Friday, February 24, 2012
about sorting
i use a full text for my db
however, when i write the SQL w/ order by and w/o order by
it's obviously time gap.
w/o using order by SQL, which return record within 5 seconds
w/ using order by SQL, which return record over 20 seconds
how can i elimiate the searching time by using order by SQL in fulltext
database ?
Thanks in advance!
Q,
Could you post the full output of SELECT @.@.version -- as this will provide
information about your environment. Could you also provide the total number
of rows in your FT-enabled table along with the expected number of rows to
be returned by your contains or freetext query?
Depending upon your answers, you may want to use CONTAINSTABLE or
FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
2000 rows or less and use an ORDER BY clause based upon RANK.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> i use a full text for my db
> however, when i write the SQL w/ order by and w/o order by
> it's obviously time gap.
> w/o using order by SQL, which return record within 5 seconds
> w/ using order by SQL, which return record over 20 seconds
> how can i elimiate the searching time by using order by SQL in fulltext
> database ?
> Thanks in advance!
>
|||"Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)"
actually, i have a 4 diff. full-text field, in one table which are (field_1,
field_2, field_3, field_4)
my query like this
select * from tblTest
where contains(field_1, '"my words"')
OR contains(field_2, '"my words"')
OR contains(field_3, '"my words"')
OR contains(field_4, '"my words"')
order by my_year desc, my_date desc
i MUST divided into 4 fields for searching, because sometimes, it only
involced field_1 for searching purpose!
"John Kane" <jt-kane@.comcast.net> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> Q,
> Could you post the full output of SELECT @.@.version -- as this will provide
> information about your environment. Could you also provide the total
> number
> of rows in your FT-enabled table along with the expected number of rows to
> be returned by your contains or freetext query?
> Depending upon your answers, you may want to use CONTAINSTABLE or
> FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
> 2000 rows or less and use an ORDER BY clause based upon RANK.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
|||Q,
First of all, thank you for providing the requested info!
You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
infosoft.dll as the OS-supplied wordbreaker.
What is causing the slow performance is not the ORDER BY clause, but the
multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
below query is reading the FT Catalog four times! Unfortunately, this is "by
design" for SQL Server 2000. As a workaround, you may want to try using one
FREETEXT query and using the "*" (asterisk) for all FT-enabled columns, this
should perform better, but may give you more results than CONTAINS as
FREETEXT can search "across columns", and is less precise in its results
than CONTAINS.
See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with CONTAINS
Clause Search Across Columns" at
http://support.microsoft.com/default...b;en-us;294809 for more info
as this behavior is also the default behavior for SQL Server 2000. You
should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank as
this parameter restricts the full "round trip" to just the Top N (some
specified number) by RANK.
FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
the above query is functionally equivalent to the following query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)"
> actually, i have a 4 diff. full-text field, in one table which are
(field_1,
> field_2, field_3, field_4)
> my query like this
> select * from tblTest
> where contains(field_1, '"my words"')
> OR contains(field_2, '"my words"')
> OR contains(field_3, '"my words"')
> OR contains(field_4, '"my words"')
> order by my_year desc, my_date desc
> i MUST divided into 4 fields for searching, because sometimes, it only
> involced field_1 for searching purpose!
>
> "John Kane" <jt-kane@.comcast.net>
glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...[vbcol=seagreen]
provide[vbcol=seagreen]
to
>
|||Thanks John Kane alot!
"John Kane" <jt-kane@.comcast.net> glsD:OFskTDQmFHA.2860@.TK2MSFTNGP15.phx.g bl...
> Q,
> First of all, thank you for providing the requested info!
> You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
> infosoft.dll as the OS-supplied wordbreaker.
> What is causing the slow performance is not the ORDER BY clause, but the
> multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
> i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
> below query is reading the FT Catalog four times! Unfortunately, this is
> "by
> design" for SQL Server 2000. As a workaround, you may want to try using
> one
> FREETEXT query and using the "*" (asterisk) for all FT-enabled columns,
> this
> should perform better, but may give you more results than CONTAINS as
> FREETEXT can search "across columns", and is less precise in its results
> than CONTAINS.
> See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with
> CONTAINS
> Clause Search Across Columns" at
> http://support.microsoft.com/default...b;en-us;294809 for more
> info
> as this behavior is also the default behavior for SQL Server 2000. You
> should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank
> as
> this parameter restricts the full "round trip" to just the Top N (some
> specified number) by RANK.
> FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
> column syntax, for example:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
> the above query is functionally equivalent to the following query:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
> CONTAINS(FTSCol3, '"john" AND "kane"')
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> (field_1,
> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> provide
> to
>
Thursday, February 16, 2012
about optimization?
There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables
however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?
thanks in advance
Moving to more appropriate forum...|||Generally speaking, you usually don't want more than about 3-4 indexes on a table that is used for an OLTP workload. Rather than guessing, if you have SQL Server 2005, you can run the query below to see which indexes are being used. If you see indexes that have zero or a very low number of reads, then you should consider dropping those indexes.
Index Read/Write stats for a single table
DECLARE @.dbid int
SELECT @.dbid = db_id()
SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id,
reads = user_seeks + user_scans + user_lookups, writes = user_updates
FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.object_id = i.object_id
AND i.index_id = s.index_id
AND s.database_id = @.dbid
AND object_name(s.object_id) = 'YourTableName'
ORDER BY object_name(s.object_id), writes DESC, reads DESC;
|||thank you very much
thus i can first tune indexs
then consider to stop index before etl, and rebuild index after that
Monday, February 13, 2012
About lost sa password when rebuild master database
I rebuild master database in order to change collation setting, but I lost
sa password After rebuild. I couldn't logon into database with sa. Why is it
like this, can I re-get sa password? Thanks.
Jerry MuNot sure what could have happen but ... have you tried to log in as a member
of the BUILTIN\Administrators group?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Iter" wrote:
> Hi Guys,
> I rebuild master database in order to change collation setting, but I lost
> sa password After rebuild. I couldn't logon into database with sa. Why is it
> like this, can I re-get sa password? Thanks.
> Jerry Mu
>|||login with a Windows NT account that is part of sysadmin
(builtin\administrator) and reset the password.
e.g.
exec sp_password null,'new password', 'sa'
-oj
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A02122C4-8006-4D6E-99B1-A2D0DD016362@.microsoft.com...
> Hi Guys,
> I rebuild master database in order to change collation setting, but I lost
> sa password After rebuild. I couldn't logon into database with sa. Why is
> it
> like this, can I re-get sa password? Thanks.
> Jerry Mu
>
About lost sa password when rebuild master database
I rebuild master database in order to change collation setting, but I lost
sa password After rebuild. I couldn't logon into database with sa. Why is it
like this, can I re-get sa password? Thanks.
Jerry MuNot sure what could have happen but ... have you tried to log in as a member
of the BUILTIN\Administrators group?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Iter" wrote:
> Hi Guys,
> I rebuild master database in order to change collation setting, but I lost
> sa password After rebuild. I couldn't logon into database with sa. Why is
it
> like this, can I re-get sa password? Thanks.
> Jerry Mu
>|||login with a Windows NT account that is part of sysadmin
(builtin\administrator) and reset the password.
e.g.
exec sp_password null,'new password', 'sa'
-oj
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A02122C4-8006-4D6E-99B1-A2D0DD016362@.microsoft.com...
> Hi Guys,
> I rebuild master database in order to change collation setting, but I lost
> sa password After rebuild. I couldn't logon into database with sa. Why is
> it
> like this, can I re-get sa password? Thanks.
> Jerry Mu
>
Thursday, February 9, 2012
about CTE in SQL 2k5
I tested a new feature of SQL2005 (named cte for recursive selection)
and found the order of the result was weird.
According to the description of help:
1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base
result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an
output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
But the result was just like the stack-calls.
Is there something wrong on the description of the help?
table creation script:
CREATE TABLE tbUser(
userId int NOT NULL,
mgrId int NULL,
CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table values:
insert tbUser values(1,null)
insert tbUser values(2,1)
insert tbUser values(3,2)
insert tbUser values(4,2)
CTE scripts
WITH reps_cte (userId, mgrId, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
higher level of the mgr
FROM reps_cte inner join tbUser -- Join with Employee
on reps_cte.mgrId= tbUser.userId -- This employee's manager
where recursion_level<=20 -- up to 20 levels of mgmt
)
Result:
1 NULL 0
2 1 0
3 2 0
4 2 0
4 1 1
4 NULL 2
3 1 1
3 NULL 2
2 NULL 1
Expecting result:
1 NULL 0 - T0 result
2 1 0 - T0 result
3 2 0 - T0 result
4 2 0 - T0 result
2 NULL 1 - T1 result
3 1 1 - T1 result
4 1 1 - T1 result
3 NULL 2 - T2 result
4 NULL 2 - T2 result
Regards,
congling"congling" <congling@.hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
The order of a result is always undefined unless the query contains an ORDER
BY clause. The BOL has this description under the heading of "Pseudocode
and Semantics", and it's is a logical description of the result set, not a
guarantee about how it is processed or the order in which the rows are
returned.
David|||congling wrote:
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
> table creation script:
> CREATE TABLE tbUser(
> userId int NOT NULL,
> mgrId int NULL,
> CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
> (
> [userId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> table values:
> insert tbUser values(1,null)
> insert tbUser values(2,1)
> insert tbUser values(3,2)
> insert tbUser values(4,2)
> CTE scripts
> WITH reps_cte (userId, mgrId, recursion_level)
> AS
> (
> SELECT userId, mgrId, 0 FROM tbUser
> UNION ALL
> SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
> FROM reps_cte inner join tbUser -- Join with Employe
e
> on reps_cte.mgrId= tbUser.userId -- This employee's manag
er
> where recursion_level<=20 -- up to 20 levels of mgmt
> )
> Result:
> 1 NULL 0
> 2 1 0
> 3 2 0
> 4 2 0
> 4 1 1
> 4 NULL 2
> 3 1 1
> 3 NULL 2
> 2 NULL 1
> Expecting result:
> 1 NULL 0 - T0 result
> 2 1 0 - T0 result
> 3 2 0 - T0 result
> 4 2 0 - T0 result
> 2 NULL 1 - T1 result
> 3 1 1 - T1 result
> 4 1 1 - T1 result
> 3 NULL 2 - T2 result
> 4 NULL 2 - T2 result
>
> Regards,
> congling
If you don't specify ORDER BY then the sorting of the result is
undefined. Try:
WITH reps_cte (userid, mgrid, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1
FROM reps_cte inner join tbUser
on reps_cte.mgrId= tbUser.userId
where recursion_level<=1
)
SELECT userid, mgrid, recursion_level
FROM reps_cte
ORDER BY recursion_level, mgrid, userid ;
David Portas
SQL Server MVP
--|||thx
Regards,
congling
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> ะด?:%23CcF$FxAGHA.20
40@.TK2MSFTNGP14.phx.gbl...
> "congling" <congling@.hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> The order of a result is always undefined unless the query contains an
> ORDER BY clause. The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
> David
>