how to free memory used by prior query statement within a batch by TSQL?

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.What about this:

select ...
DBCC DROPCLEANBUFFERS
select ...|||Won't that force a recompile of everything?

Gotta look that up...

btw...SQL Server will grab as much memory is available, and will only release it if it's not using it and something else needs it...

It's not very sociable...|||thanks for help, but it seemed not work as I hoped.

Sqlserver used 20M memory before I run the select query;
Sqlserver used 123M memory after I run the select query;
Sqlserver still used 123M memory after I run 'DBCC DROPCLEANBUFFERS', but I want memory used by Sqlserver not larger than 20M;

I don't know exactly how memory useage affect the performance of next query's execution, so I write down my primal Intention:

select... -- query A

/* do something here to make query B to be executed just as query A was not executed before( or minish query A's affection). */

select... -- query B

could I make it?|||here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average time spending;
fetch next query from cursor;
end
...

Is there any better test plan?(just test time spending)

How to format numbers in SQL Query

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99Create the below function and use it as said below.

/*This function is only for thousand separator for numbers with length 5 or 4*/
CREATE FUNCTION DBO.SEPARATETHOUSANDNUM
(
@.STRVALUE VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @.STRRETURNVALUE VARCHAR(8000)
SELECT @.STRRETURNVALUE = CASE LEN(@.STRVALUE)
WHEN 5 THEN LEFT(@.STRVALUE,2)+ ','+ RIGHT(@.STRVALUE,3)
WHEN 4 THEN LEFT(@.STRVALUE,1)+ ','+ RIGHT(@.STRVALUE,3)
ELSE @.STRVALUE END
RETURN @.STRRETURNVALUE
END

SELECT DBO.SEPAREATENUMBERS(23565) AS CHANGEDCOLUMN

gives 23,565

SELECT DBO.SEPAREATENUMBERS(2365) AS CHANGEDCOLUMN

gives 2,365

So use
Select DBO.SEPARATETHOUSANDNUM(TotalAmount) from Table1

Quote:

Originally Posted by sukeshchand

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

|||I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser

Excellent! Thanks for posting the solution!|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(money,TotalAmount),1) from BillMaser

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?|||

Quote:

Originally Posted by mjv

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

try adding precision on your convert function.

actually, although this is feasible in the database/back-end, i believe this can be better be handled in the front-end.

How to format numbers in a query

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.select to_char(number1/number2, '99.99') As testDiv from table;|||Originally posted by r123456
select to_char(number1/number2, '99.99') As testDiv from table;

When I do this, I get the error message that "to_char is not a vaild function name'|||What db are you using? to_char works for Oracle, but not SQL server.

Try:

select convert(money, (number1/number2)) As testDiv from tablesql

How to format in SSMS?

How can I format a query in SSMS so it does not look like this:

sELecT * fRoM CusTomERs

Currently SSMS doesn't have any tools to format queries except of query designer and Ctrl+Shift+U or Ctrl+Shift+L.|||I use promptsql.com for intellisense, which allow me to pick table and column names from a drop down. It integrates with SSMS and QA. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:da5be6aa-131a-4585-92c5-5114765096f7@.discussions.microsoft.com...
> Currently SSMS doesn't have any tools to format queries except of query
> designer and Ctrl+Shift+U or Ctrl+Shift+L.
>|||

Somebdy wake me from this bad dream!

Yes I have tried promptsql. It is slow and does not format keywords, but is the only intellisense addon that works with SSMS. A much better product is SqlAssist but only works in Visual Studio. VS on the other hand is horrible for working with dbs, so I started cutting and pasting queries into SSMS.

Shame on Microsoft for all this marketting hoopla and they basically put out archaic software that is stuck in the 70s.

|||FWIW the latest release of PromptSQL will auto-uppercase keywords, and has new caching features which should make it faster.sql

How to format in SSMS?

How can I format a query in SSMS so it does not look like this:

sELecT * fRoM CusTomERs

Currently SSMS doesn't have any tools to format queries except of query designer and Ctrl+Shift+U or Ctrl+Shift+L.|||I use promptsql.com for intellisense, which allow me to pick table and column names from a drop down. It integrates with SSMS and QA. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:da5be6aa-131a-4585-92c5-5114765096f7@.discussions.microsoft.com...
> Currently SSMS doesn't have any tools to format queries except of query
> designer and Ctrl+Shift+U or Ctrl+Shift+L.
>|||

Somebdy wake me from this bad dream!

