Is it possible to force the use of a compound index in a query?
create table Test (ColOne int, ColTwo int)
The compound index is ColOne + ColTwo.
I'm interested in searching on ColTwo, but I also know the value of
ColOne will always be the number "1".
How do you structure the SQL statement to concatenate the two INTs and
use the index? Note that I don't have any control over the creation of
these indexes.
We've set up a report farm with two servers, both 64 bit with 4 CPUs each. One has 16Gig and the other 8Gig of memory. We're using Windows NLB and the load test software confirms that the NLB is working. When we run a number of concurrent reports, both servers get utilized, but they only work on a few at a time. The report server queue doesn't seem to be fully utilizing the hardware. From a prior post I've learned that the report server queue automatically runs 4 reports per CPU. This is not occuring for our setup. Has anyone else experienced the same? Are there any configurations that need to be set to open the queue up? The reports are heavy (300,000 records grouped and summed). Does this affect the queuing process?
I need to know how to create a compound primary key in Transact-SQL
It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:/* SectionsContent Table */
CREATE TABLE SectionsContent (
SectionID Int NOT NULL
CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Sections(ID),
DocumentID Int NOT NULL
CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Documents(ID),
Position int NOT NULL
)
Can anyone help me ?
Hello,How can I stop/prevent SQL server from running compound SQLstatements. I do not want the server to run multipleupdate/delete/insert/select statements as a batch. Is there an option?/Kafwww.afiouni.com
View 11 Replies View RelatedI had some SQL queries which are using department ID for join , filter , Group By and Select so , i am having index on department ID of my table File Master scheme ..
CREATE TABLE [dbo].[FILE_MASTER](
[FILE_ID] [INT] IDENTITY(1,1) NOT NULL,
[DEPARTMENT_ID] [INT] NULL,
    [CLIENT_ID] [INT] NULL
    ,[LEAD_DETAIL_ID] [INT] NULL
[code]....
The above index only working when there is condition or group by on department ID .and i when i am querying ..
SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward
FROM dbo.FILE_MASTER AS CLÂ
GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE
and the plan is showing Index scan on index Indx_FM_department_ID .. Why it is not using Index seek , i guess i have both group by Columns in cover index included columns what is the use of cover index then ?
because if i am giving where condition before group by for specific Client ID , Loan Sanction Date it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..
Two servers are configured with Windows 2008 / SQL server 2012 utililizing Always-On for HA. We need to upgrade both servers to Windows 2012 / SQL Server 2014 with minimum downtime(Time for Always-On failover). The upgrade to SQL 2014 is straight forward with for minimum downtime.The Windows upgrade(2008 -> 2012) is the problem. From what I have observed and read in blogs.The Windows node to be upgraded must be removed from the Windows cluster before the node can be upgraded to Win 2012.A Win 2008 and Win 2012 node can not reside in the same cluster. If this is true then the only option I can think of is to dump the DB on WIN 2008 server and restore on Win 2012. This is an outage(time it takes to dump and restore).Is there any other method to upgrade these two nodes utilizing Always-On of some other method without downtime?
View 2 Replies View Related
Hello all,
We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs
Processors: 2x2674MHz processors
Memory: 4GB
RAID 10 disk config
When we run our process on this machine, in total it runs in 30 hours.
When this process is run on another 32 bit server with the following specs
Processors: 8x3658MHx processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 25% slower
But here is the real kicker. When this process is run on a 64 bit server with the following specs
Processors: 8x3658MHz processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 75% slower.
This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.
The default configuration settings in all three places.
Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.
Any thoughts?
I am trying to make up a SQL string which will be executed with the Exec command
I want to add a return column that is not in the table, and the table columns:
something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!
Thanks,
Judith
BEGINNER QUESTIONI have a table which has a compound primary key consisting of two columns.One of these columns is a foreign key which is generated in another table byan identity.I want to be able to generate the other primary key column valueautomatically when an insert occurs but assume that I cannot use an identitybecause it would have to be unique for this table.There will be potentially more than one user accessing this table so I wantto avoid generating the key on the client side.How can I do this? Will it require some hardcore T-SQL?I hope this is clear (I suspect it isn't) I'd be happy to supply more info.I would be extremely grateful for any help!Mark.
View 4 Replies View RelatedHello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
View 9 Replies View RelatedLooking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:
DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);
[Code] ....
But I´m not getting what I need. If you run the above select, the output should be:
stock_codedate1daily_returnLAGCompound_return
stock12014-07-080.00510 0.00000 0.0051000000
stock12014-07-090.00300 0.00510 0.0081153000
stock12014-07-100.00500 0.00300 0.0080150000
stock12014-07-110.00600 0.00500 0.0110300000
stock12014-07-120.00200 0.00600 0.0080120000
stock12014-07-130.00700 0.00200 0.0090140000
stock12014-07-140.00240 0.00700 0.0094168000
stock12014-07-150.00240 0.00240 0.0048057600
stock12014-07-160.00250 0.00240 0.0049060000
The problem is with this column:
(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
The (daily_return + 1) portion should be the accumulated compound return. So it should be something like
(lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
And the output should be:
Date1Daily returnLAGCompound Return
08/07/20140,00510,00000,0051
09/07/20140,00300,00510,0081
10/07/20140,00500,00300,0132
11/07/20140,00600,00500,0192
12/07/20140,00200,00600,0213
13/07/20140,00700,00200,0284
14/07/20140,00240,00700,0309
15/07/20140,00240,00240,0334
16/07/20140,00250,00240,0359
Hi
I have a sql 2000 db which has a table that has a compound key, the problem is that I would like to create a Full Text Catalog for this table. However I noticed that i need a single primary key... but I dont have one.
I created another field on my table called "ftcID" as an int with identity set to Yes
However when I try and create a catalog it doesnt detect that this field is unique.
Does my unique field have to be a Primary Key, I cant remove the compound primary key as it will break my application.
Any help would be much appreciated
Many thanks in advance
Hi all,
I've had this problem for a while now and I'm looking for a better solution.
I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.
I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.
Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?
Thanks for your help.
Hai,
I have been working in DW for a while, but using SSIS as an ETL tool is new for me. I worked extensively on Informatica.
Coming to my question, right now I am trying to do SCD type 1. But my dimension table has a compound key. i.e more than 1 column makes a key for the table. But SCD wizard allows to select only 1 attribute as a business key. Does any one have any suggestions on how to implement SCD if the target table has compound key.
Thanks in advance for your suggestions and answers.
Venkat
Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.
The function of this, I believe was to make the select work but not retrieve any actual values.
I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.
What I am thinking may not even apply to what I need to do here.
If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.
Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table
but I was hoping this would work.
Example:
Insert Into table
Select
1 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'CA'
Union
Select
2 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'NJ'
Union
/*Final Select */
Select
6 as RecId,
' ' as field1,
field2
From test1
Where 'some value' = 1'
Thanks much for your assistance!!!
TADEG
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
What is the difference please?
View 1 Replies View RelatedSir,
1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?
Please give me some tips & samples
Sundar Raman
Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index.
My Question is does the system creates these indexes or something else does this.
Thanks
Chak
Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list.
any help would be appreciated, thank you
At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
Thanks a lot
I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Has anyone encountered this before? Any ideas?
Appreciate it, K.
Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all.
I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
thanks
when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those.
_WA_Sys_is_platinum_0A9D95DB
_WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active
hind_c_33_15 nonclustered, statistics located on
hind_c_37_1 clustered, hypothetical located
Thanks
Raj
Is there a way T-SQL script can find out all indexes built on a set of tables, drop them and periodically ( quarterly as an example ) re-build them ?
Thanks in advance for help.
Ivan
How do you find out indexes ( with column names info ) on a table ?
Thanks in advance.
Ivan
I am on SQL 6.5.
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
All suggestions will be appreciated.
Thanks,
Eric
We have to interduce a new naming convention for the indexes currently available in the user databases.
We also have to drop all the old indexes available in about 250 tables and recreate them all acording to the naming convention we are coming up with.
Can any body suggest any idea.
I thank you guys in advance for your considaration.
hi.
there 2 different queries
1
[MYSQL].... where Cat=@CatID and Date=@Date[/MYSQL]
2
[MYSQL]... where Cat=@CatID and Date=@Date and Salesman=@SID[/MYSQL]
these queries are often used.
my question is about indexes.
should I use two different indexes?
index 1 : Cat,Date
index 2 : Cat,Date,Salesman
or only index 2 is enough ?
I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.
Here is my code:
Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6
I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?
Thanks!
Ronna
Hi all,
I want to know all indexes in database.
What do I do to get them?
Thanks in advanced,
Thi Nguyen