I have a table with three fields that are marked as indexed fields with unique keys. I want
to remove one of the fields. When I do this and hit the rebuild button -- I get an error, that
there is a duplicate field, so it will not just let me remove the one field and leave the other
two?? How can I get around this?
I have created a table from another table where I specified that one of the fields, an number field, is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.
Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?
In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?
Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
what do u think about a table that has 19 indexes and some of them are covering index?
View 3 Replies View Relatedhi all,
In the properties of the table in the MS Access there is an Indexed column. If someone would had set that to "Yes (Duplicates Ok)" how can one implement this to MSSQL???
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hi All,
I have over 100 views in a database. How can I check which view we used indexed view?
Thanks.
Hi All,
I have over 100 views in a database. I created non-clustered index on views. Now I need to check out which views have index.
How can I check which views we created as indexed views?
Thank you very much.
Hi
I am trying toe create an indexed view but cannot seem to get it right.
CREATE VIEW dbo.D_Policy_View with schemabinding
AS
SELECT Policy_ID, Environment_Code, CoB, Sub_CoB, Policy_No, Version_No
FROM dbo.D_Policy
WHERE (Policy_ID IN
(SELECT MAX(Policy_ID)
FROM dbo.d_Policy
GROUP BY Environment_Code, COB, Policy_No, SUB_COB))
I have read on BoL that MAX is not allowed but don't know of any other way to get the latest record??
please help :confused: :mad: :eek:
Hello,
I'm currently performance tuning a table with 100 million rows in it
(about 18 GB of data) and would like to know if -
1. Is the table too large to be performance tuned. Is it better to just
redesign the schema ?
2. Can using techniques as indexed views really help me with tuning such a table.
3. How long would it take to create a clustered, non clustered index on
a varchar column (for instance) on a table with 100 million rows ?
(i know this is a function of hardware as well - let's assume i'm using
afairly maxed out DL 360 - i.e. dual processor with 4 GB of memory)
Thank you very much
Alan
I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.
Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.
Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).
Hi,
I have a problem trying to create an indexed view on SQL Server 2000. There are multiple databases, one of which stores system wide data. I would like to create an indexed view on the system wide data for each of the site databases.
Is it possible to create an indexed view on data in another database?
Cheers
Hi,
I am trying to create an indexed view, but because I am using a MAX function, I get the error
Cannot create index on view "dbo.View" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.
Am totally stuck on how I can replace the MAX function.
Any help would be appreciated.
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
CREATE VIEW [dbo].[View]
WITH SCHEMABINDING
AS
SELECT TOP 100 PERCENT MAX(js_id) AS job_event, job_id
FROM dbo.JobEvent
GROUP BY job_id
ORDER BY job_event
GO
CREATE UNIQUE CLUSTERED INDEX IX_VMaxJobEvent ON View (job_id)
Thanks
snehalata writes "i create view as follows
CREATE VIEW Data
WITH SCHEMABINDING
AS
SELECT A.PartitionID,FundID,ReportDate,ForeignTaxWithheld,DomDividendIncome,RGainShortTerm,RGainLongTerm,NewIssueRGainShortTerm,
NewIssueRGainLongTerm,ChgUnrealizedGain,ReplaceTax,TotIncomeBefFee,TotalIncome,EndingNetCapital,EndingRedemptionUnits,
BeginRedemptionAmount,EndingRedemptionAmount,EndingUnits,InterestOverseas,ExpenseOverseas,OrdIncome,
ReallocationExpense,BeginRedemptionFee,EndingRedFee,BeginGrossCapital,EndingGrossCapital,GPFees,FixedExpense,MergerCost,
SellingCommission,GrossRoR,NAV,GAV,GPMgmtFee,IMMgmtFee,GPIncentivefee,IMIncentivefee,NetRoR,MonthCounter,
BegUnits,BegAddUnits,BegAddAmount,EndAddAmount,GrossRealizedGain,BrokerCommission,
NetRealizedGain,OperatingExpense,OffsellExp,OrgExp,USObligationIncome,FixIncomeIntrIncome,
CapitalGain,SellingFee,SellingMgmtFeeMidQtr,SyndicateCost,BeginNetCapital,DomesticDividendExp,FixedIncomeIntrExp
FROMdbo.vPart1A
LEFT JOIN dbo.vPart2B
ON A.PartitionID= B.PartitionID
then if i creat index as follows
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
create unique clustered index ind1 on Data(FundID,ReportDate)
it gives me following error
Cannot index the view 'MonthliesTest2.4.dbo.Data'. It contains one or more disallowed constructs.
why so?"
Dear experts,
I've been working for an ERP solutions, company, as a DBA....
we have around 1200 tables as wellas 650 views.....
we are not using clustered index on views.....
using the clustered index will boost the performance? and the ERP is web based application. so that modifications will be done on a regular basis....
is it good thing to implement clustered indexes on these views....
please guide me in this regard
thank you verymuch
Vinod
Even you learn 1%, Learn it with 100% confidence.
I am trying to index a large number of PDF files using SQL Server Full Text indexing, and am running into an issue where about 1% of the documents are not being indexed. I looked in the SQL Full Text logs and the following error appears thousands of times:
Error '0x80043651: msftesql should reprocess this document in an isolated fashion to confirm the error.' occurred during full-text index population for table or indexed view '[DocumentWarehouse].[dbo].[Document2006_tbl]' (table or indexed view ID '485576768', database ID '5'), full-text key value 0x00E32429. Attempt will be made to reindex it.
The component 'MSFTE.DLL' reported error while indexing. Component path 'Y:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnMSFTE.DLL'.
Occasionally this warning appears:
Warning: No appropriate filter was found during full-text index population for table or indexed view '[DocumentWarehouse].[dbo].[Document2006_tbl]' (table or indexed view ID '5', database ID '485576768'), full-text key value 0x00FE8A91. Some columns of the row were not indexed.
The problem isn€™t because certain PDF documents can€™t be indexed, because when you reinsert a doc that wasn€™t indexed, it does get indexed.
My version of SQL Server is 9.00.2153.00 running on Windows Server 2003.
what does it mean ? Why it is necessary to create/put it ? How do you use it in SQL 2005 or 2000 ?
How can (is full-text indexed) property TRUE in column properties in moment create table with right-click on tables and select NEW TABLE?
View 1 Replies View Relatedis there any work around this?
View 1 Replies View RelatedHi,
I have large table rrsn_security_t - more than half a million rows. I do a complete update on all the rows of the table using the following query.
UPDATE rrsn_security_t
set
cusip = b.fmr_cusip,
master_issuer_num = b.mstr_isr_cusip,
ticker = b.fmr_symb,
description = b.fmr_name,
prim_exchange_code = c.exchange_key,
shares_otstndng = d.amount,
iv_type = b.iv_typ,
active = case when b.deact_date is null then 'Y' else 'N' end
FROM rrsn_security_t a INNER JOIN
ref_security_t b ON
a.security_id = b.fmr_cusip LEFT OUTER JOIN
shares_outstanding_feed_t d ON
b.fmr_cusip = substring(d.fmr_cusip,1,9) AND
d.fmr_type = 'OUTS' LEFT OUTER JOIN
rrs_exchange_t c ON
b.dft_exch_cd = c.exchange_id
where b.fmr_cusip not in (select security_id from rrsn_scrty_ovrrd_in_effect_t)
This is a part of a daily batch load and a DTS package. The table has one clustered index and three non clustered indexes. Two of them are covering indexes. The update is on all the columns that have the non clustered indexes.
The problem is that when I run the update the transaction log runs to more than a GB of memory and alomost takes an hour to do so. Without the index it takes around 300 MB and 7 mins.
I am not confortable with the idea of dropping and recreating the index since it is not necessary in SQL Server 7.0, though it was the case in the previous versions of SQL Server.
Also the query plan with the indexes on shows that Table spool/Eager spool to optimize rewinds takes 50% of the query cost.
Could any one help me with how I should deal with this situation.
Thanks in advance
GV
Hello!
I need to insert 30 times daily 50000 rows into 13-month invoice big table tb_Invoce
The table has 15 columns.
4 of them are indexed (1 clustered)
The table is heavily queried.
I want to minimize insertion time and minimize time of table locked.
What is the best algorithm for inserting?
Thanks a lot
I had some issues yesterday with the fact that some of the tables I had indexed views for did not have a unique/clustered index. The tables had unique indexes and clustered indexes but not a unique/clustered index. What I was seeing were rows that should have been in the view, not showing up in a regular select but they would showup in a with noexpand hint.
To fix the problem I created a unique/clustered index on each of the underlying table but cannot find that requirement anywhere, is this a requirement and if so can someone tell me where to find it.
Thanks
How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!
I have an indexed view with a clustered index on my database........when I try to run and update statement agaisnt the table that is referenced in the view, I get one of the following errors:
Server: Msg 3624, Level 20, State 1, Line 1
Location: q:SPHINXNTDBMSstorengdrsinclude
ecord.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 414
Connection Broken
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 52
Process ID: 414
Connection Broken
any ideas?
Hi all,
i have a very general question about databases. What is the advantage and disadvantage of using a heavily indexed database?
The advantage i could think is that search operations will be fast. The disadvantage (according to me who is a newbie) is that the size of the database will increase.
My teacher however is not very happy with this answer and wants me to research more. Any help will be greatly appreciated.
Thanks
I created an indexed view in SQL 2000, and I expected to see the index created on the view referenced in the execution plan when I query the view. Instead, I see the index for the base table referenced in the execution plan. Why?
There are 6,000,000+ records in the base table, and the view only references 256 of these rows.
Here is some of the DDL if you need it:
CREATE TABLE [alarm_t] (
[ct_dtm] [datetime] NOT NULL ,
[dst_flg] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[stn_nm] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[alarm_txt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[utc_dtm] [datetime] NOT NULL ,
[create_utc_dtm] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [alarm_idx2] ON [dbo].[alarm_t]([ct_dtm], [stn_nm], [dst_flg]) ON [PRIMARY]
GO
create view dbo.alarm_Mapbd_v with schemabinding
as
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE[stn_nm]= 'Mapbd'
GO
create unique clustered index alarm_Mapbd_idx1 on dbo.alarm_Mapbd_v
( stn_nm, ct_dtm, dst_flg )
go
update statistics alarm_t
go
update statistics alarm_Mapbd_v
go
The following 2 queries have the exact same execution plan, both showing a cost of 50%. I expected to see the index created on the view referenced in the execution plan for the first query. Is the index created on the view being used?
selectstn_nm, ct_dtm, dst_flg
fromalarm_Mapbd_v
go
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE[stn_nm]= 'Mapbd'
go
Thanks for your assistance.
Tom
I have created a unique clustered index on a view.
The view does a GROUP BY on 3 of the columns and
uses the COUNT_BIG aggregate function.
I used the following SET commands before creating the view and the index:
SET ARITHABORTON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLSON
SET ANSI_PADDINGON
SET ANSI_WARNINGSON
SET NUMERIC_ROUNDABORTOFF
I can insert and delete rows from the base table, and the indexed view is updated fine.
However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:
Executed as user: NT AUTHORITYSYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Why am I getting this error?
The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.
Thanks,
Tom
Lalitha writes "Can I use DML statements against indexed views?
If yes how it works internally, means will the pages gets locked during update and inserts and when the base tables get reflected of these modified data?"
Hi, I'm having a problem creating an indexed view of a productprice table
CREATE TABLE [dbo].[ProductPrice](
[ProductPriceGUID] [uniqueidentifier] ROWGUIDCOL,
[ProductGUID] [uniqueidentifier],
[Price] [decimal](7, 2),
[IsSRP] [bit],
[EffectiveDate] [datetime]
)
Each ProductGUID can and should have multiple records in the ProductPrice table. I want to create an indexed view that will group by ProductGUID that will show either the most recent custom price if a (isSRP = 'False') record exists or the most recent srp price if there is only (isSRP = 'True'), then a column stating whether the price column is taken from a (isSRP = 'True') record, and followed by the most recent srp price (isSRP = 'True').
SELECT
[PP].ProductGUID AS [ProductGUID],
CASE
WHEN MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) = 0
THEN MAX(CASE [PP].IsSRP WHEN 'True' THEN 0 ELSE [PP].Price END)
ELSE MAX([PP].Price)
END AS [Price],
MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) AS [PriceIsSRP],
MAX(CASE [PP].IsSRP WHEN 'True' THEN [PP].Price ELSE 0 END) AS [SRP]
FROM dbo.ProductPrice AS [PP]
-- Eliminate all but the most recent custom price, and msrp price
WHERE ([PP].IsSRP = 'True' AND [PP].EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'True'))
OR ([PP].IsSRP = 'False' AND EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'False'))
GROUP BY [PP].ProductGUID
This query works, but I can't create an indexed view on any query with a subquery. So, If I had some way of limiting the query to return only the most recent IsSRP = 'True' and the most recent IsSRP = 'False' (if one exists) without using a subquery then this would be aces.
Oh, and every ProductGUID has at least one IsSRP = 'True' record.
Any ideas??
hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.
View 1 Replies View Related