Qes. #1 What is the difference between function and stored procedure in SQL server?
Function |
Stored Procedure |
| The function must return a value | Stored Procedure is optional, Even a procedure can return zero or n values. |
| Functions can have only input parameters | Procedures can have input or output parameters. |
| Procedures cannot be called from a Function | Functions can be called from Procedure |
| The function allows only SELECT statements in it. | The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it |
| The function can be embedded in a SELECT statement. | Procedures that cannot be utilized in a SELECT statement |
| function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section | Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section |
| try-catch block cannot be used in a Function. | An exception can be handled by try-catch block in a Procedure |
Qes. #2 Can we call a procedure from another procedure?
Yes we can call SP from another SP like below
ALTER PROCEDURE [Reports].[GetXMLData] ( @AssigneeId INT, @FinancialYearId INT ) AS Begin EXEC [Reports].[Get_Salary] @AssigneeId, @FinancialYearId EXEC [Reports].[Get_HP] @AssigneeId, @FinancialYearId End
Qes. #3 Can we do an insert operation in a function
No, you can not do Insert/Update/Delete, Functions have only READ-ONLY Database Access
Can we use Procedures in the SQL statements anywhere like WHERE/HAVING/SELECT?
No,
Qes. #4 What is user-defined data types and tables in SQL Server
Here the data types are in different groups.
- Exact Numeric: bit, Tinyint, Smallint, Int, Bigint, Numeric, Decimal, SmallMoney, Money.
- Approximate Numeric: float, real
- Data and Time: DateTime, Smalldatatime, date, time, Datetimeoffset, Datetime2
- Character Strings: char, varchar, text
- Unicode Character strings: Nchar, Nvarchar, Ntext
- Binary strings: binary, Varbinary, image
- Other Data types: sql_variant, timestamp, Uniqueidentifier, XML
- CLR data types: hierarchyid
- Spatial data types: geometry, geography
Qes. #5 What are the names of system databases in SQL Server?
| master Database | Records all the system-level information for an instance of SQL Server. |
| msdb Database | Is used by SQL Server Agent for scheduling alerts and jobs. |
| model Database | Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward. |
| Resource Database | Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. |
| tempdb Database | Is a workspace for holding temporary objects or intermediate result sets. |
Qes. #6 Where the SQL agent reside in SQL SERVER
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server.
Benefits of SQL Server Agent
- SQL Server Agent uses SQL Server to store job information.
- Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.
Qes. #7 What is the way to optimize SQL query
-- Avoid the below query SELECT Employee.ID, Employee.Name, Department. DepartmentName FROM Employee, Department WHERE Employee.ID = Department.EmpID -- Prefer this one SELECT Employee.ID, Employee.Name, Sales.LastSaleDate FROM Employee INNER JOIN Department ON Employee.ID = Department.EmpID
Qes. #8 What is a bit data type in SQL?
SQL Server bit data type is an integer data type that can take only one of these values: 0, 1, NULL.
Qes. #9 What are views in the SQL server?
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
Types of Views
- Indexed Views
- Partitioned Views
- System Views
Qes. #10 What are indexed views?
An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table.
An indexed view has a unique clustered index. The unique clustered index is stored in SQL Server and updated like any other clustered index. An indexed view is more significant compared to standard views that involve complex processing of large numbers of rows, such as aggregating lots of data or joining many rows.
How to create indexed Views
- create a view with schema binding option
- create an index on view
Qes. #11 What are clustered indexes?
A clustered index defines the order in which data is physically stored in a table. It sorts and stores the data rows in the table or view based on their key values. there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.
Once we create a table and define any column as a primary key, then a clustered index will automatically create.
EXECUTE sp_helpindex tablename
Qes. #12 Can a table have multiple cluster index
No, A table has only one primary key, so only one cluster index in a table is possible
Qes. #13 what is a non-cluster index, how many non-clustered indexes we can create in the SQL server?
A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.
When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.
Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX
Difference
- There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
- Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
- Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.
Qes. #14 What are the Joins, Types of Joins in SQL-Server
Join clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL Joins
Here are the different types of JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tablesLEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right tableRIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left tableFULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Qes. #15 What are Cross join and Left Outer Join
- CROSS JOIN is also known as the Cartesian product / Cartesian join.
- Cross join defines where the number of rows in the first table multiplied by a number of rows in the second table.
- Cross Join applies to all columns.
Full outer join:
FULL OUTER JOIN combines the results of both left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the join clause.
Read More
Qes. #16 Which is better Join or SubQuery?
A subquery is easier to write, but a joint might be better optimized by the server.
To optimize the SQL query, it’s always advisable to avoid correlated subquery,
Qes. #17 What are triggers and what is instead of triggers?
Qes. #18 What are the differences between the primary key and a unique key?
Qes. #19 What is the difference between varchar and nvarchar
Qes. #20 Can we insert a @ symbol in the varchar datatype
Qes. #21 What are serialization and deserialization
Qes. #22 Where are Magical tables stored
Qes. #23 Do u have done Exception handling in the database
Qes. #24 What are the ways to optimize SQL Query to increase the performance
Qes. #25 When the table is stored in B-Tree
Qes. #26 What is Tuning Wizard
Qes. #27 What are Magic Tables
Qes. #28 Difference between local temp variables vs global temp variables
Qes. #29 What are @@variables
Qes. #30 What are triggers
Qes. #31 What is serialization
Qes. #32 How to fetch top 5 rows from database then fetch 2,8 and 10 salary
Qes. #33 What is the tuning wizard in SQL
Qes. #34 What is CT in SQL
Qes. #35 How to get the length of a string in SQL
Qes. #36 How to reverse the string in SQL and also do the same in c#
Qes. #37 What is a dependency injection pattern?
Qes. #38 Types of Functions in SQL
Qes. #39 How to do self-Join in SQL
Qes. #40 What is the difference between stored procedure and function in SQL
Qes. #41 How many types of functions are there in SQL
Qes. #42 What are triggers
Qes. #43 What are magic tables in SQL
Qes. #44 What are temp and temp variable
Qes. #45 Find out emp name who have more than 1 Mob No
Qes. #46 In a table salary column, there are 20,000 rows, but only 1500 values
Qes. #47 What are scalar functions in SQL
Qes. #48 What are the Primary Key and Foreign Key in the SQL server
Qes. #49 What are Constraints, Alternate Key, and Candidate Key in SQL server
Qes. #50 What is Rank in SQL Server?
Qes. #51 How to prevent deadlock in SQL server
Qes. #52 How to break the deadlock in SQL server
Qes. #53 How to find distinct rows from the data table in c#
Qes. #54 Write a program that runs from 1 to 100 when number divided by 3 print “Amit”
Qes. #55 What are transactions in the SQL server?
Qes. #56 What are magic tables in SQL SERVER
Qes. #57 What is ACID properties in SQL SERVER
Qes. #58 What is 5th Normalization form
Qes. #59 What are the differences between normalization and denormalization
Qes. #60 What are the differences between delete and truncate
Qes. #61 What is the transaction
Qes. #62 Can truncate could be rolled back in the transaction
Qes. #63 What is the difference between function and stored procedures in SQL
Qes. #64 What is the difference between primary key and unique key
Qes. #65 What is CTE in SQL server
Qes. #66 What is a profiler in SQL SERVER
Qes. #67 What is the use of the Master database in SQL SERVER
Qes. #68 What is the difference between SQL and T-SQL
Qes. #69 What is Normalization and How many types of Normalization are their
Qes. #70 What is the difference between repeated data and redundant data
Qes. #71 What is identity in SQL SERVER
Qes. #72 What is Index in SQL Server
Qes. #73 How many clustered indexes we can create in the SQL server
Qes. #74 What is a deadlock in SQL Server
Qes. #75 Does materialize view to store the data in a separate location
Qes. #76 When we insert rows in the table then do we need to refresh materialized view?
Qes. #77 What is Locking in SQL SERVER
Qes. #78 What are ACID Properties in SQL SERVER
Qes. #79 Fetch 5th highest salary from salary table
Qes. #80 Write a Query in SQL to find different valves from a column which should start only from a,b and second case value start from a but only three characters
Recent Comments