all_about_SQL

all_about_SQL all about Business intelligence..ask here ...solve here..take help..give help

22/11/2024

Let say there are two tables Table A and Table B joined together with multiple conditions seperated by "OR" clause, due to this optimizer is selecting index spool operator to process the data, is it optimal or should be changed in terms of better performance???

My opinion I will post by tomorrow...

07/03/2023

SQL interview -10
1) what is the meaning of bulk insert?
2) How D-SQL can be used for inserting multiple text files at a time?
3) What filter options are needed for BCP?
4) How to use Maxerrorcount and what is the benefit of that?
5) If first row is not the data row, how to fetch the data row?

07/03/2023

SQL interview -9
1) what is the difference between an index view and a normal view.
2) Query optimizer chooses which plan if query written on indexes view.
3) how to change to index selection.
4) what are the best index tuning steps.
5) is there any way to create multiple clustered indexes on the same table.
6) how many indexed views are possible on one table.
7) in what cases table scans are better and why?
8) Like operators give index seek with which type of Index.
9) How the query optimizer creates an ex*****on plan.
10) Index hints are good in which case.
, , ,

12/02/2023

SQL Interview -8
1) Indexes are always important,if yes why?
2) which index type is not used generally for databases and why?
3) what if fragmentation is good for databases? Comment if any condition is true.
4) which fragmentation is more dangerous for queries, external or internal?
5) Is there any other process except rebuild and reorganize for removal of fragmentation?
# dba # DBA interview

10/02/2023

SQL INTERVIEW -8
1) What is data validation stored procedure.
2) Important steps needed for data validation.
3) How to use a multiline table valued function for data validation.
4) Constraints can be applied or not in a table variable.
5) What statistics are needed to optimize a table variable.

06/02/2023

SQL interview -7
1) how to migrate stored procedures from one database to another.
2) Is there any method to make global stored procedures in databases
3) Creating a stored procedure in the master database can be accessed from all databases
4) Difference between system stored procedure and user defined stored procedure.
5) How to mark an object as a system object.
6) What are advantages if a stored procedure will be marked as a system object and can be accessed by all databases.

29/01/2023

SQL INTERVIEW -7
1) Why system database are important.
2) what if tempdb memory is filled
3) What are tempdb spills?
4) How is the query optimised using tempdb memory?
5) Is there any option to increase tempdb memory, if yes how and why?
6) How backup of a database is important in terms of optimization.

28/01/2023

Step by Step Process for DB Migration (Side by Side)
Step 1: Once the Target VM is ready, we need to install the SQL Version based on the recommendations or SOP.
Step 2: Take a backup from the Source and Copy to the Target and restore with Recovery option
Step 3: Move all the logins (by using sp_help_revlogin), Jobs, Operators, DB Mail Configurations and Server Configurations
Step 4: Move all the linked servers and if any Proxy accounts
Step 5: Enable inbound/outbound firewall rules for SQL Server/Agent Service and Browser Service
Step 6: Handover the Server to Test their application
Step 7: Once Application team confirmed, everything looks good then will align with all the stake holders/application team and Business Users and required teams and finalize the cutover date for actual migration

On the Day of Cutover, follow the below steps
Step 1: Inform to Application team to bring down the app services
Step 2: Disable if any backup policies enabled from the Third-Party Tools like EMC, NETWORKER or Veeam or Commvault
Step 3: Disable the Backup account if any
Step 4: Initiate Full backups for all the databases to Native Drive or Shared Location, Keep the backup file name in Standard Format like ServerName_DB_Name_Type_Date.bak\trn
Step 5: Connect to the target server and map the shared location or copy the backup files from the folder
Step 6: Restore the databases with No Recovery if it is a production, because if we want to take a log backup from source and then copy those t-log backups to target server and restore with recovery option
Step 7: Once log backups are restored, verify all the databases are online or not, if not take necessary action
Step 8: Fix Orphan Users by funding the sp_change_users_login ‘Report’
Step 9: Verify the Linked Servers Connections
Step 10: Verify the DB Owner Data details and change if needed and change the compatibility
Step 11: Verify the Job owner details and take necessary action if required
Step 12: Handover to the Application for Validation
Once DB Activities are completed, App team will modify their connection strings and Network team will make the ns changes and update to app team to validate
Step 13: App team will validate and confirm whether application is working or not. If any login issues troubleshoot and fix

27/01/2023

SQL interview -6
1) what is the difference between rank() and dense_rank()
2) limitations on using rank function and group by together.
3) Can ranking functions be used with all SQL statements.
4) The importance of a derived table with ranking functions .
5) Can we filter results on ranking functions without using a derived table.

26/01/2023

SQL INTERVIEW -5
1) How sample data can be selected from a set of rows.
2) what is percent sampling
3) why sampling is necessary.
4) can CTE be a solution for data sampling.
5) Which could be a better solution, CTE or Temp table and why
6) What statistics can be seen after data sampling.
7) If data rows are more than 1 million, what percent sampling would be a better case.

24/01/2023

SQL interview -4
1) why openquery is used in SQL servers.
2) To use a different server what naming convention needs to be used.
3) what complex procedure is generally used.
4) is it possible to create a view with parameter,if yes how?
5) why we use exist and not exist.

23/01/2023

some optimization techniques for SQL query:
1) try to avoid (*) for column selection.
2) always use three part naming conventions.
3) on calculated column, if possible create columnstored indexes.
4) while using computed column, use persisted property.
5) Avoid dynamic SQL .
6) Try to use temporary table instead of table variable if data is big( also can check tempdb spills)
7) indexes should not be fragmented regular maintenance is needed.
8) For stored procedure tables, try to update statistics regularly.

Address

Gorakhpur
273013

Telephone

+918318719251

Website

https://www.youtube.com/@erravipratapsingh8409

Alerts

Be the first to know and let us send you an email when all_about_SQL posts news and promotions. Your email address will not be used for any other purpose, and you can unsubscribe at any time.

Contact The Business

Send a message to all_about_SQL:

Shortcuts

Share