I was reading this blog below about Indexed Views and needed some clarification.
When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
[URL] ....
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts..... can you run process in sql server against tables when:
- indexes are being created - the structure of a table is being modified - the database is being shrunk
If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)
1. I am trying to create an index, which is running for a long time. Is there an option to know the progress so far or how much more time it is going to take?
2. Is there anyway to find out how much space is required to rebuild an index?
------------------------ I think, therefore I am - Rene Descartes
I could not find an SQL script, which would generate index creation DDL for a specific table/index. Query Analyzer does it, but I need to do it programmatically. Does anyone have a working script? Thank you.
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
I am trying to create a new full text index on some table and getting the follwing error
"The Microsoft Search service
cannot be administered under the present user account"
i have already checked that Microsoft Search service is properly installed with the server version of SQL Server and this is running under the system account
could anyone plz comeout with the solution to this problem]
I'm trying to use a user defined alias type in a table creation, and everything works as expected, right until the moment where I rightclick on [Indexes] and click [new index] to create a new index.
Then I get this error:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Cannot show requested dialog. ------------------------------ ADDITIONAL INFORMATION: Cannot show requested dialog. (SqlMgmt) ------------------------------ Object reference not set to an instance of an object. (SqlManagerUI) ------------------------------ BUTTONS: OK ------------------------------
Can anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country.
Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds?
is there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
I have a Performance issue in ssrs report , query takes execution time 1 to 2 min. When I add the non clustered index for one of the table columns which takes only 2 sec to execute the query. But as from my environment I couldn't able to add the index due to these changes will effect for all reports. Without adding indexes which takes 2 min to run that report. My question is:
Is there any way to add the non-clustered index on the report itself like if a non clustered index exists "execute the query" else create a non clustered index and run that report with newly created index in report itself like a temp table concept. How to add this concept in this below query:Final Query without non clustered index, takes 2 min to execution time ---
with cte1 ( MovementDate, MovementTypeDesc, CDCNumber, MovementTime, LocationReportMovement, OtherLocationCode,
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee (OrganizationLevel, OrganizationNode) WITH (DROP_EXISTING = ON) ON TransactionsFG1; GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
I'm just starting to dabble around with LINQ and all of the examples I've come across all have 1 where clause. Is this intentional such that we are supposed to build queries on top of queries or is there a method in which I can add multiple predicates to my 1 query? I'm sorry if this sounds like a stupid question, I've been googling it to death and reading the few manuals on LINQ but like I said, all of the examples have had 1 where predicate.
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this: CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey OR LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Hello all. I'm running SQL Server 2000 and I'm trying to get a veryfew, recent rows of data from a table based on an indexed datetimecolumn. Here's my predicate:where order_date > dateadd(hour, -1, getdate())i.e. everything more recent than one hour ago. This corresponds to the3 or 4 rows in which I'm interested. I have order_date indexed and Ihave current statistics. When I check the explain plan for this queryI see expected rows returned: 114,000. When I go on to join to severalother tables I end up with unnecessary hash joins -- due to theinaccurate cardinality estimates on this table.However, if I use the following predicate (which corresponds to datawithin the last 3 days):where order_date > '2006-03-24'then I see an estimated rows returned: 6 -- which is pretty accurate.[color=blue]>From there the rest of the query's explain plan falls together nicely.[/color]So my question is: how do I get the optimizer to realize that one hourago is pretty recent?Many thanks,Scott
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
There is a checkbox when you create an SSIS catalog which says "Enable automatic execution of Integration Services stored procedure at SQL Server startup". It's a good practice to have this option on. BUT! How do you verify whether this option is ON or OFF after you have created a catalog and how do you enable it if it wasn't enabled at the time of creating the catalog?
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
I have a table with primary key and also clustered index on that primary key column. I need almost all columns from that table. When I wrote the select column names, it showing that Index scan occurred. How can I avoid that Index scan and change to index seek? When I check the fragmentation of that Index it is showing more than 34%. Is that fragmentation is ok or do I need to reorg the Index?
how to fragment an index so I can test it's fragmented performance on an iSCSI LUN.I can test without an index, that's fine. I can test with a newly created index (of course that means it's not fragmented) and that's fine.But what I want to do is DELIBERATELY FRAGMENT () an index to 90%+ fragmented to test it's performance.
I want to know more details about the Clustered Index Delete. Is that Clustered Index Delete in the execution plan is good or bad or we can neglect that cost. Is there any way to avoid that clustered Index delete operator from the execution plan.
When creating a column store index, are there any reasons not to include all columns, besides index size of course? i.e. will the index be more versatile with more columns or should I treat it exactly like its a standard index, putting only necessary columns, in the correct order?
