Tuesday, December 3, 2019

SQL Server interview questions

SQL interview Questions

Are you planning for your SQL interview ? Want to crack your SQL Interview ? then go for the SQL Server interview questions book, 


        This book gives you a complete idea about the SQL database. It starts with a very basic concept like what is a database, its usage, types, creation, and data storage, security, sorting, and searching for a stored procedure. This book is a complete set of interview breaking questions and answers with live examples and plenty of screenshots. This book takes you on a journey to mastering the SQL database, including SQL datatypes, functions, triggers, and stored procedures. This book also covers the latest and new features of SQL 2016, 2017 and 2019 CTP with examples.

In the beginner section, we start with very basic concepts like what is a database, why to use a database, different types of database types, what is SQL, its usages, advantage and disadvantages, SQL datatypes, its different operators and how to use them with samples.
In the intermediate section, we will learn about the different SQL functions, SQL Joins (used to fetch values from multiple SQL tables) and SQL DDL, DCL, and DTL commands.
(About the last chapters) This is the advanced section of the book where we have provided an explanation of the SQL stored procedure, triggers, and SQL view concepts, additionally, we have covered SQL core concepts like keys, indexes, injections and constraints. We have also introduced cutting-edge concepts like SSRS, SSIS, SQL Cloud database (Azure), JSON Support and a list of the new features of SQL 2016, 2017, CTP-2019 with SQL performance improvement tips. Finally, we have ended the book with a series of random SQL questions and answers.

TAGLINE 
Let us break the SQL interview with the help of SQL Server interview questions.

KEY FEATURES  
• Database Basic Concepts
• SQL Fundamentals
• DDMS, SQL Statements, and Clauses
• SQL Operators, Datatypes, and Keywords
• SQL Functions, Wildcards and Dates
• SQL Joins and CASE Statement
• SQL DDL, DCL, and DTL Statements
• SQL Stored procedures, Triggers, Views, and Transactions
• SQL Keys, Indexes, Injection, and Constraints
• SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
• New features of SQL 2016, 2017, and 2019
• SQL Performance Improvement Tips
• Fuzzy Interview Questions and Answers


WHAT WILL YOU LEARN  
After reading this book, you will be able to understand SQL database concepts, handle core database activities like data security, searching, migration, and sorting. You will be able to handle the database transactions, use different SQL datatypes, functions, triggers, and stored procedures to save and retrieve data from the database. You will also be able to understand advanced SQL concepts like SQL reporting services, integration services, cloud database and new features from the latest SQL versions like 2016, 2017, and 2019.

WHO THIS BOOK IS FOR 
This book is built in such a way that it is useful for all categories such as technical or non-technical readers. This book is perfect. If you are a fresher and you want to learn about SQL, or if you are a teacher and you want to spread SQL knowledge, this book is very helpful. If you want to crack the database interview or if you are working as a DBA and you want to upgrade your knowledge, or if you are backend developer, database tester, performance , or if your role is that of a database admin, SQL developer, data analyst, mobile app developer or if you are working on core SQL concepts, this book is just right for you.
This book is very useful as it contains many simple real-time scenarios for each concept. All functionalities are explained with real SQL screenshots and database records.

CONTENTS
1. Database and SQL Basics
2. DDMS SQL Statements and Clauses
3. SQL Operators, Keywords, and Datatypes
4. SQL Operators
5. SQL Functions, Wildcards, and Dates
6. SQL Joins and CASE Statement
7. SQL DDL, DCL, and DTL Statements
8. SQL Stored Procedures, Triggers, Views, and Transactions
9. SQL Keys, Indexes, Injections, and Constraints
10. SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
11. New features of SQL 2016, 2017, and 2019
12. SQL Performance Improvement Tips and Fuzzy Interview Questions

https://bpbonline.com/collections/new-relases/products/sql-interview-questions-a-complete-question-bank-to-crack-your-ann-sql-interview-with-real-time-examples

Thanks
Prasad

Thursday, August 29, 2019

Debug your stored procedure.

Getting started