Yes I have tried promptsql. It is slow and does not format keywords, but is the only intellisense addon that works with SSMS. A much better product is SqlAssist but only works in Visual Studio. VS on the other hand is horrible for working with dbs, so I started cutting and pasting queries into SSMS.

Shame on Microsoft for all this marketting hoopla and they basically put out archaic software that is stuck in the 70s.

|||FWIW the latest release of PromptSQL will auto-uppercase keywords, and has new caching features which should make it faster.

How to format a date field in select query

Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.

select id, name, create_date from actionstable;

Please help, Thank you.You can use the SQL CONVERT() function, to convert the date to an nvarchar(), or better, format the date in the presentation layer, in the datagrid itself. In the DataFormatString of the DataGrid's column that will contain the date, use 0:d

how to form query?

i am using this statement
select dateadd(dd,1,20010331)
and it's throwing an error
Arithmetic overflow error converting expression to data type datetime.
what's wrong?sql server wants to have date strings, not integers

select dateadd(dd,1,'2001-03-31')|||i am using this statement

select dateadd(dd,1,20010331)


and it's throwing an error

Arithmetic overflow error converting expression to data type datetime.

what's wrong?You're missing quotes:

select dateadd(dd,1,'20010331')

How to force Query Optimizer to do what I want ?

I'm really going nuts. I have the following two tables:
Table1: 20 Mio Records, 2 Fields
Table2: 600'000 Records, 25 Fields
and the following query:
SELECT Fields FROM Table1 t1, Table2 t2
WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 AND
t2.category <> 3
ORDER BY field1, field2, field3
The Query optimizer decides to filter (and sort!!) table 2 first and then
join it to table 1. this takes up to 10 minutes. If it would go the other wa
y
round and first query table 1 using the Searchword criteria and then joint
the result set to table2, the query would take maybe 1-2 seconds. I updated
statistics, all indices are there. What the hell is going on ?
BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange...
HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
that didn't help. I assume since table 1 has so many rows and just 2 fields,
it decides it has very poor selectivity (which is actually wrong) and uses
the smaller table 2 instead. I tried JOIN ON, doesn't help.
Any help is greatly appreciated !
mbrtal. Enterprise Windows Application Development.Please be more specific about your problem. Give the _exact_ query (for this
kind of performance trouble shooting it is essential to know which columns
you select and order by. "Fields" and "field1, field2, field3" just doesn't
convey that kind of information) and give the DDL for you tables _and_
indexes. (See www.aspfaq.com/5006)
Jacco Schalkwijk
SQL Server MVP
"mbrtal" <mbrtal@.discussions.microsoft.com> wrote in message
news:20ACC629-4052-45E9-8C00-59A45CC1C226@.microsoft.com...
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12
> AND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
> way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I
> updated
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very
> strange...
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2
> fields,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.
>|||mbrtal,
You say about Table1 that "it [sql-server] decides it has very poor
selectivity (which is actually wrong)". How do you know that? If the
table has high selectivity and you have updated the statistics WITH
FULL_SCAN, then SQL-Server will judge the selectivity correctly. Why do
you think the different access path will result in a 30,000 percent
performance gain if you haven't seen SQL-Server execute the query this
way?
If you want to test the performance and see the query plan when Table1
is accessed first, then make sure Table1 is mentioned first (which is
already the case) and add the query hint OPTION (FORCE ORDER) to the
query.
But I have to agree with Jacco. Please post the actual query and
(simplified) DDL. Some information we are missing now:
- What indexes are present, and are they clustered?
- What tables do field1, field2 and field3 originate from?
- What data type definition do the columns Table1(SearchWord),
Table2(Status) and Table2(Category) have?
- Does Table1(id) have the same data type and size as Table2(id)?
- etc. etc.
If SQL-Server does not choose the 'obvious' plan, there there is
probably a very good reason for it. The limited information you posted
will not reveal the necessary details...
Gert-Jan
mbrtal wrote:
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 A
ND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I update
d
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange..
.
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2 field
s,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.

How to force 'lazy spool'

Hi,
I've got 2 server. One is the restore of the other.
But query where not performed by the same way on each.
One use a lazy spool, and the other does not.
The first make 2s to answer the query
The second make 4min !
I'd like to anderstand why !
It's the same installation of SQL Server 2000
And database are restore from each other.
Did anyone have an explication ?
ThanksHi
update statistics on both servers (for details please refer to the BOL)
"Florimond" <florimond@.gmail.com> wrote in message
news:ee293729.0504270647.7322ed74@.posting.google.com...
> Hi,
> I've got 2 server. One is the restore of the other.
> But query where not performed by the same way on each.
> One use a lazy spool, and the other does not.
> The first make 2s to answer the query
> The second make 4min !
> I'd like to anderstand why !
> It's the same installation of SQL Server 2000
> And database are restore from each other.
> Did anyone have an explication ?
> Thanks

