1. What is Distributed Queries in SQL Server?
Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. Microsoft SQL Server supports distributed queries by using OLE DB.
SQL Server users can use distributed queries to access the following:
#. Distributed data stored in multiple instances of SQL Server.
#. Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.
2. What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Once SPs are encrypted, the original text of the SP are not visible. User who has created the SP will need to save the text to be used to create SP somewhere safe to reuse it again or Users that have access to system tables or database files can retrieve the encrypted text.
3. What is user-defined functions in SQL Server?
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
In terms of functionality it is similar to C# or VB.NET functions except the fact that code is written in T-SQL.
4. What is user-defined data type in SQL Server?
User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability.
5. What is a tuple?
A tuple is an instance of data within a relational database
6. What is the use of bit data type and what kind of data is stored into it?
Bit datatype is used to store boolean information like 1 or 0 where 1 is considered as true and 0 is considered as false.
While querying the database we need to use 1 or 0 in the select statement to filter out the data instead of true and false.
7. What is blocking in SQL Server?
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.
8. Difference between varchar and char:
varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.
Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.)
Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc)
9. Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is possible to create a Foreign Constraint.
10. What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.
11. How to get GUID in sql server?
This will return you GUID
12. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction
13. What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
14. What is Log Shipping?
In Microsoft SQL Server, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers
15. What is DTS?
DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.