This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the main role of Model Database in SQL Server?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
What is a collation in SQL Server?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
What is Use of @@ SPID in SQL Server?
A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed.
What is cloud computing?
Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.
How do I rename my SQL Server instance?
SQL Server doesn’t support renaming the instance name directly so you can uninstall the existed instance and install it with the new name and change the computer name and rename the part of the instance name that corresponds to the computer name.
How do I store data in multiple languages within one database?
The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and nvarchar(max) data types, instead of their non-Unicode equivalents, char, varchar, and text. Unicode is a standard for mapping code points to characters.
How do I configure SQL Server to enable distributed transactions via Linked Server?
To enable distributed transactions across different servers, you need to configure MS DTC on both servers and some of your SQL Server server options.
Please run “services.msc” from windows run dialog to check DTC service is running or not.
Please make sure this service could access the network and could receive connection from other server.
Please enable the XACT_ABORT.
What is recompilation?
When the cached execution plan for a query cannot be used so the procedure recompiles.
How do I configure Windows Firewall to allow remote connections using TCP/IP protocol for SQL Server?
On the server on which SQL Server runs, we need to find out which ports that SQL Server is listening and add which to Windows Firewall Inbound Exceptions.
Firstly, we need to add TCP port on which SQL Server service is listening to the Windows Firewall Inbound Exceptions so that the client can make connections to SQL Server over Windows Firewall.
Secondly, if we do not specify TCP port in the connection string and SQL Server is not listening on the TCP 1433 port, we need to add UDP 1434 port on which SQL Server Browser Services is listening to the Windows Firewall Inbound Exceptions.
What is parallelism?
SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly. When a single query runs of multiple CPUs is known as query parallelism.
What is the default query threshold for parallelism?
The query optimizer decides to utilize multiple SPIDS running on different processors to query / transfer data. Default threshold is 5 seconds.
How to start the SQL Server with minimal configuration?
If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.
What is auto close option?
AUTO CLOSE option:
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
When set to OFF, the database remains open after the last user exits.
What do you know about the MSDTC service?
MSDTC (aka Microsoft Distributed Transaction Control) is a piece of software which a lot of people use, but they don’t really know what it does, or how it works. MSDTC is used by SQL Server and other applications when they want to make a distributed transaction between more than one machine.
What do you know about the port UDP 1434?
UDP port 1434 is used for SQL Server named instances. The SQL Server Browser service listens on this port for incoming connections to a named instance. The service then responds to the client with the TCP port number for the requested named instance.
What do you know about the SQL Server Browser Service?
While commonly thought of as “one of those other SQL Server services”, life without the SQL Server Browser Service would be a bit more of a hassle. Its main purpose is to aid in the detection of SQL Server instances and provide information necessary for connecting to them. It listens on UDP port 1434 and returns port numbers and version information for requested instances on a machine.
When a user connects to the default instance, the connection is passed to TCP 1433 automatically. When connecting to a named instance and the port number is unknown, the first connection made is to the Browser Service at UDP 1434, which then returns the proper port number on which to initiate the TCP connection.
What is the Instance-aware and Instance-unaware services in SQL Server?
Instance-aware services are associated with a specific instance of SQL Server.
- SQL Server
- SQL Server Agent
- Analysis Services
- Reporting Services
- Full-text search
Instance-unaware services are shared among all installed SQL Server instances.
- Integration Services
- SQL Server Browser
- SQL Server Active Directory Helper
- SQL Writer
