I have to write a complex searching query with TSQL with many filters and paged them as well as sort them at the same query. I have 2 tables with one to one relations to each other and try to use inner join.
The first table called dbo.Products with following columns: Id, Name, Price, Model and color.
The second table is called dbo.Items with following columns: Id, products_Id, Tel, Area, Category and Date.
I need to select Id, Category,Date and Area from the Items table and Name, Price and Model from Products table. Here is my shot:
SELECT Id,Category,Date,Name,Price,Model
FROM dbo.Items
INNER JOIN dbo.Products ON dbo.Items.Id=dbo.Products.Id
WHERE Category=1 AND Name LIKE '%is%' AND Price<1000 AND Area=2
ORDER BY Date DESC
ORDER BY [Id] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
My first question is if the order is correct and the second question is there is better way to write it for better performance, something like using Stored Procedures?
The right syntax would be:
SELECT I.Id,Category,Date,Name,Price,Model
FROM dbo.Items as I
INNER JOIN dbo.Products as P ON I.Id=P.Id
WHERE Category=1 AND Name LIKE '%is%' AND Price<1000 AND Area=2
ORDER BY Date DESC, I.[Id] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY