If your first index is on AssetId, Date, DataSource and UserId (in that order) then you don't really need a separate index on AssetId and Date because they are the leading columns in you primary key.
The conventional wisdom is that you should put your most unique columns at the front of indexes. I tend to recommend that you put the columns that are most commonly searched upon (which may be different).
If you also needed to frequently search on UserId and Date then you would consider using an additional index.
Note that (by default) SQL-Server uses a clustered index to enforce primary keys. This has the side-effect of appending your primary key columns to every non-clustered index that you create on the table. If you are using this primary key to join tables then you might want to consider using a separate identity column as the primary key on this table, and a separate non-clustered index on the four columns that you noted in your question.
Pretty much as I thought then, although your point about non-clustered indexes appending the primary key on is an interesting one. I had assumed that the non-clustered index would have some sort of 'pointer' which could read straight into one of the data files rather than a primary key value to lookup.
I would imagine this could make the indexes very large, especially as in my table there are 6 columns in the primary key. Worth thinking about!
For a table with a clustered index, the pointer into the base table is the clustered index columns. If you don't like this, don't use clustered indexes.
A clustered index means that the actual data rows are stored in the leaf levels of the index.
If the non-clustered indexes stored some physical location data as the pointer value, as they do for 'heaps' (tables with no clustered index), the indexes would have to be updated as rows moved within a page or between pages due to additional records being inserted. This wouldn't be easy.
I would like to retrieve specific region names, where a region code is less then 3 characters. I cant just do a normal select query, as some of the codes are more then 3 characters. Here is my sql query, but its not returning anything.
SELECT RegionName, RegionCode
WHERE DATALENGTH(RegionCode) < 3
ORDER BY RegionName
i need sample or tutorial of an application that work with ms access database (*.mdb) or sqlserver that installed in server and clients could run application with shortcut and work
i wanna create a networking application
i have two databases which are used for different projects. but i need to update a column of one database with the data from another database.
is there a way to do that using SPs? or any other way?
You can do this job by these statement.
1)Declare a Variable like v
2)Select the record you want use by SELECT Statement and assign it to the v by = operator
3)Update the record you want by UPDATE Statement and set it to the v.
I hope this solution is useful
I use SQL Server 2005 and Visual Basic 2005.
I write these Statement in SQL Server but it executes these with error and the record dose not inserted into table Project.
CREATE PROCEDURE Insert_Project
SET NOCOUNT ON;
DECLARE @Number_O int;
SELECT @Number_O=Number-sum(Number_A) FROM Machine_View WHERE Row=@pRow_Machine GROUP BY Number
INSERT INTO Project
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
Please help me.
Hi guys ! I have installed windows vista and i also installed microsoft sql sever 2005 express edition, and also I have downloaded the requierd update ! but when i want to create a database i get this message :
TITLE: Microsoft SQL Server Management Studio Express
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
The server principal "MRK\Mr.K" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)
i need a help in an automation of data fetch from db to excel.
I am having 2 tables in sql server db. at present i will type hundreds of number in excel sheet then i will import that to sql server as a table then executing that table and the primary table using some sql statement i used to fetch the required information. now the problem is when we are doing it for multiple records its too difficult to repeat the process repeatedly.so i want to automate this process.
slno eno,ename, eaddress, ecity
exporting data through excel is eno
comparing this eno in excel with the primary table i can fetch the remining details to the specified set of eno.
how to automate if anyone have idea please share it. so that i can begin my process as soon as possible.
I would used DTS. It comes with SQL Server.
If you have sql server 2000 go to where you start enterprise manager. There is an icon that says import export. If you are using sql 2005 you need to go to the database you want to export the table. Right click the database go to tasks then export.
Pick your sql server name and database to export from.
Pick Excel from the drop down and set the output file name.
Then pick the table / table names or do a query.
I think that could work pretty nice for you process.