How to force FTS on fractions?

When I made the jump to SQL 2005 from SQL 2000, the following query stopped
working. ie no result set unless I remove the fraction 1/4.
(Removing the fraction 1/4 works, but then returns many many rows)
SELECT * FROM MAKT
WHERE CONTAINS(MAKTX,'"valve" AND "SS" AND "needle" AND "1/4"')
With my old database on SQL2000, the same query returns:
IDMATNRMAKTX
65129502030710Valve, needle, 1/4", body material ss
77651902030890Valve, needle, 1/4", body material ss
59756502030900Valve, needle, 1/4", body material ss
77651602030860Valve, needle, 1/4", body material ss
60563702030990Valve, needle, 1/4", body material ss
***
The SQL 2000 database was copied to a new WIN2003 x64 server with SQL 2005
pro x64. Other than OS and SQL version, the databases are identical. Both
databases have FTS on the table in question. SQL 2005 just won't return a
result set on fractions. Is there a switch or registry hack? Can I go back to
SQL2000 FTS engine?
This works for me, what language are you querying and indexing in?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Dennis4j" <Dennis4j@.discussions.microsoft.com> wrote in message
news:9F4FDA26-FAA1-468E-930C-9DD4FC9512C8@.microsoft.com...
> When I made the jump to SQL 2005 from SQL 2000, the following query
> stopped
> working. ie no result set unless I remove the fraction 1/4.
> (Removing the fraction 1/4 works, but then returns many many rows)
> SELECT * FROM MAKT
> WHERE CONTAINS(MAKTX,'"valve" AND "SS" AND "needle" AND "1/4"')
> With my old database on SQL2000, the same query returns:
> ID MATNR MAKTX
> 651295 02030710 Valve, needle, 1/4", body material ss
> 776519 02030890 Valve, needle, 1/4", body material ss
> 597565 02030900 Valve, needle, 1/4", body material ss
> 776516 02030860 Valve, needle, 1/4", body material ss
> 605637 02030990 Valve, needle, 1/4", body material ss
> ***
> The SQL 2000 database was copied to a new WIN2003 x64 server with SQL 2005
> pro x64. Other than OS and SQL version, the databases are identical. Both
> databases have FTS on the table in question. SQL 2005 just won't return a
> result set on fractions. Is there a switch or registry hack? Can I go back
> to
> SQL2000 FTS engine?
>

how to force a table scan in query

