0

I am facing here a problem with a SQL statement, which fetches the data from table called News according to category : News : Id(PK) int, Title string, Subject string, Uid int, Cid int. 

SELECT Id, Subject, Uid, Title FROM News WHERE Uid = @Uid

This statement operates slowly comparing to statement without WHERE since it should go and check every single row to ensure if it is accomplish the condition.

So imagine with me the table News with 10000000 article. What should I do about such a thing?

Improve this question

4 Answers 4

Reset to default
4

If the Id column is a primary key that might already be clustered (they are by default), you just need to create a non-clustered index on the Uid column - especially of the Uid column is a uniqueidentifier (GUID) data type.

This can be created by running the following SQL:

CREATE NONCLUSTERED INDEX IX_News_Uid ON News ( Uid )

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

And how to !? I've been reading an article about this, and the solution was by implement a T-SQL query to create Nonclustered index, but I don't know where to write this query !
I have now edited my original answer to include the SQL to create the non clustered index. NOTE: The format is CREATE NONCLUSTERED INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLUMNS IN INDEX>).
1

You should create an index for the column Uid, with Id, Subject and Title as included columns.

That way the database can run the query using only the index, and doesn't have to touch the table at all.

Improve this answer

2 Comments

What do you mean by creating an index !? Nonclustered index ?
@Israa Adb: Yes. A clustered index can't have included columns.
0

I would create an unique index on Uid.

Improve this answer

Comments

0

You should could create a clustered index on the Uid column. This should improve performance. Note: You can only have one clustered index column per table.

Improve this answer

4 Comments

Or Non Clustered index would be fine/better as it is only looking up a single record and CIs are better reserved for range queries.
Can't you only have one clustered index per table?
@Chad - Yes. So probably the OP already has one on the PK unless they explicitly created it non clustered.
I'm assuming Uid is the Primary Key in the table anyway, in which case it's already using Uid as a clustered index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.