“SQL Database Concepts frequently Asked Questions by expert members with experience in Basic SQL Database Concepts. These interview questions and answers on SQL Database Concepts will help you strengthen your technical skills, prepare for the interviews and quickly revise the concepts. So get preparation for the SQL Database Concepts job interview”
-SQL Server provides a graphical tool which helps system administrators to monitor T-SQL statements of Database Engine.
-SQL Profiler can capture and store data related to every event to a file or a table.
-SQL Server Profiler can be used
1. To create a trace
2. To store the trace results in a table.
3. To watch the trace results when the trace runs
4. To replay the trace results
5. To start, stop, pause, and modify the trace results
SQL Server supports searches on character string columns using Full-Text Query. This query uses full-text catalogs and indexes to perform activities which are managed by the Microsoft search service. The full-text catalogs and indexes are not stored in a SQL Server database and they can't be backed up and restored by using the Transact-SQL BACKUP and RESTORE statements. The full-text catalogs must be resynchronized separately after a recovery or restore operation.
Similarity
-These both command will only delete data of the specified table, they cannot remove the whole table data structure.
Difference
-TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
-We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.
-TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.
-We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE.
-If table is referenced by any foreign key constraints then TRUNCATE will not work.
The several phases a transaction has to go through are listed here. Database is in a consistent state when the transaction is about to start.
1. The transaction starts processing explicitly with the BEGIN TRANSACTION statement.
2. Record is written to the log when the transaction generates the first log record for data modification.
3. Modification starts one table at time. Here database is in inconsistent state.
4. When all of the modifications have completed successfully and the database is once again consistent, the application commits the transaction.
5. If some error it undoes (or rolls back) all of the data modifications. This process returns the database to the point of consistency it was at before the transaction started.
-Normalization is set of rules that are to be applied while designing the database tables which are to be connected with each other by relationships. This set of rules is called Normalization.
-Benefits of normalizing the database are
1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.
SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.
The different purposes of using SQL Profiler are:
It is used to find the cause of the problem by stepping through problem queries.
It is very useful to analyze the cause of slow running queries.
It can be used to tune workload of the SQL server.
It also stores security-related actions that can be reviewed by a security administrator.
SQL Profiler also supports auditing the actions performed on instances of SQL Server.
The 'fill factor' option indicate how full SQL Server will create each index page.
When the index page doesn’t have free space for inserting a new row, SQL Server will create new index page and transfer some rows from the previous index page to the new index page. This process is called page split.
If we want to reduce the number of page splits then we can use Fill factor option. Using Fill factor SQL will reserve some space on each index page.
The fill factor is a value from 1 through 100 that indicates the percentage of the index page to be left empty. The default value for fill factor is 0.
If the table contains the data which is not changed frequently then we can set the fill factor option to 100. When the table's data is modified frequently, we can set the fill factor option to 80% or as we want.
Data Definition Language (DDL):- are the SQL statements that define the database structure.
Example:
a. CREATE
b. ALTER
c. DROP
d. TRUNCATE
e. COMMENT
f. RENAME
Data Manipulation Language (DML):- statements are used for manipulate or edit data.
Example:
a. SELECT - retrieve data from the a database
b. INSERT - insert data into a table
c. UPDATE - updates existing data within a table
d. DELETE
e. MERGE
f. CALL
g. EXPLAIN PLAN
h. LOCK TABLE
Data Control Language (DCL):-statements to take care of the security and authorization.
Examples:
a. GRANT
b. REVOKE
There are three types of replication in SQL SERVER
1. Snapshot Replication.
2. Transactional Replication
3. Merge Replication
In Snapshot Replication snapshot of one database is transferred to another database.
In this replication data can be refreshed periodically and all data will be copied to another database every time the table is refreshed.
In transactional replication data will be same as in snapshot replication, but later only the transactions are synchronized instead of replicating the whole database.
We can specify the refresh of database either continuously or on periodic basis.
Merge replication replicate data from multiple sources into a single central database.
The initial load will be same as in snapshot replication but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.
XPath is a language defined by the W3C, used to select nodes from XML documents. SQL Server 2000 supports XPath language that helps to retrieve data from XML documents. To do so, SQL server uses a mapping schema to define an XML based view of the data in one or more SQL Server tables and views. The mapping schema is then used to retrieve data.
STUFF: This function is used to replace the part of string with some another string.
Syntax:
STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting Position for overwriting
Length - Length of replacement string
String2- String to overwrite
Example:
SELECT STUFF(‘Arpit',2,2,'mi')
Output: Amit
REPLACE: This function is used to replace all the occurrences of particular string by another string.
Syntax:
REPLACE(String1, String2, String3)
Example:
SELECT REPLACE(‘Arpit Jain’,’i’,’m’)
Output: Arpmt Jamn
If you want to replace the first occurrence of “I”, Replace wouldn't work, because it always replaces ALL occurrences of the string.
SQL Server supports two security (authentication) modes:
Windows Authentication and Mixed mode
Windows Authentication mode connects to an instance of SQL Server through a Windows NT 4.0 or Windows 2000 user account.
Mixed mode (Windows Authentication and SQL Server Authentication) connect to an instance of SQL Server by using either Windows Authentication or SQL Server Authentication.
Users who connect through a Windows NT or 2000 user account can make use of trusted connections in either Windows Authentication mode or mixed mode.
-Suppose we have a EmpDetails table in our database and there are certain number of stored procedures in database. We want to know in which stored procedure(s) table EmpDetails is used.
-We can use following query
SELECT
sys.objects.name, sys.objects.type, sys.objects.type_desc,
sys.objects.schema_id, sys.syscomments.text
FROM sys.objects
INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id
where sys.syscomments.text like '%EmpDetails%'
And type ='P'
SQL Server uses the following steps to process a single SELECT statement:
1. The parser breaks SELECT statement into logical units, such as keywords, expressions, operators, and identifiers.
2. A query tree is built.
3. The query optimizer then determines the best steps that can return fastest result while consuming the fewest resources.
4. The relational engine begins executing the execution plan, requesting the storage engine to pass up data.
5. The relational engine processes the data returned from the storage engine into the result set format and returns the result set to the client.
-In SQL Server there are two system tables “Inserted” and “Deleted” called Magic tables.
-These are not the physical tables but the virtual tables generally used with the triggers to retrieve the inserted, deleted or updated rows.
-When a record is inserted in the table that record will be there on INSERTED Magic table.
-When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.
-When a record is deleted from that table that record will be there on DELETED Magic table.
CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch.
A batch can't contain altered command and then the new columns referenced command of the same table.
The EXECUTE keyword is not required, when an EXECUTE statement is the first statement in a batch
It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure.
The execution of User defined function will be stopped if any error occurred in it. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement.
We can use User defined function in XML FOR clause but we can use stored procedure in XML FOR clause.
It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment.
User defined functions do not return output parameters while stored procedure can return output parameters.
rendc.org
Whether it is technological solutions or user-oriented innovations, We have put together for you the best sources of HR Interview Questions and Answers, Tech Ideas, and Programming Tutorials."
Get all the latest information on rendc.org
© 2022 | All rights reserved.
RENDC
welcome to www.rendc.org