I have a query thats using a particular index and I would like to force the
query to do a table scan instead. How can i do so ?
Heres a sample query
select * from tableA where col1= 5Geeze why would you want to?!
I think your only way would be to drop the index. I don't think you can
force the optimiser not to use an index...
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||Remove the index.
>--Original Message--
>I have a query thats using a particular index and I would
like to force the
>query to do a table scan instead. How can i do so ?
>Heres a sample query
>select * from tableA where col1= 5
>
>.
>|||Table hint WITH INDEX(0)
BOL: If a clustered index exists, INDEX(0) forces a clustered index scan
and INDEX(1) forces a clustered index scan or seek. If no clustered index
exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an
error.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||There are definitely reasons why you wouldn't want to use an index, but most
of them can be solved by making sure your statistics are updated. If the
optimizer is incorrectly choosing to use a nonclustered index, you can far
far more reads than a simple table scan would take. You might also just want
to run the query without the index for testing and comparison purposes, to
find out how much the index is really saving you, to see if it's worth the
cost of its maintenance.
As Russell pointed out, you can use WITH INDEX(0) to force no index to be
used on a particular table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"London Developer" <dev@.nowhere.com> wrote in message
news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Geeze why would you want to?!
> I think your only way would be to drop the index. I don't think you can
> force the optimiser not to use an index...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I have a query thats using a particular index and I would like to force
> the
> > query to do a table scan instead. How can i do so ?
> >
> > Heres a sample query
> >
> > select * from tableA where col1= 5
> >
> >
>|||btw , Kalen, in your latest article in SQL Mag about the optimiser, listing
2 did take an index scan when you mentioned that it would take a table scan
bcos of less reads. My optimiser is not smart enough I guess as yours :-)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> There are definitely reasons why you wouldn't want to use an index, but
most
> of them can be solved by making sure your statistics are updated. If the
> optimizer is incorrectly choosing to use a nonclustered index, you can far
> far more reads than a simple table scan would take. You might also just
want
> to run the query without the index for testing and comparison purposes, to
> find out how much the index is really saving you, to see if it's worth the
> cost of its maintenance.
> As Russell pointed out, you can use WITH INDEX(0) to force no index to be
> used on a particular table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "London Developer" <dev@.nowhere.com> wrote in message
> news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Geeze why would you want to?!
> > I think your only way would be to drop the index. I don't think you can
> > force the optimiser not to use an index...
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I have a query thats using a particular index and I would like to
force
> > the
> > > query to do a table scan instead. How can i do so ?
> > >
> > > Heres a sample query
> > >
> > > select * from tableA where col1= 5
> > >
> > >
> >
> >
>|||Hi Hassan
Can you be more specific? I usually write my articles about 3 months in
advance (I just submitted February's article yesterday), so which is the
latest? October or November?
I'll take a look at it.
Thanks!
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> btw , Kalen, in your latest article in SQL Mag about the optimiser,
listing
> 2 did take an index scan when you mentioned that it would take a table
scan
> bcos of less reads. My optimiser is not smart enough I guess as yours :-)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > There are definitely reasons why you wouldn't want to use an index, but
> most
> > of them can be solved by making sure your statistics are updated. If the
> > optimizer is incorrectly choosing to use a nonclustered index, you can
far
> > far more reads than a simple table scan would take. You might also just
> want
> > to run the query without the index for testing and comparison purposes,
to
> > find out how much the index is really saving you, to see if it's worth
the
> > cost of its maintenance.
> >
> > As Russell pointed out, you can use WITH INDEX(0) to force no index to
be
> > used on a particular table.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "London Developer" <dev@.nowhere.com> wrote in message
> > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > Geeze why would you want to?!
> > > I think your only way would be to drop the index. I don't think you
can
> > > force the optimiser not to use an index...
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I have a query thats using a particular index and I would like to
> force
> > > the
> > > > query to do a table scan instead. How can i do so ?
> > > >
> > > > Heres a sample query
> > > >
> > > > select * from tableA where col1= 5
> > > >
> > > >
> > >
> > >
> >
> >
>|||I think it was November..the latest issue on the newstand... that had Yukon
on the cover plus the listing that had examples of orderdetails table
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
These lines in particular
"If you run the command SET STATISTICS IO ON, then execute Listing 2's
queries, you'll see that the queries each take 10 logical reads-one for each
page in the table. The first query returns 58 rows. If the optimizer had
decided to use the nonclustered index on Quantity, SQL Server would have had
to perform 58 bookmark lookup operations, a much higher cost than the 10
logical reads of the table scan. The second query returns 33 rows, so it,
too, would have cost more than 10 logical reads if the optimizer had decided
to access the nonclustered index and perform bookmark lookups."
But when executed my queries had around 60 logical reads since it was using
the index with a bookmark lookup
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> Hi Hassan
> Can you be more specific? I usually write my articles about 3 months in
> advance (I just submitted February's article yesterday), so which is the
> latest? October or November?
> I'll take a look at it.
> Thanks!
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> listing
> > 2 did take an index scan when you mentioned that it would take a table
> scan
> > bcos of less reads. My optimiser is not smart enough I guess as yours
:-)
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > There are definitely reasons why you wouldn't want to use an index,
but
> > most
> > > of them can be solved by making sure your statistics are updated. If
the
> > > optimizer is incorrectly choosing to use a nonclustered index, you can
> far
> > > far more reads than a simple table scan would take. You might also
just
> > want
> > > to run the query without the index for testing and comparison
purposes,
> to
> > > find out how much the index is really saving you, to see if it's worth
> the
> > > cost of its maintenance.
> > >
> > > As Russell pointed out, you can use WITH INDEX(0) to force no index to
> be
> > > used on a particular table.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "London Developer" <dev@.nowhere.com> wrote in message
> > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > Geeze why would you want to?!
> > > > I think your only way would be to drop the index. I don't think you
> can
> > > > force the optimiser not to use an index...
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > I have a query thats using a particular index and I would like to
> > force
> > > > the
> > > > > query to do a table scan instead. How can i do so ?
> > > > >
> > > > > Heres a sample query
> > > > >
> > > > > select * from tableA where col1= 5
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I just got that in the mail today!
I'll take a look and see if I can figure out why you might have gotten
different behavior than I did.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#XajESznDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I think it was November..the latest issue on the newstand... that had
Yukon
> on the cover plus the listing that had examples of orderdetails table
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
> These lines in particular
> "If you run the command SET STATISTICS IO ON, then execute Listing 2's
> queries, you'll see that the queries each take 10 logical reads-one for
each
> page in the table. The first query returns 58 rows. If the optimizer had
> decided to use the nonclustered index on Quantity, SQL Server would have
had
> to perform 58 bookmark lookup operations, a much higher cost than the 10
> logical reads of the table scan. The second query returns 33 rows, so it,
> too, would have cost more than 10 logical reads if the optimizer had
decided
> to access the nonclustered index and perform bookmark lookups."
> But when executed my queries had around 60 logical reads since it was
using
> the index with a bookmark lookup
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi Hassan
> >
> > Can you be more specific? I usually write my articles about 3 months in
> > advance (I just submitted February's article yesterday), so which is the
> > latest? October or November?
> > I'll take a look at it.
> >
> > Thanks!
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> > listing
> > > 2 did take an index scan when you mentioned that it would take a table
> > scan
> > > bcos of less reads. My optimiser is not smart enough I guess as yours
> :-)
> > >
> > >
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > > There are definitely reasons why you wouldn't want to use an index,
> but
> > > most
> > > > of them can be solved by making sure your statistics are updated. If
> the
> > > > optimizer is incorrectly choosing to use a nonclustered index, you
can
> > far
> > > > far more reads than a simple table scan would take. You might also
> just
> > > want
> > > > to run the query without the index for testing and comparison
> purposes,
> > to
> > > > find out how much the index is really saving you, to see if it's
worth
> > the
> > > > cost of its maintenance.
> > > >
> > > > As Russell pointed out, you can use WITH INDEX(0) to force no index
to
> > be
> > > > used on a particular table.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "London Developer" <dev@.nowhere.com> wrote in message
> > > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > > Geeze why would you want to?!
> > > > > I think your only way would be to drop the index. I don't think
you
> > can
> > > > > force the optimiser not to use an index...
> > > > >
> > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > > I have a query thats using a particular index and I would like
to
> > > force
> > > > > the
> > > > > > query to do a table scan instead. How can i do so ?
> > > > > >
> > > > > > Heres a sample query
> > > > > >
> > > > > > select * from tableA where col1= 5
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to force 0's in place of null values..