We can debug our .NET application easily using a debugger from debug menu, .NET application has its own .PDB (Program Debug Database) file that used to store Debug location and increment linking of debug configuration. This all about .NET but can we debug our stored procedures? Answer is YES, we can debug our stored procedure. Let's find out How to do it.
Stored procedures are popular enough due to their features like enhanced security, separation is possible in data functionality and application, improved performance (as fewer call made to database), basically it's a one time and one place processing theme. SP (stored procedure) is now more popular in DBA and Developer crowd, But after writing large SP's, how to debug them ? How to test them if they are running properly
So here is the need to Debug stored procedure, Let's see what we need to debug it.

Bit by bit

1. First step is to create a Stored procedure (if you have stored procedure already created then you can skip this step), Here i am creating a simple Stored procedure, which select record from EMP table for specific ID, see below snap
Image 1
2. After stored procedure created successfully, we are ready to debug it. Basically There are couple of ways to debug stored procedure
  1. Using SQL Management studio (SSMS)
  2. Using Visual Studio
Will see how to Debug it using SQL Management studio (I am using SQL 2008R2 Express)
  • Open SQL 2008 R2
  • Click on ViewMenu --> Object Explorer
  • Click on Connect database engine  --> Enter username and password for SQL database --> Connect to database, see below snippet
            Image 2
  • Expand Database --> Select Programmability folder --> Stored Procedures (You will see your procedure here)
            Image 3
  • Open New Query window by right click on database and click on 'New Query'
  • Write statement 'Exec ProcedureName' (in my case its procedure name is 'sp_empInfo')
  • Put Debugger on that line using key F9
            Image 4

  • Select Green arrow (debug) from SQL and your debugger gets start, Press F11 to step in to procedure
  • You can see output in 'local' and 'callstack' window
          Image 5
Debug it using Visual Studio
Same way you can use Visual Studio to debug the stored procedure
  • Go to Visual studio and connect database. follow below steps
  •  Open Visual studio editor
  •  Click on ViewMenu --> SQL Server object Explorer (Before VS 2012 it was Server explorer)
            Image 6
  •  In SQL Server object Explorer Pane right click on Data Connections
  •  Select Add connection
  •  Enter credential (like SQL instance, SQL server user name, Password, database name)
  • Right click on database and allow 'Application debugging' and 'Allow SQL/CLR debugging', see below snap
           Image 7
  • Expand Database --> Select Programmability folder --> Stored Procedures
  • Right click procedure --> Select 'Debug Procedure'
           Image 8
You can start Debug from now.

Exceptions

Access permission is the biggest issue while debug stored procedure,
Common Error Messages
following are the common error messages occurred during debug stored procedure
  1. Unable to start the transact-SQL debugger, could not connect to the Database Engine instance...
  2. Unable to start T-SQL debugging, Could not connect to computer...
  3. Logon failure: Unknown username or bad password...
  4. Failed to start debugger, The execute permission was...
  5. Could not attach to SQL Server process on...
see some exceptions/errors snap below
Image 9
Following exception occurred when you tried to debug Remote stored procedure
Image 10
Common points to avoid error while debugging
To resolve above errors you can try out following workarounds
  1. If you are debugging stored procedure on local SQL server then your local SQL user should be in 'sysadmin' role, to add user in sysadmin role, run below stored procedure (it is inbuilt), see below syntax
  2. sp_addsrvrolemember '<Login>', 'sysadmin'
  3. Configure TCP port in Windows firewall to enable t-SQL Debugging, for details see MSDN link
  4. Configure TCP port in Windows firewall to enable for remote t-SQL Debugging (when SQL server is different and want to Debug it from client machine) see MSDN link, For remote debugging in either case you can use Windows local or domain account authentication
  5. Open firewall ports on both SQLserver machine as well as client, PORTS: TCP 139, TCP 445, UDP 137 and UDP 138 
  6. SQL management Studio is should be lunched with “Run an administrator…”
  7. Management studio(Client machine) and Server (SQL) should be in same domain, otherwise you need to setup account with same Username and password

What we learn !

We can debug stored procedure by using Visual studio or by using SSMS (Sql management studio), we can use local variables, call stack windows for that. You can provide parameters to stored procedure (if any) and get instant debugger on it. 
Hope it will be useful in daily .NET/SQL life. 

Thanking You
- Prasad