SQL Server Section

Efficient SQL performance is crucial for the smooth operation of any database-driven application. Slow queries can lead to longer load times, hampering user experience and reducing overall efficiency. This blog post will explore strategies and best practices to optimize and improve SQL performance. Understanding SQL Performance SQL performance optimization refers to making database queries execute as efficiently as possible. This involves minimizing resource usage, reducing execution time, and ensuring the database scales well with increasing data and user load. Key Strategies for Improving SQL Performance 1. Indexing Smartly Use Indexes Judiciously: Proper indexing can dramatically speed up data retrieval. However,…

SQL is a versatile language for managing and manipulating databases, and one of its most powerful features is the ability to join tables. The LEFT JOIN command in SQL is particularly useful for combining data from multiple tables, providing a broader view of the dataset. This blog post aims to illuminate the LEFT JOIN command, discussing its purpose, functionality, and practical applications. What is LEFT JOIN? The LEFT JOIN command in SQL combines rows from two or more tables. It returns all records from the left table (table1) and the matched records from the right table (table2). If there is…

SQL, the language of databases, is a foundational tool in the arsenal of anyone working with data. Among its various commands, the INNER JOIN is critical in efficiently retrieving and combining data from multiple tables. This blog post aims to demystify the INNER JOIN command, providing insights into its functionality, uses, and best practices. Understanding INNER JOIN At its core, an INNER JOIN is a method used in SQL to retrieve data from two or more tables based on a related column between them. This command is essential when combining data from different tables into a single, unified dataset. How…

What is one? An SQL Server execution plan helps investigate performance issues with queries. As Database professionals, we receive user complaints that systems run slowly. Query results that used to take two or three seconds suddenly require 2 or 3 minutes. Troubleshooting may require investigating server resources (CPU, memory), statistics, disk performance, blocking, deadlocks and indexes, and often it is more than one of these. What happens to your query when the SQL server runs it? The diagram below shows the basic flow: – The user or application authenticates with SQL Server and submits a query. SQL Server parses the query and…

Performance matters Ensuring your applications run quickly is vital in an age where users expect immediate results. Most modern computers are high-speed – smoking fast – even compared to machines from 5-10 years ago. Unless the application is doing complex maths or rendering images in games, chances are your computer is only idling along. That is, unless it is accessing data from files or a database. Most of the applications I work with have a lot of data in SQL server. Hundreds of GB of data are common. Sometimes, a database has several TB of data. Accessing such a database…

Background Most of the systems I have worked on are Database First applications. Either the team or SQL experts define the database first – or it has existed for years – and then we build code to use it. When you point the tooling at the database, ORM tools, such as Entity Framework and EFCore, generate models for you. However, what do you do when you want to have POCOs for your model? You could hand code each model; you could use a tool like CodeSmith to generate the models, or you could do something like the SQL below and…

What are identity fields? Like most database systems, SQL Server can define numeric fields as Identity fields. This means the database server automatically sets the numeric field value to the next available value each time a record is inserted into the table. The first record gets 1, the second 2, etc. Typically, the developers create Identity fields as the Primary Key columns on tables. Again, typically, these are ‘int’ fields. In most databases, this does not cause a problem. Indeed, for most databases, the use of ‘int’ Identity fields will never be a problem. What’s the problem? The problem is…

Background Whenever users report that a system is slow, there can be many reasons. It can also be several different problems causing the system to be slow. However, more often than not, the problem is due to your code accessing data from something, and it is usually a database. Even the best and most optimised database application can still have problems; perhaps too many users are running complex processes or just one long-running query impacting every other function. How do we check who is running what? There are lots of built-in SPs and Queries that allow you to view what…

Why duplicates? It is surprisingly easy to inadvertently create duplicate records in a table. More often than not, it is because we either didn’t check for an existing record or we didn’t look for the right records by messing up the keys in some way. How do we delete the duplicates? This first step is to find the duplicate records. I use this code: – with x as   (select  *,rn = row_number()             over(PARTITION BY fieldName order by FieldName)             from    mytable) select * from x where rn > 1 This lists all instances where there are duplicates of ‘fieldName’…

Do you know? There are lots of reasons why you might want to know how many records there are in your database tables. One of the most common is because your fellow business users want the number to impress potential customers. The code SELECT sc.name +'.'+ ta.name TableName  ,SUM(pa.rows) RowCnt  FROM sys.tables ta  INNER JOIN sys.partitions pa  ON pa.OBJECT_ID = ta.OBJECT_ID  INNER JOIN sys.schemas sc  ON ta.schema_id = sc.schema_id  WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)  GROUP BY sc.name,ta.name  ORDER BY SUM(pa.rows) DESC