Hi,
I am using an MDX query which generates null values,Instead of null values I
need to display 0 's.
I have tried the below options but none is working fine for me,
please give me any help/suggestion/URL.Its really urgent !!
=Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
=iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
=Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
Thanks and Regards,
Rajesh Yennam.
HA India.This what I use and it works fine.
Good Luck!
=iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
message news:RajeshYennam@.discussions.microsoft.com:
> Hi,
> I am using an MDX query which generates null values,Instead of null values I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||I asked the same thing in the Olap-news group
(microsoft.public.sqlserver.olap). Here are the answers I got:
***
tawargerip@.hotmail.com <tawargerip@.hotmail.com>:
use IIF( isempty(<Measure name>),0,<Measure name>)
you may need to create a calculated member for each measure like this
and
Cymryr <Cymryr@.hotmail.com>:
Function CoalesceEmpty is the right way to do it, see BOL
***
The CoalesceEmpty looks promising, but I haven't got around to implement it
yet.
Kaisa M. Lindahl
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:BE2BF79D-CDBC-488D-9B31-6F9132507717@.microsoft.com...
> Hi,
> I am using an MDX query which generates null values,Instead of null values
I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||Thanks for your quick response John.Actually this also not working for me. If
you find any alternative solution please post it.
Thanks & Regards,
Rajesh Yennam,
HA India.
"John Geddes" wrote:
> This what I use and it works fine.
> Good Luck!
> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>
>
> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
> message news:RajeshYennam@.discussions.microsoft.com:
> > Hi,
> > I am using an MDX query which generates null values,Instead of null values I
> > need to display 0 's.
> > I have tried the below options but none is working fine for me,
> > please give me any help/suggestion/URL.Its really urgent !!
> >
> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> >
> > Thanks and Regards,
> > Rajesh Yennam.
> > HA India.
>
>|||Put the null detection code in a function in the Code element of the report.
The problem with IIF is that it evaluates all conditions and throws on NULL.
As simple if then else statement will work.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:CBD98285-010A-4EB9-8DFE-2DBF0875C898@.microsoft.com...
> Thanks for your quick response John.Actually this also not working for me.
> If
> you find any alternative solution please post it.
> Thanks & Regards,
> Rajesh Yennam,
> HA India.
> "John Geddes" wrote:
>> This what I use and it works fine.
>> Good Luck!
>> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>>
>>
>> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
>> message news:RajeshYennam@.discussions.microsoft.com:
>> > Hi,
>> > I am using an MDX query which generates null values,Instead of null
>> > values I
>> > need to display 0 's.
>> > I have tried the below options but none is working fine for me,
>> > please give me any help/suggestion/URL.Its really urgent !!
>> >
>> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
>> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
>> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
>> >
>> > Thanks and Regards,
>> > Rajesh Yennam.
>> > HA India.
>>

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIA
Data Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am no
t able to know
which line is giving the problem. How do I make QA show me the offending lin
e.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIAsql

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||This is a multi-part message in MIME format.
--010807000500020704010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
--010807000500020704010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Depends on what info you want to find out. But it will be some
variation on this:<br>
</tt>
<blockquote><tt>select object_name(i.[id]) as tablename, i.*</tt><br>
<tt>from dbo.sysindexes as i</tt><br>
<tt> inner join dbo.sysfilegroups as g on g.groupid = i.groupid</tt><br>
<tt>where g.groupname = 'PRIMARY'<br>
</tt></blockquote>
<tt>If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Anubis wrote:
<blockquote cite="miduyBFlhIcFHA.1148@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
</pre>
</blockquote>
</body>
</html>
--010807000500020704010007--|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find transposed data and near misses

I would like some advice on a data and query problem I face. I have a
data table with a "raw key" value which is not guaranteed to be valid
at its source. Normally, this value will be 9 numeric digits and map to
a "names" table where the entity is given assigned an "official name".

My problem is that I'd like to be able to identify data values that are
"close" to being "correct". For example, in the case of a
nine digit number such as 077467881, I'd like to be able to identify
rows with values close to this raw string. That is, if
there were a row with a value for this column that was "off" by say, a
transposed single digit (such as 077647881 in this example)
I would like to find a query to locate the "close candidates" in a
result set. If I can find rows having a raw key
value that is close to a "good key" then I can allow my user to use
other criteria to possibly assign the "close key" as
an alternate or alias of the official key. Here is part of my schema:

CREATE TABLE MYData (
StateCD char (2) NOT NULL ,
CountyCD char (3) NOT NULL ,
MYID int NULL ,
RawNumString varchar(9) NULL ,
SaleMnYear datetime NOT NULL ,
NumberWidgets int NOT NULL ,
)

CREATE TABLE MYNames (
MYID int IDENTITY (1, 1) NOT NULL ,
OfficialName varchar (70) NOT NULL ,
CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
(
MYID
)
)
CREATE TABLE MYAltID (
RawNumString varchar (9) NOT NULL ,
MYID int NOT NULL ,
CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
(
RawNumString
) ,
CONSTRAINT FK_HasName FOREIGN KEY
(
MYID
) REFERENCES MYNames (
MYID
)
)
So, how to generalize something like:
SELECT * FROM MYData WHERE RawNumString = '077467881'
OR RawNumString = '077647881'For what it's worth...

The LIKE operator can perform several forms of wildcard comparisons against
2 strings. For example:

if '90120' like '9_120' print 'Yes' else print 'No'
if '90120' like '9012[0..9]' print 'Yes' else print 'No'
if '90120' like '*0120' print 'Yes' else print 'No'

Yes
Yes
Yes

The SoundEx function returns a checksum for a character string, but not
numbers. It basically disregards vowels and double letters and returns a 4
char result. For example:

print soundex('Robert')
print soundex('Roberto')
print soundex('Rabertie')
print soundex('Rabbit')
print soundex('Rob')

R163
R163
R163
R130
R100

These can be included in a where clause. For example:
SELECT * FROM MYData WHERE RawNumString like '*7746*'
SELECT * FROM MYData WHERE SoundEx(RawName) = SoundEx('Francesco')

Keep in mind that performing like or soundex comparisons do not take
advantage of indexes, so performance could be a problem on a large table.

