Hi! How do you query an entire db for a value?
I know how to do table queries from the SQL Analyzer, but not a full DB search.
Like, if I wanted to find a value '137.51 in a DB, what would I use?
Many thanks!
JJDynamic SQL
A Cursor
A temp table
And a lot of hours
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Tuesday, March 27, 2012
General db queries
Hi
I am currently running the IbuySpy Portal on my lap top using MSDE. Could anyone clarify a few points for me ...
1) If I buy SQL Server 2000 which I aim to do ... will this simply install over my MSDE and leave my portal database intact and run it as per normal?
2) I currently use web matrix to explore my portal database - I notice that in the users table of the portal the field size property for the key UserID field is set to "4" - does this in fact mean that there is only capacity to have 9999 users and then no more can be added? I assume this is correct.
3) About sql scripts - is there some utility within SQL Server that you can point at a database and it will create a .sql install script that will recreate a database - is this what Query Analyser is capable of??
Any clarification welcomed
Regards
Johnnny BHope this helps:
1. I don't believe so. You will have to run the sql scripts from MSDE into SQL Analyzer.
2. Int data type's range is: -2,147,483,48 to 2,147,483,647.
3. Try the SCRIPT method: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.aspsql
Monday, March 26, 2012
General Advice: Multiple queries VS large joins
I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks!
Andrew Wied
It really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks!
Andrew Wied
It really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
General Advice: Multiple queries VS large joins
I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)
Is there different scenarios where one approach is better than the other
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)
Is there different scenarios where one approach is better than the other
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
General Advice: Multiple queries VS large joins
I am wondering: I have a database with a few disjoint sets, and I was wonder
ing what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database,
or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data s
ize and rather light accesses to my app. Any suggestions?
Thanks!
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
ing what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database,
or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data s
ize and rather light accesses to my app. Any suggestions?
Thanks!
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
Sunday, February 19, 2012
full-text queries with express edition
Hi,
I understand that full-text indexing is not available in the express
edition.
My situation is that we have to write an application to take advantage of
full-text-indexing when available.
Quesitions are.
1. Does "freetext" and "contains" queries works on express edition?
(by doing a full table scan or something like that)
2. If it does not, how do I write the c# that recognize the capability,
so that c# code can send/execute different queries based on
"full-text-capability" of sql-server.
any direction is deeply appreciated.
Thanks
Nalaka
1) It currently does not work on SQL FTS.
2) I believe the February issue of SQL Server pro has an article on how to
get this working using pure tsql.
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
"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:u%23h7eJNMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I understand that full-text indexing is not available in the express
> edition.
> My situation is that we have to write an application to take advantage of
> full-text-indexing when available.
> Quesitions are.
> 1. Does "freetext" and "contains" queries works on express edition?
> (by doing a full table scan or something like that)
> 2. If it does not, how do I write the c# that recognize the capability,
> so that c# code can send/execute different queries based on
> "full-text-capability" of sql-server.
> any direction is deeply appreciated.
> Thanks
> Nalaka
>
|||Nalaka wrote on Mon, 13 Feb 2006 11:19:10 -0800:
> Hi,
> I understand that full-text indexing is not available in the express
> edition.
> My situation is that we have to write an application to take advantage of
> full-text-indexing when available.
> Quesitions are.
> 1. Does "freetext" and "contains" queries works on express edition?
> (by doing a full table scan or something like that)
> 2. If it does not, how do I write the c# that recognize the capability,
> so that c# code can send/execute different queries based on
> "full-text-capability" of sql-server.
> any direction is deeply appreciated.
In my code I first do the following:
select FULLTEXTCATALOGPROPERTY('STKcatalog', 'ItemCount'),
FULLTEXTCATALOGPROPERTY('STKcatalog', 'PopulateStatus')
If NULLs returned, FTS is not available for the named catalog (in my case
STKcatalog). I also use these values to double check whether the index is
being rebuilt, and to make sure that the ItemCount value is close to my
table row count. This way if there is not FTS catalog, or it's being
populated, I can switch my query to use LIKE rather than CONTAINS.
Dan
I understand that full-text indexing is not available in the express
edition.
My situation is that we have to write an application to take advantage of
full-text-indexing when available.
Quesitions are.
1. Does "freetext" and "contains" queries works on express edition?
(by doing a full table scan or something like that)
2. If it does not, how do I write the c# that recognize the capability,
so that c# code can send/execute different queries based on
"full-text-capability" of sql-server.
any direction is deeply appreciated.
Thanks
Nalaka
1) It currently does not work on SQL FTS.
2) I believe the February issue of SQL Server pro has an article on how to
get this working using pure tsql.
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
"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:u%23h7eJNMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I understand that full-text indexing is not available in the express
> edition.
> My situation is that we have to write an application to take advantage of
> full-text-indexing when available.
> Quesitions are.
> 1. Does "freetext" and "contains" queries works on express edition?
> (by doing a full table scan or something like that)
> 2. If it does not, how do I write the c# that recognize the capability,
> so that c# code can send/execute different queries based on
> "full-text-capability" of sql-server.
> any direction is deeply appreciated.
> Thanks
> Nalaka
>
|||Nalaka wrote on Mon, 13 Feb 2006 11:19:10 -0800:
> Hi,
> I understand that full-text indexing is not available in the express
> edition.
> My situation is that we have to write an application to take advantage of
> full-text-indexing when available.
> Quesitions are.
> 1. Does "freetext" and "contains" queries works on express edition?
> (by doing a full table scan or something like that)
> 2. If it does not, how do I write the c# that recognize the capability,
> so that c# code can send/execute different queries based on
> "full-text-capability" of sql-server.
> any direction is deeply appreciated.
In my code I first do the following:
select FULLTEXTCATALOGPROPERTY('STKcatalog', 'ItemCount'),
FULLTEXTCATALOGPROPERTY('STKcatalog', 'PopulateStatus')
If NULLs returned, FTS is not available for the named catalog (in my case
STKcatalog). I also use these values to double check whether the index is
being rebuilt, and to make sure that the ItemCount value is close to my
table row count. This way if there is not FTS catalog, or it's being
populated, I can switch my query to use LIKE rather than CONTAINS.
Dan
Subscribe to:
Posts (Atom)