Welcoem to posts!!
in the future - u will be able to do some more stuff here,,,!! like pat catgirl- i mean um yeah... for now u can only see others's posts :c
SQL TIP: stop using this condition in your query
`where some_column=@some_parameter or @some_parameter is NULL`
It does not use the index of `some_column` and leads to full table scan.
Example :
create or alter procedure GetBooks
@SearchTerm nvarchar(40)=null
as
begin
set nocount on;
select * from Book
where @SearchTerm is null or Title like @SearchTerm+'%';
end
go
-- execute the procedure
exec GetBooks @SearchTerm='The Epic Of Gilgamesh'; -- return 1 row
This query causes the full table scan.
You can solve it with:
1- Dynamic SQL
2- OPTION (RECOMPILE)
I have written an article and include previous community post on sql tip. You can find the solution using dynamic sql and option(recompile) there.
You can check this out:
ravindradevrani.medium.com/what-makes-your-query-n…
1 - 0
SQL Tip: Avoid using implicit conversions.
When we compare two values with different data types, the sql server converts the lower-precedence data type to higher-precedence data type. Let's look at the example below:
-- Note: Note: `CardNumber` is `nvarchar`, there is an index on the `CardNumber` and we are comparing it with an integer 11119775847802
SELECT
CreditCardID,
CardNumber
FROM Sales.CreditCard
WHERE CardNumber = 11119775847802;
Here `CardNumber` is a type of `nvarchar` and we are comparing it with an `integer`. `CardNumber` has an index. There is only one record with CardNumber=11119775847802, so it should read only 1 row to fetch the record. But that won't happen. Sql server database engine reads all the rows to find this record. You wonder why? Ok let me explain.
`CardNumber` is a `nvarchar` type, we are searching for a record : 11119775847802 which is an integer type. Instead of converting 11119775847802 to the `nvarchar`, the SQL Server converts all `CardNumber` rows to an integer, leading to index scan. Are you getting the problem here. We just need a single record, whose CardNumber is 11119775847802, for that the whole table is being scanned.
So, instead of 11119775847802, use '11119775847802', which avoids implicit conversion in a table side, as show below:
SELECT
CreditCardID,
CardNumber
FROM Sales.CreditCard
WHERE CardNumber = '11119775847802';
This query will read 1 row only and use the index effectively.
2 - 0
SQL Tip: Don't use `WHERE field LIKE '%search_term%'` unless you really need it. It would not use index on the field. It would be problematic, if you have a table with large record set. Let's look at the example
Note: The 'Book' table have 999996 records, and it has a non-clustered index on the column 'Title'.
Query 1:
SELECT
b.*
FROM Book b
WHERE b.Title Like '%fairy%';
This query Won't use the index. It Performs 'index scan' and read all 999996 rows (whole 'Book' table).
Query 2:
SELECT
b.*
FROM Book b
WHERE b.Title Like 'fairy%';
This query will use 'index seek' and read only 1 row. Since we have only one record that starts with the text 'fairy'.
3 - 0
SQL Tip: Never put a column inside a function in the where clause.
A query is a `SARGable` (SEARCH ARGUMENTable) if it can take advantage of the index.
If you Include a column name inside a function in the `where` clause, it will make your query non SARGable. Even that column has an index. Let's look at the image to understand it more.
2 - 3
If you are familiar with sql server execution plan, you must have heard about table scan, clustered index scan, index seek, Key lookup and RID lookup. I have created a detail blog post about it. It is okey if you are not familiar with execution plans, I have demonstrated how to use it. Checkout if you are interested in database engineering or performance tuning, these are the core concepts you need to understand.
medium.com/codex/sql-server-table-scan-clustered-i…
4 - 0
Pagination with offset is not a good approach for the large dataset. I ran a query on the dataset of 1 million records in the sql server 2022 and compared the result. First approach (LEFT) with offset and the second approach (RIGHT) without the offset.
Example: We are accessing the 5 records after 999995.
Problem with offset: Offset scans the 999995 rows the returns the next 5 rows. Offset have the overhead of scanning the rows.
The approach used in the right side, do not uses offset. We are using the seek approach there. We are fetching the top 5 records after the 999995. This approach is much faster, comparison results are shown in the result.
However, if your Ids are UUIDs then this approach won't work.
0 - 0
Creating and installing a CLI tool with .net
medium.com/codex/creating-and-installing-a-cli-too…
1 - 0
Containerize Your .NET Application with SQL Server Using Docker Compose
medium.com/@ravindradevrani/containerize-your-net-…
1 - 0
Understanding the pure and impure functions with c# examples
ravindradevrani.medium.com/understanding-the-pure-…
6 - 1
Let's build and grow together...