"JJA" <johna@.cbmiweb.com> wrote in message
news:1117641848.807351.148700@.g47g2000cwa.googlegr oups.com...
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
> CREATE TABLE MYData (
> StateCD char (2) NOT NULL ,
> CountyCD char (3) NOT NULL ,
> MYID int NULL ,
> RawNumString varchar(9) NULL ,
> SaleMnYear datetime NOT NULL ,
> NumberWidgets int NOT NULL ,
> )
> CREATE TABLE MYNames (
> MYID int IDENTITY (1, 1) NOT NULL ,
> OfficialName varchar (70) NOT NULL ,
> CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
> (
> MYID
> )
> )
> CREATE TABLE MYAltID (
> RawNumString varchar (9) NOT NULL ,
> MYID int NOT NULL ,
> CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
> (
> RawNumString
> ) ,
> CONSTRAINT FK_HasName FOREIGN KEY
> (
> MYID
> ) REFERENCES MYNames (
> MYID
> )
> )
> So, how to generalize something like:
> SELECT * FROM MYData WHERE RawNumString = '077467881'
> OR RawNumString = '077647881'|||[posted and mailed, please reply in ews]

JJA (johna@.cbmiweb.com) writes:
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:

Fuzzy logic is not for the faint of heart, and it's definitely not my
area of expertise.

Assuming that you always have nine digits, one approach is compare
character by character and if 7 or more match, count this as a possible
match:

SELECT *
FROM tbl
WHERE CASE WHEN substring(col, 1, 1) = substring(@.val, 1, 1)
THEN 1 ELSE 0
END +
CASE WHEN substring(col, 2, 1) = substring(@.val, 2, 1)
THEN 1 ELSE 0
END +
...
CASE WHEN substring(col, 9, 1) = substring(@.val, 9, 1)
THEN 1 ELSE 0
END >= 7

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you ever worked with check digits before? They can prevent errors
in data entry instead of trying to patch them after the fact. The idea
of keeping an invalid key does not sound like a good design.|||Yes, I know this is not good design but we are getting a raw data file
from another organization and we have no control over their practices.
Most occurrences of this number are "valid" but it is clear from
looking at the data that there is no validation at the source. The
nature of the data is such that if we can identify 7 or 8 bytes of data
as being the same as another 9 byte and valid "key", we could assume
the key could be improved to point at the same 9 byte valid entity. So,
I thought I'd run this notion past the world of experts for some ideas.|||Thanks very much for this neat suggestion. It is exactly what I hoped
for and I can implement this a stored procedure with a couple of
parameters. I will provide a little interface where the analyst can
launch the sproc and see if there are any "near-misses". Very cool
application of the CASE facility. Thanks again.|||JJA (johna@.cbmiweb.com) writes:
> Thanks very much for this neat suggestion. It is exactly what I hoped
> for and I can implement this a stored procedure with a couple of
> parameters. I will provide a little interface where the analyst can
> launch the sproc and see if there are any "near-misses". Very cool
> application of the CASE facility. Thanks again.

Glad to hear that the idea was useful to use. Whether it suffices remains
to see. As I said that fuzzy-logic stuff is horrible.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to find transposed data and near misses

