Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

I know these are just statistics placeholders, but I am wondering if
they might be a good hint into what fields should be indexed. Any
comments?
Example:
_WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
_WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
_WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?Hi Joshua,
Auto generated statistics on a column are indeed a good indicator that the
column might have to be indexed. Keep in mind though that statistics are
created the first time the column is accessed as part of a where clause,
join, group by, order by etc, and that that may also be the last time. In
other words, check that the column is indeed used more or less regularly
before you start creating indexes everywhere.
Jacco Schalkwijk
SQL Server MVP
"Joshua Guttman" <joshuaguttman@.excite.com> wrote in message
news:5a7a8917.0401291329.57833b2@.posting.google.com...

quote:

---

> I know these are just statistics placeholders, but I am wondering if
> they might be a good hint into what fields should be indexed. Any
> comments?
> Example:
> _WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
> _WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
> _WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?

---

Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

I know these are just statistics placeholders, but I am wondering if
they might be a good hint into what fields should be indexed. Any
comments?
Example:
_WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
_WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
_WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?Hi Joshua,
Auto generated statistics on a column are indeed a good indicator that the
column might have to be indexed. Keep in mind though that statistics are
created the first time the column is accessed as part of a where clause,
join, group by, order by etc, and that that may also be the last time. In
other words, check that the column is indeed used more or less regularly
before you start creating indexes everywhere.
--
Jacco Schalkwijk
SQL Server MVP
"Joshua Guttman" <joshuaguttman@.excite.com> wrote in message
news:5a7a8917.0401291329.57833b2@.posting.google.com...
> I know these are just statistics placeholders, but I am wondering if
> they might be a good hint into what fields should be indexed. Any
> comments?
> Example:
> _WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
> _WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
> _WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys

appending a temporary table to another temporary table

I need to append one table to another. The fields/columns headings are
identical in both tables. How do I append one to the other using SQL (in
Query Analyzer)?Hi Mitch
Try something like this:
INSERT INTO #temptable1
SELECT * FROM #temptable2
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:A0D31075-F452-4DF5-B78A-AA9482992986@.microsoft.com...
>I need to append one table to another. The fields/columns headings are
> identical in both tables. How do I append one to the other using SQL (in
> Query Analyzer)?
>|||insert into tableA
select * from TableB
http://sqlservercode.blogspot.com/

Append Views

My Problem is

I have 2 views --> 2 Databases (2 Products) but there are same fields (Same structure)

and I have to created the report by Crystal Reports to compare the Quatity of all product in my Company

So how Can I combine them (2 views with the same recoed but not the same data)

thanks for helping me

Kate

You can try Linked Servers if your databases are on different servers or on different instances.

If they are on the same instance then you can use

select * from DatabaseName.dbo.TableName
OR
select * from DatabaseName..TableName

Eralper

Append Counter variable to field name

Hi all,

I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!

Here's the proc:

WHILE @.Counter < 4
BEGIN

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @.ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @.DateFrom) AND (tbl_SurgerySlot.StartTime < @.DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)

SET @.FieldName = 'Days' + CONVERT(VARCHAR(20),@.Counter)

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

SET @.DateTo = DATEADD(Day,1,@.DateTo)

--Increment the loop counter
SET @.Counter = @.Counter + 1

When I run the above the follwoing message is displayed:

Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.

The object IS valid so I'm lost...Try to use object owner (object_owner.table_name)|||Instead of :

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

you could use exec:

exec('insert into ...'+ @.FieldName+') ...'|||OK, thanks, I'll give that a go...|||You can turn the whole thing in to a set based solution..

Also, are you sure that the query will return 1 row...|||Hi Bret,

Not sure what u mean by a Set based solution. I've just realised that the way I'm doing this won't work anyway cos' every time I use the INSERT statement it will obviously insert a new row, which I don't want it to do. I want the code to fill up the row with firgures for each day e.g.

Row1 5, 25,6

At the mo' it will do:

Row1 5,
Row2 ,25,
Row3 , , 6

Thought I could maybe store the data in an Array before committing it to the DB but have found T-SQL doesn't support this! Any ideas...|||The are no arrays in sql server...

I guess you could call a table like an array...

If you have sql server 2000 you can use table varialbles...

And I'm kinda of lost (so what else in new) with your example

Can you tell us, in business terms, what you're trying to do?|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)

Thanks for the chuckle...

Lots of time sql server will through an erroneous error...

BUT...your process needs to be changed...

If you ever figure out what's suppose to happen, tell use and post the ddl of the tables, some sample data with dml statements and expected results..

good luck...

Append / Insert Into Question

When appending data from one table to another...
If the structure is the same,
do you need to list all fields in the query
insert into t1 ( f1, f2, f3 etc..)
Select f1, f2, f3 etc from t2
Thanks in advance,
Bob.John 3:16 wrote:
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
Actually you don't have to but it's silly not to. It is valid syntax to
leave out the column list in an INSERT but if you do that SQL will
match the target columns by their relative positions, not by name. Your
code may be less reliable and certainly harder to maintain and support
if you don't reference columns by name. The wisest policy is to list
the column names every time.
You can save yourself some typing by dragging the column lists from the
Object Browser in Query Analyzer.
David Portas
SQL Server MVP
--|||Works for me:
insert t1 select * from t2
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||Nope - but it's good practice at any time.
For a shortcut to get started, in Query Analyzer's Object Browser, you
can right-click \ Script object to [desired target] As \ Insert.
John 3:16 wrote:
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||...one other thing.
This is maybe a good idea for a one time thing.
I would not use this method in production code.
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||Thanks David.
..Like you said, the time savings doesn't justify
future potential problems resulting from source or target changes.
Thanks for the reply,
Bob.

> Actually you don't have to but it's silly not to. It is valid syntax to
> leave out the column list in an INSERT but if you do that SQL will
> match the target columns by their relative positions, not by name. Your
> code may be less reliable and certainly harder to maintain and support
> if you don't reference columns by name. The wisest policy is to list
> the column names every time.
> You can save yourself some typing by dragging the column lists from the
> Object Browser in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks Raymond
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23gnyFTq9FHA.472@.TK2MSFTNGP15.phx.gbl...
> ...one other thing.
> This is maybe a good idea for a one time thing.
> I would not use this method in production code.
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Trey.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eEBw2Sq9FHA.3132@.TK2MSFTNGP12.phx.gbl...
> Nope - but it's good practice at any time.
> For a shortcut to get started, in Query Analyzer's Object Browser, you can
> right-click \ Script object to [desired target] As \ Insert.
>
> John 3:16 wrote:

anyway to check if a text field is blank (not null but just a empty string ) ?

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:

IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))

Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanksif exists (select 1 from XATPoDetail x inner join inserted i on x.ReqNbr=i.ReqNbr where cast(Note as varchar(8000)) = '')|||if exists (select 1 from XATPoDetail x inner join inserted i on x.ReqNbr=i.ReqNbr where cast(Note as varchar(8000)) = '')
wow thank you so much!!! ive been messing w/ this all day!

