October 19, 2010 at 8:57 am
i have set up a data collection to pick up heavy queries on the system, now i want to set up full-text search on the snapshots.trace_data table to query the TextData column better
the problem is this snapshots.trace_data doesn't have a unique column, so i made a computed one (i once added a identity column but that resulted in way too many duplicated columns. i guess data collection does not like identity) composed of EventSequence and the Snapshot_id as follows
ALTER TABLE snapshots.trace_data add UniqueCol as (CAST(SNAPSHOT_ID AS VARCHAR) + '-' + CAST(EVENTSEQUENCE AS VARCHAR)) PERSISTED
and created the index
CREATE UNIQUE INDEX IX_UNIQUE ON snapshots.trace_data (UniqueCol)
along the way i must have done something wrong because i still can't define the full-text index. i get the error
'IX_UNIQUE' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key. (Microsoft SQL Server, Error: 7653)
anyone with full-text knowledge care to shed some light on this?
--
Thiago Dantas
@DantHimself
October 19, 2010 at 9:19 am
Hi,
This is probably due to your use of a computed column. From BOL:
The KEY INDEX must be a unique, single-key, non-nullable column
Your computed column is non-deterministic (even though it's persisted), i.e. a future record could be added that would compute to NULL.
Can you not just add an incrementing number column?
Regards, Iain
October 19, 2010 at 9:49 am
irobertson (10/19/2010)
Hi,This is probably due to your use of a computed column. From BOL:
The KEY INDEX must be a unique, single-key, non-nullable column
Your computed column is non-deterministic (even though it's persisted), i.e. a future record could be added that would compute to NULL.
Can you not just add an incrementing number column?
Regards, Iain
i could and i did try it before, but i ended up getting way too many duplicate rows (5-10 duplicates per row) which really screwed things up
could i fix this by wrapping the casts around ISNULL ?
--
Thiago Dantas
@DantHimself
October 19, 2010 at 9:58 am
I don't think so. Because SQL can't know the content of the two fields in your computed column until it computes them, the expression will always be non-deterministic, persisted or not (I think, happy to be corrected if someone else knows better).
When you tried adding a numeric column, How did you end up with dupes if the added column was identity?
October 19, 2010 at 10:12 am
irobertson (10/19/2010)
I don't think so. Because SQL can't know the content of the two fields in your computed column until it computes them, the expression will always be non-deterministic, persisted or not (I think, happy to be corrected if someone else knows better).When you tried adding a numeric column, How did you end up with dupes if the added column was identity?
something with how data collector uploads the data into the table, couldn't pin-point the exact reason
--
Thiago Dantas
@DantHimself
October 19, 2010 at 10:33 am
see below
October 19, 2010 at 10:33 am
Had a quick look on msdn, Microsoft suggest not touching the tables at all:
Do not modify the metadata of management data warehouse tables unless you are adding a new collector type.
Do not directly modify the data in the management data warehouse. Changing the data that you have collected invalidates the legitimacy of the collected data.
Instead of directly using the tables, use the documented stored procedures and functions that are provided with the data collector to access instance and application data. The table names and definitions can change, do change when you update the application, and might change in future releases.
See: http://msdn.microsoft.com/en-us/library/bb677306.aspx
Maybe you could ship the data to a processing table and then query it from there?
October 19, 2010 at 11:23 am
ALTER TABLE snapshots.trace_data ADD UniqueCol AS (CAST(SNAPSHOT_ID AS VARCHAR) + '-' + CAST(EVENTSEQUENCE AS VARCHAR)) PERSISTED NOT NULL
adding the column like this made it work.
since its a computed column i don't see much harm.
if it indeed does change ill adapt, no biggie. we just use this sporadically to find bad queries now and then
--
Thiago Dantas
@DantHimself
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply