I am performing a Select Into from a #table into a real table that has a surrogate key. If this is in a transaction (or not in one) am I guaranteed that the records inserted will be sequential surrogate key ids?
Select * into REALTABLE from MYPOUNDTABLE --40 rows
Can I assume that if the first one inserted is id 32 that the last one is 72?
I am performing a Select Into from a #table into a real table that has a surrogate key. If this is in a transaction (or not in one) am I guaranteed that the records inserted will be sequential surrogate key ids?
Select * into REALTABLE from MYPOUNDTABLE --40 rows
Can I assume that if the first one inserted is id 32 that the last one is 72?
Can i write a Stored Procedure so as to enter the data in the tables in bulk. Say we have an application through which we can enter the data in some 4-5 tables. For actual testing we need to have a large amount of data being populated in all these tables and its not feasible to do this through the application in a short time. Is there any way out for such a situation so that we can enter the valid data with different confitions in the tables ?
Thanx,
i have a catalog and add directory which has 10,000 documents and all are index but if i add 1000 documents to that directory and i don't want those 1000 documents to be indexed. i want only previous 10,000 index document and don't want to new document to be indexed. is there any way can stop the new document to be indexed, please let me it's bit urgent.
Thanking you in anticipation
hi!
when i read some reference books about the SQL7.0, i often met 'surrogate key'. what's the surrogate key? what's its funtion? could you give me a good example?
thanks very much!
Hi gurus
can any one tell me what is the best way to use surrogate key (except uniqueidentifier datatype)? how can I use with TSQL?
I have two DropDownList controls, ddlGroup and ddlLocation.
The contents of ddlLocation will be determined by the selection of ddlGroup. A better explanation is as follows:
ddlGroup =
ADVANCED DEVELOPMENT
DESIGN
HEAD TEST
INK R&D
JETTING
PROCESS
RELIABILITY
The SQL query that shows my logic is:
IF @Group = 'ADVANCED DEVELOPMENT' SELECT Location FROM tblLocations WHERE Location like 'AD%'ELSE IF @Group = 'DESIGN' SELECT Location FROM tblLocations WHERE Location like 'DE%'ELSE IF @Group = 'HEAD TEST' SELECT Location FROM tblLocations WHERE Location like 'HT%'ELSE IF @Group = 'INK R&D' SELECT Location FROM tblLocations WHERE Location like 'INK%'ELSE IF @Group = 'JETTING' SELECT Location FROM tblLocations WHERE Location like 'JT%'ELSE IF @Group = 'PROCESS' SELECT Location FROM tblLocations WHERE Location like 'PR%'ELSE IF @Group = 'RELIABILITY' SELECT Location FROM tblLocations WHERE Location like 'RL%'
I need to define the content of ddlLocation after a selection is made in ddlGroup; how can I accomplish this using Visual Web Developer 2008 Express Edition?
I'm using Full-text in various databases on my servers (SQL2005 on W2K3). On a few databases the Full-text population ends with the error:
'Error '0x80030050' occurred during full-text index population for table or indexed view '[database].[dbo].[table]' (table or indexed view ID '1714105147', database ID '9'), full-text key value 0x00015EE1. Failed to index the row.'
The next log-line lets me know the name of the dll that caused the problem:
The component 'offfilt.dll' reported error while indexing. Component path 'C:WINDOWSsystem32offfilt.dll'.
There's one solution I read about, but that one is not the case here. That sollution states that this problem occurs when de datatype is not the same as the filetype (e.g. datatype is pdf, documenttype is doc).
What can be the problem here?
Thanks.
Hi,
I have a full text index created on a table with PK, text column and timestamp column. The table has 10 million rows. I tried one time full population and CPU spiked so after couple of hours i stopped full population.
Now since i have a timestamp column in the table I want to do a incremental population.
But when I run a select
SELECT * FROM sys.fulltext_indexes
The incremental_timestamp column is showing value 0x0000000000000000
How do I find how long will it take for incremental population to complete?
Thanks,
Yogesh
The orininal design of my db (part of it...) is the following
A JOB has a Number and a Description.
Each JOB can have one or two TASKS (min one, max two). Each TASK is identified by the JOB it belongs to and an Index (unique only for the same JOB).
Each TASK has one an only one set of INFO1, one and only one set of INFO2, one and only one set of INFO3 etc.
A: JOB (JobNum [PK], JobDescription, ...)
B: TASK (JobNum [PK] [FKa], Index [PK], TaskDescription, ...)
C: INFO1 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
D: INFO2 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
(There is a reason to keep INFO1, 2 and 3 separate, because eachof them will be linked to different table. This might influence the answer to my real question.)
First of all, I wouldn't add any surrogate key for TASK, not to loose the logic behind; plus I'd put an ined on JonMum only, being Index equal to 1 or 2 only, so not selective.
The real question is about INFO1 (and 2, 3 etc.) table: should I leave JobNum and Index as PK (consider that the PK of INFo1 will be used as FK for another table), or should I use a surrogate key, like for eaxmple
C: INFO1 (Info1ID [PK], JobNum [FKb], Index [FKb], ...)
I don't really like this solution. Actually I'd prefer the following
C: INFO1 (Info1ID [PK], ...)
where Info1ID = JobNum + Index (+ = string concatenation).
Any suggestion?
Thanks
Hi,
How to create surrogate key in a dimension table?
What transformations can be used to create it?
Hi, I'm trying to use the SK script from Donald Farmers book but the code isn't accepted
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim CurrentKey As Integer
Public Overrides Sub PreExecute()
CurrentKey = CInt(Me.Variables.FILCodesSK)
End Sub
Public Overrides Sub Input_ProcessInputRow(ByVal Row As Input0Buffer)
CurrentKey += 1
Row.SurrogateKey = CurrentKey
End Sub
End Class
There is a problem with the use of the overrides on the Input_ProcessInputRow sub should this be renamed?
Cheers, Al
hello,
I'm looking for a way to populate my index on insertion but not on updates.
I tried each possible value for CHANGE_TRACKING MANUAL|AUTO|OFF and it automatically takes every changes that have been made before in account. is there a way to "flag" the rows that I don't want the server to re-index (i.e. updated rows).
Thanks for reading, any help is welcome.
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
We want to add population data in fact table.
I have used two matrix in one of my reports. One matrix is right above other. Both matrix's columns are allocated for month name. I.e there are 12 columns for each month of the year for each matrix.
column name of the second matrix was hidden. so end user can see only first matrix column name and corresponding data in each matrix.
But the problem is now, when there is no data for perticular month in first matrix, thats month's column does not appear at all.
Lets say there is no data for November in first matrix. so Novem column is missing in first mtrix now. but still Novem column is shown on the second matrix as it has some data, although column name is not shown. I wonder how I can show all the columns of both matrix regardless of population of data.
Thanks
Hi,
I am working with Naive Bayesian Algorithm and I do not understand why if the original table has 8.000 rcds the size of the entire population shown in Hystogram viewer is only of 2000.
Do you have any answer?
Hey All, I'm trying to decide what's the 'best' to use. I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one. I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key. Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys. For example I'm doing an authentication system from scratch and am looking at the User table. Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used. I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key. However it still seems sort of sloppy, for lack of a better word, to me. Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables. And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.
View 2 Replies View RelatedHello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...
If I have a separate table NextKey (NextKey int) with one row that I update as follows...
declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)
are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....
Thanks
John
My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...
Consider the following...
A JOB describes the processment of a document.
Each document can exist in two versions: English and French.
A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version.
So we have the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).
Each TASK may require a TRANSLATION sub_task.
Each TASK may require a TYPING sub_task.
Each TASK may require a DISTRIBUTION sub_task.
For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.
That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION.
So we have the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.
To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others.
So we have...
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)
that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.
I wish I could send a pic of the ER diagram...
Maybe there is another and better way to model this: if so, any suggestion?
Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:
C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)
this will "improve" the ASSIGNMENTs tables:
F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)
I could even go further using a surrogate key even for TASK, which leads me to the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (TaskID [PK], JobNum [FKa], Version , Priority, ...)
C: TRANSLATION (TaskID [PK] [FKb], DueDate, ...)
D: TYPING (TaskID [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (TaskID [PK] [FKb], Copies, ...)
F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)
I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.
{CREATE TABLEs and INSERTs follow...}Gents,I have a main table that is in ONE-MANY with many other tables. For example, ifthe main table is named A, there are these realtionships:A-->BA-->CA-->DA-->EWith one field in Common (Person). The tables B, C, D and E are History tables,with Start and End dates. Each person has a Program history (table B, ie), anExperience history (table C, ie), and so on...many differernt types ofhistories, and it may grow from here....table F, G, etc.The included CREATE TABLEs and INSERTs contain tables A, B and C.The problem: Each tblCase (table A) record has a date. When joining all of thehistory tables to tblCase on Person, obviously you get a cross-product of eachhistory unless you specify a WHERE clause that extracts one single record fromeach of the histories (duh...that's the point...to extract a single record fromeach history, because there can only be one value in effect at the time of theCase.)QUESTION: From a performance standpoint, would it behoove me to maintain thesurrogate ***HistoryID from each history table in tblCase, or, assuming theindexes are set up properly, would a WHERE condition for each history besufficient? For example, the following select works as expected:SELECT CasePerson, CaseDate, ProCode, ExpYearFROM tblExperienceHistory INNER JOIN (tblCase INNER JOIN tblProgramHistory ONtblCase.CasePerson = tblProgramHistory.ProPerson) ON tblCase.CasePerson =tblExperienceHistory.ExpPersonWHERE CaseDate BETWEEN ProStartDate and ProEndDateAND CaseDate BETWEEN ExpStartDate and ExpEndDateIt extracts the single record from each history for each person for each case.But I'm afraid of performace with such a scenario.Instead, I could store each ***HistoryID in the table tblCase, and then justjoin on that...no WHERE needed. But the trade-off is that I'd have to buildprocesses to maintain that. ("Hey, when you insert a record into tblCase, makesure to go get each HistoryID from the History tables!" or "If the user changesthe date ranges in one of histories, make sure to update tblCase to match thenew historyID!")Maybe a clustered index on each ***History table on Person/StartDate combinedwith the WHERE clause should perform as well as a real JOIN on surrogateintegers.It seems cheesey to have to resort to surrogate IDs...but the performanceincrease might be worth it. Also, if I go that route, whenever I add a newhistory table, I'd have to change the design of tblCase AND any SPs thatreference it. With the WHERE solution, I'd only have to change the SPs.Comments are welcome! (tblCase grows at 250,000 records per year; the historytables will increase about 1000 records per year)DCMFANCREATE TABLE [dbo].[tblCase] ([CaseID] [char] (5) CONSTRAINT [PK_tblCase] PRIMARY KEY CLUSTERED NOT NULL ,[CaseDate] [smalldatetime] NOT NULL ,[CasePerson] [char] (5) NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblExperienceHistory] ([ExperienceHistID] [int] IDENTITY (1, 1) NOT NULL ,[ExpPerson] [char] (5) NOT NULL ,[ExpStartDate] [smalldatetime] NOT NULL ,[ExpEndDate] [smalldatetime] NOT NULL ,[ExpYear] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblProgramHistory] ([ProgramHistID] [int] IDENTITY (1, 1) NOT NULL ,[ProPerson] [char] (5) NOT NULL ,[ProStartDate] [smalldatetime] NOT NULL ,[ProEndDate] [smalldatetime] NOT NULL ,[ProCode] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('12345', '3/1/03', '00000')INSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('A1G34', '4/23/03', '00001')INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '1/1/03', '5/19/03', 1)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '5/20/03', '12/31/03', 2)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00001', '4/20/03', '11/1/03', 0)INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '2/1/03', '9/30/03', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '10/1/03', '5/1/04', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00001', '1/1/03', '12/31/03', '55555')
View 4 Replies View RelatedI have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.There must be a better way of doing this and I don't have access to any DQS services.
Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance!
John
Hi,
The scenario is the data comes from various sources and its staged into staging database. From this staging database it goes into data warehouse database. Everyday this staging database is truncated and repopulated from various sources.
I've a dimension table called DimCustomers which consists of around 300,000 rows and has lots of different types of SCD columns. It takes around 4-5 hours to load data from staging to this dimension table. Currently I'm using a For Loop container which uses a store proc to extract 15000 rows each time and populate my dimension tables. First couple of loops it goes off quickly but as and when the number reaches half of the count it slows down and hence it takes around 4-5 hours to load data.
What would be the best approach to populate this kind of dimension table.
Thanks
I have two tables:
countries(country_id integer, country_name string)
authors(auth_id integer, country_id integer, auth_name string)
...Where "country_id" in the authors table refers to the same country_id in the countries table.
I want a stored procedure to handle the insertion of new rows in the authors table. There are two methods of doing it:
1) CREATE PROCEDURE addAuthor( authorName, countryId )
And
2) CREATE PROCEDURE addAuthor( authorName, countryName )
Now, I like #1 because the implementation is simple -- the calling code simply passes an author name, and a country id and an INSERT INTO statement is called with those parameters
INSERT INTO authors( @authorName, @countryId )
I like #1, because it hides the surrogate "id" key from the application calling code. But on the downside, it has more overhead work, because you have to first a) verify a country with that name exists, and b) select that id into a variable.
DECLARE id INT;
IF EXISTS (select * from countries where country_id = @countryId ) THEN
SELECT country_id INTO id FROM countries WHERE country_name = @countryName;
END IF;
(Sorry I may have the SQL syntax wrong up there, but I was just trying to demonstrate the extra overhead involved).
Which approach do you guys think is better?
I am in the process of building a fact table in a staging area. The data in the host system has numerous composite keys, so I have replaced all the composite keys in the dimensions with surrogate keys (integer) which are generated using an identity at load time. When I load the staging (fact) table, I have set the default value of all the foreign keys to 0. What I must do now is update all the foreign key values with the surrogate key values from the dimensions. I'm using an update command and the original gid values from the source system in the where clause...i.e.
UPDATE X
SET x.key_1 = y.key_1
FROM TableA X WITH (NOLOCK)
INNER JOIN TableB Y WITH (NOLOCK)
ON x.org_id = y.org_id
AND x.bus_id = y.bus_id
AND x.prov_gid = y.prov_gid
AND x.log_gid = y.loc_gid;
This seems to work fine for most tables. However, I am now trying to update a table that has over 10 million rows and approximately 30 foreign keys. The script runs for hours. I ususally stop it after about 8 hours when it still hasn't completed. Since the keys are dynamic and they could possibly change during each load process, I can't add them during the load process.
Is there a better way to update these keys. I need to regenerate the fact tables every night and taking this much time to reload a fact table is just not practicle. I've indexed the alternate keys on all the dimensions and have also indexed the gids on the target fact table. Am I doing something wrong? Have I over indexed the target table? Please help! Thanks Jerry
Hi,
I have a dimension called 'Caller Type' with the following attributes:
CallerTypeKey ---- surrogate key
CallerTypeID
CallerTypeDesc
CreatedByKey ---- foreign surrogate key from User Dimension
I used Script Task to get the last used key and increment it so i can use it for new records in my dimension. however, my dimension is linked to a User Dimension and I need the surrogate key of that once I insert the new record to CallerType Dimension.
How would I do that?
cherriesh
This is the code iam using to get the incremental surrogate keys:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
'Declare a variable scoped to class ScriptMain
Dim counter As Integer
Public Sub New() 'This method gets called only once per execution
'Initialise the variable
counter = 1093
End Sub
'This method gets called for each row in the InputBuffer
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
'Increment the variable
counter += 1
'Output the value of the variable
Row.instance = counter
End Sub
End Class
--'Instance' is my surrogate feild name
but iam getting an error saying that InputBuffer is not defined ..Any idea?
If I want to add two more incrementive fileds ,where i have to add it?
Sorry if it sounds silly ,iam very new to this scripting.
Thanks
Niru
I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.
How can I do that using SSIS package?
thanks,
Aref
I have several stage to star (i.e. moving data from a staging table through the key lookups into a fact table) ETL transformations in a single SSIS package. Each fact table has a different set of measures but the identical foreign key set, e.g. ConsultantKey, SubsidiaryKey, ContestKey, ContestParamKey and MonthKey.
Currently I have to replicate the key lookup (Surrogate Key Pipeline, or SKP) for each data flow. If I could cache each dimension one time in the package and reuse it for each stage to fact it would be much more efficient.
Is there a way for me to reuse a common data flow?
I have a table having 220 lakhs of records and one of the column is Full Text enabled.We have used ContainsTable() to search for data, but we are unable to get results as expected. so we done rebuild.During Index Rebuild, population is failed.I have found this error in error log and it is saying to do resume population.So I want to know how long it takes to complete Resume population process.
look at the below more details about FT Index table.
Row count - 22155112
Index space - 1,903.250 MB (1.9 GB)
Data space - 87,552.258 MB (87 GB)
sqlserver2008 R2
and the below query we have used
HTML Code:
SELECT Distinct top 50 cal.case_id,cal.cas_details
From g_case_action_log cal (READUNCOMMITTED)
inner join containstable(es.g_case_action_log, cas_details,
' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key]
Where cal.product_id = 38810 ORDER By cal.case_id DESC
This query is not going to search in recent inserted/updated rows. this is the actual issue we are facing.
how to fix this error and if population need to be resume, then how long takes to do resume population.
Error log:
full-text crawl logs for details.
2007-06-01 07:33:55.63 spid25s Error: 7683, Severity: 16, State: 1.
2007-06-01 07:33:55.63 spid25s Errors were encountered during full-text index population for table or indexed view '[XXXX].[dbo].[RECORDS]', database 'XXXX' (table or indexed view ID '738101670', database ID '17'). Please see full-text crawl logs for details.
2007-06-01 07:33:55.63 spid25s Changing the status to MERGE for full-text catalog "XXXX" (21) in database "XXX" (17). This is an informational message only. No user action is required.
This happens for every table that has membership in the full text storage, it is not specific to a database or column type
Scrawl Log:
2007-06-01 07:33:00.57 spid23s Informational: Full-text Full population initialized for table or indexed view '[XXXX].[dbo].[ATTACHMENTS]' (table or indexed view ID '517576882', database ID '17'). Population sub-tasks: 1.
2007-06-01 07:33:36.20 spid23s Error '0x80070003' occurred during full-text index population for table or indexed view '[XXXX].[dbo].[RECORDS]' (table or indexed view ID '738101670', database ID '17'), full-text key value 0x00002B59. Attempt will be made to reindex it.
2007-06-01 07:33:55.63 spid25s Informational: Full-text retry pass of Full population completed for table or indexed view '[XXXX].[dbo].[RECORDS]' (table or indexed view ID '738101670', database ID '17'). Number of retry documents processed: 31899. Number of documents failed: 31899.
2007-06-01 07:33:55.63 spid25s Changing the status to MERGE for full-text catalog "XXXX" (21) in database "XXXX" (17). This is an informational message only. No user action is required.
2007-06-01 07:33:56.59 spid23s Informational: Full-text Auto population initialized for table or indexed view '[XXX].[dbo].[RECORDS]' (table or indexed view ID '738101670', database ID '17'). Population sub-tasks: 1.
Thanks for you feedback.
I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
Thanks
I want to change the work table name to work_version2 and later drop the work table. First, I created the table (work_version2) along with the data structure seen below and later inserted data from the work table. As I tried to make workID a surrogate key in work_version2 using SSMS, I got the below error message when I try to save the changes. Is there a way to do this?
Saving changes is not permitted. The changes you have make requires the following tables to be dropped and re-created. You have either make changes to a table that cant't be recreated or enabled the option that prevent saving changes that requires the table to be recreated. Work_version2.
CREATE
TABLE WORK(
WorkID Int NOT
NULL IDENTITY (500,1),
Title Char(35) NOT
NULL,
Copy Char(12) NOT
[Code] ....