User-Defined Function is a function that can be written as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the requirement.
This returns Scalar value or a table.
Posted Date:- 2021-09-18 07:39:23
The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option must be set to allow the usage of optimistic models.
<> The READ_COMMITTED_SNAPSHOT option is used for the read committed optimistic model.
<> ALLOW_SNAPSHOT_ISOLATION option is used for the snapshot isolation level.
Posted Date:- 2021-09-18 07:38:24
These are few encryption mechanisms in SQL Server to encrypt data in the database:
1. Transparent Data Encryption
2. Symmetric Keys
3. Asymmetric Keys
4. Transact SQL functions
5. Certificates
Posted Date:- 2021-09-18 07:37:27
Hotfixes are single, cumulative software packages applied to live systems. This includes one or more files used to address a problem in a software product. Patches are a programs installed on the machines to rectify the problem occurred in the system and ensured the security of the system. So, basically hotfixes are a kind of patch offered by Microsoft SQL Server to address specific issues.
Posted Date:- 2021-09-18 07:35:55
SQL Server 2019 is available in 5 editions. These are as follows:
* Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
* Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
* Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
* Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
* Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.
Posted Date:- 2021-09-18 07:34:57
The following are three ways to count the number of records in the table:
1. SELECT * FROM TableName;
2. SELECT COUNT(*) FROM TableName;
3. SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND
indexid< 2;
Posted Date:- 2021-09-18 07:33:46
The command DBCC CHECKDB is used to check the physical and logical integrity of all the objects in the mentioned database. To do that, it performs the following operations:
* Runs DBCC CHECKALLOC on the mentioned database.
* On every table and view in the database, the DBCC CHECKTABLE command is executed.
* Runs DBCC CHECKCATALOG on the database.
* Then it validated the contents of every indexed view in the mentioned database.
* It also validates link-level consistency between file system directories and table metadata while storing varbinary(max) data in the file system using FILESTREAM.
* Finally, it validates the Service Broker data in the database.
So, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.
Also, note that DBCC is supported on databases that contain memory-optimized tables but does not provide repair options. This implies that you must regularly back up databases and test those backups.
Posted Date:- 2021-09-18 07:32:19
A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.
The different types of relationships in DBMS are as follows:
1. One-to-One Relationship – Used when a single row in Table A is related to a single row in Table B.
2. One-to-Many Relationship – Used when a single row in Table A is related to many rows in table B.
3. Many-to-Many Relationship – Used when many rows in table A can be related to many rows in table B.
4. Self -Referencing Relationship – Used when a record in table A is related to record in same table.
Posted Date:- 2021-09-18 07:30:40
A developer must check type of information stored, volume of data and the data that will be accessed.
In a scenario, where you are upgrading an existing system, you should analyze the present data, existing data volumes occur, and check the method through which data is accessed, to help you understand the problem areas for design.
In a scenario, where you are using a new system, you have to keep the information about what data will be captured, and what are the constituents of data, and the relationship between the data items.
Posted Date:- 2021-09-18 07:28:44
A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.
To create a user-defined function, refer to the following example:
1. CREATE FUNCTION samplefunc(@num INT)
2. RETURNS TABLE
3. AS
4. RETURN SELECT * FROM customers WHERE CustId=@num
To execute the above-created function, refer to the following command:
1. SELECT * FROM samplefunc(10)
Posted Date:- 2021-09-18 07:27:36
SQL Server 2019 is the latest version of SQL Server that is available in the market and Microsoft launched this on November 4th, 2019 with the support of the Linux O/S.
Posted Date:- 2021-09-18 07:25:34
The physical and logical design plays an important part in the performance of SQL Server-based applications. We need to ensure that correct data is captured in proper tables, the data items have proper relationships between them and data redundancy is reduced. I would also suggest that while you are designing a database, make sure that it is an iterative process to achieve all the required system goals and is under constant observation. Once the database design is set, it is very tough to change the design according to requirement. You can only add new relationships and data items.
Posted Date:- 2021-09-18 07:24:22
SQL Server-based cursors are used when you wish to work on a record at any instance of time, rather than taking all the data from a table as a bulk. However, cursors are not preferred to be used when large volumes of data is present as it affects performance. In a scenario, where it is not possible to avoid cursors, then try to reduce the number of records to process by using a temporary table and then eventually build the cursor from this.
Posted Date:- 2021-09-18 07:23:43
Linked Server is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.
Posted Date:- 2021-09-18 07:23:10
Mirroring in SQL Server is designed to maintain a hot standby server, that is consistent with the primary server in terms of a transaction. Also, the transaction log records are sent from the principal server to the secondary server.
Following are the advantages of Mirroring:
1. Consists of an automatic fail over mechanism.
2. More efficient than Log Shipping and is more robust.
3. The primary server is synced with the secondary server in real-time
Posted Date:- 2021-09-18 07:22:01
SELECT E.Emp_Name
FROM Employee E
INNER JOIN Course Instructor Ci
ON E.Emp_No=Ci.Emp_No
INNER JOIN Course C
ON Ci.Course_ID=C.Course_ID
WHERE C.Course_Name=’EDC’
Posted Date:- 2021-09-18 07:20:05
SELECT * FROM Employee
WHERE Salary>20000 AND Dept_Name=’ECE’
Posted Date:- 2021-09-18 07:19:35
SELECT * FROM Employee
WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’
Posted Date:- 2021-09-18 07:18:59
SELECT * FROM Employee
WHERE Emp_Name LIKE ‘a%’
a% ----> starts with a
%a ----> ends with a
Posted Date:- 2021-09-18 07:18:28
SELECT * FROM Employee
WHERE Age NOT BETWEEN 18 AND 22;
Posted Date:- 2021-09-18 07:18:00
These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows
<> 1204, 1205, 1222 – These flags are used for deadlock Information.
<> 174 – This trace flag increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.
<> 1118 – Forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and 2008) To reduces TempDB contention.
<> 652 – This trace flag disables page prefetching scans.
<> 2566 – Used to run the DBCC CHECKDB command without data purity check, unless the DATA_PURITY option is specified.
Posted Date:- 2021-09-18 07:16:28
The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which command executes and the best time to run the job to avoid the load on the system.
Posted Date:- 2021-09-18 07:14:58
Following are the methods used to protect against SQL injection attack:
* Use Parameters for Stored Procedures
* Filtering input parameters
* Use Parameter collection with Dynamic SQL
* In like clause, user escape characters
Posted Date:- 2021-09-18 07:14:33
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
Posted Date:- 2021-09-18 07:13:35
CDC is termed as "Change Data Capture." It captures the recent activity of INSERT, DELETE, and UPDATE, which are applied to the SQL Server table. It records the changes made in the SQL server table in a compatible format.
Posted Date:- 2021-09-18 07:12:24
Open Cluster Administrator checks the SQL Server group where you can see the current owner. So the current owner is the active node, and other nodes are passive. Because at one time only one node can be active and must be in the passive mode in a two node.
Posted Date:- 2021-09-18 07:08:10
RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.
Posted Date:- 2021-09-18 07:06:13
SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.
SQL Server Reporting Services has the following two basic components.
Processors
Extensions
Architecture of SQL Server Reporting Services
The following diagram shows the architecture of SQL Server Reporting Services.
Posted Date:- 2021-09-18 07:05:32
If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.
Posted Date:- 2021-09-18 07:02:47
Hotfixes are small software patches that are applied to live systems. A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product.
For example - a software bug
A patch is a program installed in the machine to rectify the problem occurred in the system and ensured the security of that system. The hotfix is a Kind of Patches provided by the Microsoft.
In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. Hotfix response proactively against any bug
Posted Date:- 2021-09-18 06:58:47
Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.
Posted Date:- 2021-09-18 06:58:09
Microsoft SQL Server uses a lock hierarchy whenever the data is read or something is changed in the data. Whenever a row is read, SQL Server acquires a shared lock. Similarly, as soon as we change a row, SQL Server acquires an Exclusive lock. These locks are incompatible with each other. So, the INTENT locks are used to indicate at a higher level which locks are applied within a lock hierarchy. There are mainly three kinds of INTENT locks:
1. Intent Shared Lock(IS): This lock is used when you have a shared lock at the row level.
2. Intent Update Lock(IU): The Intent update lock is used when you have an update lock at the row level.
3. Intext Exclusive Lock(IX): This lock is used when you have an exclusive lock at the row level.
Posted Date:- 2021-09-18 06:56:12
Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.
Posted Date:- 2021-09-18 06:54:24
User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
Posted Date:- 2021-09-18 06:53:12
Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.
Posted Date:- 2021-09-18 06:52:50
A table variable is faster in most cases since it is held in memory while a temporary table is stored on a disk. However, when the table variable’s size exceeds memory size the two table types tend to perform similarly.
Posted Date:- 2021-09-18 06:52:01
Log shipping is the process of automating the backup of a database and transaction log file on a primary database server and then restoring them on a standby server. Many servers support this technique for maintaining a backup server, such as Microsoft SQL Server, 4D server, MySQL, and PostgreSQL.
The primary purpose of log shipping is to increase database availability just like replication. On each of secondary database, log backups are applied particularly.
Steps for log shipping process:
* Firstly take a backup of transaction log file on Primary SQL server instance
* Copy the log file on secondary SQL server instance
* Restore the Log backup file onto secondary SQL Server instance
Posted Date:- 2021-09-18 06:46:41
Abstract class:
* It provides a set of rules to implement next class. Rules are provided through abstract methods.
* An Abstract method does not contain any definition.
* When a class contains all functions without the body, it is called as Fully Abstract Class.
* Another class can inherit only one abstract class.
Interface:
* If a class contains all abstract methods, then that class is called Interface.
* Interface support like multiple inheritances.
* An Interface does not contain any implementation
* We can only use public or abstract modifiers.
Posted Date:- 2021-09-18 06:44:47
A @@SPID returns the session ID of the current user process.
Posted Date:- 2021-09-18 06:41:36
Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.
Posted Date:- 2021-09-18 06:41:22
Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.
Posted Date:- 2021-09-18 06:41:00
For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
Posted Date:- 2021-09-18 06:40:36
Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.
Posted Date:- 2021-09-18 06:39:58
A table variable is faster in most cases since it is held in memory while a temporary table is stored on a disk. However, when the table variable’s size exceeds memory size the two table types tend to perform similarly.
Posted Date:- 2021-09-18 06:32:16
Database encryption, CDCs tables – For on the fly auditing of tables, Merge operation, INSERT INTO – To bulk inserts into a table from another table, Hierarchy attributes, Filter indexes, C like operations for numbers, resource management, Intellisense – For making programming easier in SSMS, Execution Plan Freezing – To freeze in place how a query is executed.
Posted Date:- 2021-09-18 06:31:49
CTEs also is known as common table expressions are used to create a temporary table that will only exist for the duration of a query. They are used to create a temporary table whose content you can reference to simplify a query structure.
Posted Date:- 2021-09-18 06:31:32
Primary keys – Unique clustered index by default, doesn’t accept null values, only one primary key per table.
Foreign Key – References a primary key column. It can have null values. Enforces referential integrity.
Unique key – can have more than one per table. It can have null values. It cannot have repeating values. Maximum of 999 clustered indexes per table.
Posted Date:- 2021-09-18 06:31:13
A derived attribute is an attribute that is obtained from the values of other existing columns and does not exist on its own. A derived persistent attribute is a derived attribute that is stored. A computed attribute is an attribute that is computed from internal system values.
Posted Date:- 2021-09-18 06:30:42
By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
Posted Date:- 2021-09-18 06:29:28
Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.
Posted Date:- 2021-09-18 06:28:21