i didn't think of that at all.. in my trigger i have a part where i need to copy a text field from one table to another and i had to use table aliases as well.. is this the case all the time that whenever i'm using the text data type and comparing it w/ something else i have to use table aliases?

also, what does "select 1" mean? does that mean it will only select 1 field even if the query returns more than 1 row? (my query would return 1 row all the time becuase of the reqnbr constraint)

one last thing.. when casting the Text data type and casting it as a varchar, does varchar(8000) mean 8000 would be the max length of the varchar?

thanks a lot, i really appreciate the help :D|||1. When comparing TEXT datatype you don't need to use table alias. I do it to shorten the statement. You can write it like this and it will work the same:

if exists (select 1 from XATPoDetail inner join inserted on XATPoDetail.ReqNbr=inserted.ReqNbr where cast(Note as varchar(8000)) = '')

2. "select 1" can also be rewritten as "select 'OK'" or "select *" etc. It really doesn't matter what you SELECT inside IF EXISTS (...) construct, as long as the SELECT returns non-empty result set IF EXISTS (...) will evaluate to TRUE.

3. CAST(<text_field> as varchar(8000)) will take the first 8000 bytes of the value stored in the TEXT field and convert them to VARCHAR datatype.

Anything Like a <br /> Tag?

I am trying to place two fields in one column to save a little space.

=Fields!username.Value & "<br />" & Fields!notes.Value

However, the br tag doesn't work. Does XML permit some sort of text wrapping? If so, how can I accomplish this?

Try VbCrLF.

Thats vb's interpretation of new line character.

|||

Thanks for the quick response. That did it.

Anyone who can help me the solve the strange problem

Hi,

i encountered a very strange problem when using SSIS, the oledb source convert some fields in retrieving the data from oracle database,the datatype of the target field is NUMBER(5,1), and all the data retrieved from it have the uniform 'x.y', and remained same when i previewed the data in the oledb source,but the output of the data changed to 'x', the data converted to integer, anyone knows how to solve the problem please let me know, thanks a lot.

Where is this output that you are looking at ? The output of the OLE DB Source?

If so, it could be that the externl columns have been mapped to the wrong pipeline types. Check this out in the OLE DB Source Advanced Editor.

-Jamie

|||Do you have anything between the source and destination that might be converting the data?|||

Jamie Thomson

thanks for your kindly help, the problem i mentioned above has been solved.

|||

icewill wrote:

Jamie Thomson

thanks for your kindly help, the problem i mentioned above has been solved.

Icewill,
I 've the same problem, question how did you solved it?
Thanks in advance.
Olaf

Anyone who can help me the solve the strange problem

Hi,

i encountered a very strange problem when using SSIS, the oledb source convert some fields in retrieving the data from oracle database,the datatype of the target field is NUMBER(5,1), and all the data retrieved from it have the uniform 'x.y', and remained same when i previewed the data in the oledb source,but the output of the data changed to 'x', the data converted to integer, anyone knows how to solve the problem please let me know, thanks a lot.

Where is this output that you are looking at ? The output of the OLE DB Source?

If so, it could be that the externl columns have been mapped to the wrong pipeline types. Check this out in the OLE DB Source Advanced Editor.

-Jamie

|||Do you have anything between the source and destination that might be converting the data?|||

Jamie Thomson

thanks for your kindly help, the problem i mentioned above has been solved.

|||

icewill wrote:

Jamie Thomson

thanks for your kindly help, the problem i mentioned above has been solved.

Icewill,
I 've the same problem, question how did you solved it?
Thanks in advance.
Olaf