I would like some advice on a data and query problem I face. I have a
data table with a "raw key" value which is not guaranteed to be valid
at its source. Normally, this value will be 9 numeric digits and map to
a "names" table where the entity is given assigned an "official name".
My problem is that I'd like to be able to identify data values that are
"close" to being "correct". For example, in the case of a
nine digit number such as 077467881, I'd like to be able to identify
rows with values close to this raw string. That is, if
there were a row with a value for this column that was "off" by say, a
transposed single digit (such as 077647881 in this example)
I would like to find a query to locate the "close candidates" in a
result set. If I can find rows having a raw key
value that is close to a "good key" then I can allow my user to use
other criteria to possibly assign the "close key" as
an alternate or alias of the official key. Here is part of my schema:
CREATE TABLE MYData (
StateCD char (2) NOT NULL ,
CountyCD char (3) NOT NULL ,
MYID int NULL ,
RawNumString varchar(9) NULL ,
SaleMnYear datetime NOT NULL ,
NumberWidgets int NOT NULL ,
)
CREATE TABLE MYNames (
MYID int IDENTITY (1, 1) NOT NULL ,
OfficialName varchar (70) NOT NULL ,
CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
(
MYID
)
)
CREATE TABLE MYAltID (
RawNumString varchar (9) NOT NULL ,
MYID int NOT NULL ,
CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
(
RawNumString
) ,
CONSTRAINT FK_HasName FOREIGN KEY
(
MYID
) REFERENCES MYNames (
MYID
)
)
So, how to generalize something like:
SELECT * FROM MYData WHERE RawNumString = '077467881'
OR RawNumString = '077647881'For what it's worth...
The LIKE operator can perform several forms of wildcard comparisons against
2 strings. For example:
if '90120' like '9_120' print 'Yes' else print 'No'
if '90120' like '9012[0..9]' print 'Yes' else print 'No'
if '90120' like '*0120' print 'Yes' else print 'No'
Yes
Yes
Yes
The SoundEx function returns a checksum for a character string, but not
numbers. It basically disregards vowels and double letters and returns a 4
char result. For example:
print soundex('Robert')
print soundex('Roberto')
print soundex('Rabertie')
print soundex('Rabbit')
print soundex('Rob')
R163
R163
R163
R130
R100
These can be included in a where clause. For example:
SELECT * FROM MYData WHERE RawNumString like '*7746*'
SELECT * FROM MYData WHERE SoundEx(RawName) = SoundEx('Francesco')
Keep in mind that performing like or soundex comparisons do not take
advantage of indexes, so performance could be a problem on a large table.
"JJA" <johna@.cbmiweb.com> wrote in message
news:1117641848.807351.148700@.g47g2000cwa.googlegroups.com...
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
> CREATE TABLE MYData (
> StateCD char (2) NOT NULL ,
> CountyCD char (3) NOT NULL ,
> MYID int NULL ,
> RawNumString varchar(9) NULL ,
> SaleMnYear datetime NOT NULL ,
> NumberWidgets int NOT NULL ,
> )
> CREATE TABLE MYNames (
> MYID int IDENTITY (1, 1) NOT NULL ,
> OfficialName varchar (70) NOT NULL ,
> CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
> (
> MYID
> )
> )
> CREATE TABLE MYAltID (
> RawNumString varchar (9) NOT NULL ,
> MYID int NOT NULL ,
> CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
> (
> RawNumString
> ) ,
> CONSTRAINT FK_HasName FOREIGN KEY
> (
> MYID
> ) REFERENCES MYNames (
> MYID
> )
> )
> So, how to generalize something like:
> SELECT * FROM MYData WHERE RawNumString = '077467881'
> OR RawNumString = '077647881'
>|||[posted and mailed, please reply in ews]
JJA (johna@.cbmiweb.com) writes:
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
Fuzzy logic is not for the faint of heart, and it's definitely not my
area of expertise.
Assuming that you always have nine digits, one approach is compare
character by character and if 7 or more match, count this as a possible
match:
SELECT *
FROM tbl
WHERE CASE WHEN substring(col, 1, 1) = substring(@.val, 1, 1)
THEN 1 ELSE 0
END +
CASE WHEN substring(col, 2, 1) = substring(@.val, 2, 1)
THEN 1 ELSE 0
END +
..
CASE WHEN substring(col, 9, 1) = substring(@.val, 9, 1)
THEN 1 ELSE 0
END >= 7
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you ever worked with check digits before? They can prevent errors
in data entry instead of trying to patch them after the fact. The idea
of keeping an invalid key does not sound like a good design.|||Yes, I know this is not good design but we are getting a raw data file
from another organization and we have no control over their practices.
Most occurrences of this number are "valid" but it is clear from
looking at the data that there is no validation at the source. The
nature of the data is such that if we can identify 7 or 8 bytes of data
as being the same as another 9 byte and valid "key", we could assume
the key could be improved to point at the same 9 byte valid entity. So,
I thought I'd run this notion past the world of experts for some ideas.|||Thanks very much for this neat suggestion. It is exactly what I hoped
for and I can implement this a stored procedure with a couple of
parameters. I will provide a little interface where the analyst can
launch the sproc and see if there are any "near-misses". Very
application of the CASE facility. Thanks again.|||JJA (johna@.cbmiweb.com) writes:
> Thanks very much for this neat suggestion. It is exactly what I hoped
> for and I can implement this a stored procedure with a couple of
> parameters. I will provide a little interface where the analyst can
> launch the sproc and see if there are any "near-misses". Very
> application of the CASE facility. Thanks again.
Glad to hear that the idea was useful to use. Whether it suffices remains
to see. As I said that fuzzy-logic stuff is horrible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to find the table used in Stored Procedure By Query

Hi,

I need to a find table which is used by list of stored procedures.

Can you please send me the query which is used?

Thanks and Regards

Abdul M.G

There is no built-in function in SQL Server that can do this. You need to search sysobjects table for matching strings.

Here's a piece of code I found that will list all stored procedures that reference a certain table.

SELECT DISTINCT so.name FROMsyscomments scINNERJOINsysobjects soon sc.id=so.idWHERE sc.textLIKE'%tablename%'

[Original source here]

|||

A much simpler way is to get the output of the sp_depends system stored procedure. This will give you any tables, views, stored procedures, user-defined functions or triggers used by the given stored procedure. Usage is sp_depends 'YourSPName'. Required tables will have a value of 'user table' in the type field in the output.