Nandhini Ramanujam

Hi. I'm Anthony. I'm a SQL developer working in full global tech. I have over 10 years of experience in SQL development. I've been involved in performance tuning, database designing, and SQL server migration, as well as Oracle SQL server migration. In my current project, we're sending data through APIs to another URL. This has been achieved using SQL server procedures, which is a 20-year-old project. Without disturbing the existing functionality, we're sending data and performing enhancements to it. That's it from my side.

So, without accessing the database, a low-running query can be caused by the data load, and no log can be used in the select statement. That is, it will give only the date 30 reads. Without accessing the database, we can use select statements with no log. That is the limit we can have.

To minimize schema changes, what we can do is have some temporary tables or views. And also, if permitted, we can have user-defined functions to achieve some calculations or computations. That's what we can do. Other than that, we should understand the requirement clearly, and we should design the database properly with the primary table and the related tables.

Yes. A table partitioning can be done in the tables. In my current project, I have done table partitioning. Based on the year, it's like if it's based on the year, we can do the partitioning so that date data can be read easily. So if it's like an order table or some transaction table, we need to identify the key column of that. So in the data or key where the data has been written, we need to identify and implement a non-clustered index on it, and then we can have a table partitioning based on that. And we can write the select statements or procedures based on that condition. It will work fine.

To improve the indexing and index performance, we need to identify whether this index has the exact problems or not. We should not over-index as well as we should not do the minimum of indexing. So, the criteria where conditions have been used frequently, we need to identify that. So most-used bear conditions can be composed as an index part, and then we can use it. And also, the columns with indexing will be tough, but still, if we require it, we can use it.

Okay, complex queries are like joining multiple tables efficiently means we need to identify the primary table and then use join conditions to join the detail tables. The join condition must be correct, whether left join or right join, and it must be appropriate based on the primary key and foreign key relationships. We should not use functions in the on condition or where conditions, as it will reduce the query performance. Since we are already using multiple tables, the query performance will be slow. What we can do is avoid functions in the join conditions and where conditions, instead compute the data and then join the conditions. If we want to use a function, we can use it in the select columns, as it will not affect the performance mostly. If the data load is very high, we can segregate the tables, for example, if we have six tables, we can separate them into three, and then join based on it. We can populate the data into temporary tables and then have a join. This will improve the data read, as the three tables will have lower data. We can also select the condition based on the metadata or primary detail in the three tables, and then split the query into two, populating the data into two temporary tables, and then have a select from that, which will improve the performance of the queries.

Actually, in the while statement, select star from select count(*) from users where active = 1 and id > 0. So this while loop will be executing every time so that we can select the count of the row and we can delete the user based on the where condition. There is no minimum and maximum value mentioned. Instead of mentioning 0, we can give some value. And the count of the record, we need to select the appropriate column in a local variable assigned to it, and then we can give that. And also, in the where condition, we need to identify which tables to be deleted, so the where condition is also missing in the delete statement.

Actually, price category has been defined here only in the where condition. So the column does not exist in the product state products table. So that column is a defined column. Like, it has been defined in the select list only. So the price category is equal to expensive will not work. The syntax error is where price category is equal to expensive. That line will give an syntax error. So rather than using it, we need to write a subquery of this. We can, like, select ID, the column names that have been listed from products within the subquery, we need to select. We can write this query, and then from that, we can select. From this and select all the columns listed here where price category is equal to expensive.

To avoid data concurrency, we can use in high transaction environments. We can use no lock in all these transactions stored procedures as well as the select statements that have been used. And also, if multiple inserts and updates are happening, it must be within the begin transaction and rollback. If it fails, we can rollback the transaction. That action we need to use, and data concurrency issues will be prevented. The proper begin transaction rollback is not done properly. So if we manage that properly, this data concurrency error will not occur.

Yes. We can have Analysis Services in the database if we want to have analytical data, such as fact tables, and we need to denormalize the tables. We need to have fact tables and dimension tables, and we need to have analysis of the data. For example, we need to have analytical data for the year, a particular period, or a particular transaction or dimension. By then, we can use SQL Analysis Services to implement this, which will provide more analysis of the data in the database. We can have a separate database for it to populate the data, and then move to that. Over a particular period of time, we can have it separately.

Documenting is that we need to maintain a complete code. Like if we are planning to release a code, if we are going to release a particular code, we need to have all the scripts maintained in a particular document. In that, we need to have the table changes and table creations first. Then we need to have the data types creations or data types alterations. After that, we need to have the table creations and table alterations. Then we need to have functions, alterations, and function creations. Then we can go for triggers, and then for procedures. This must be the order. And also, if we are going to give the upgrade of the code, then we need to have the recovery of the code. If we want to roll back the complete script, we should have the opportunity or we should have the option to roll back the changes. So, a rollback script is a must while releasing the code. We can have, we can maintain the code in Jira or whatever it may be, we have like a source tree or a lot of options to maintain the code, but still we need to have a rollback script for each and every release script that is mandatory.