1. How to shrink a database in Mssql?
In MSSQl you can shrink database by using SQL Server Management Studio or Transact-SQL.We have described both method below
Using SQL Server Management Studio
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand Databases, and then right-click the database that you want to shrink.
Point to Tasks, point to Shrink, and then click Database.
Database
Displays the name of the selected database.
Current allocated space
Displays the total used and unused space for the selected database.
Available free space
Displays the sum of free space in the log and data files of the selected database.
Reorganize files before releasing unused space
Selecting this option is equivalent to executing DBCC SHRINKDATABASE specifying a target percent option. Clearing this option is equivalent to executing DBCC SHRINKDATABASE with TRUNCATEONLY option. By default, this option is not selected when the dialog is opened. If this option is selected, the user must specify a target percent option.
Maximum free space in files after shrinking
Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99.
Click OK.
By Using Transact-SQL
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This quesry uses DBCC SHRINKDATABASE to decreases the size of the data and log files in the UserDB database and to allow for 10 percent free space in the database.
DBCC SHRINKDATABASE (UserDB, 10);
GO
Source : https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-2017
2. How to check database version of mssql?
Steps to identify the SQL Server version
Log in to the Windows Server system hosting your SQL Server database.
Open a command prompt window.
Execute the following command:
SQLCMD -S servername\instancename
where servername is your server name and instancename is the SQL instance name.
Enter select @@version and press .
At the 2 > prompt, type go and press .
The SQL version running on the server displays in the dialog box.
Repeat step 3 through step 5 for each additional instance.
Source: https://support.solarwinds.com/Success_Center/Orion_Platform/Knowledgebase_Articles/How_to_identify_your_SQL_Server_version_and_edition
3. How to escape single quotes and wild cards in mssql?
To escape single quotes ( ' ) in MS SQL all you need to do is add two single quotes instead of one.
Example:
INSERT into name_column ([name]) VALUES ('R"Rams')
To escape wild cards you can use default square braces "[]" like this- [%] or [_] or define your own escape character by using the keyword ESCAPE at the end of your where clause.
4. Explain Magic tables in MSSQL?
SQL Server allows you to define a Magic Table. Magic Tables are invisible tables or virtual tables. You can see them only with the help Triggers in SQL Server. Magic Tables are those tables which allow you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server.
These are the two Magic Tables:
Inserted
Deleted
Generally Magic Tables are invisible tables, we can only see them with the help of Trigger's in SQL Server.
Source: https://www.c-sharpcorner.com/UploadFile/rohatash/magic-tables-in-sql-server-2012/
5. List the Logical operators in SQL Server?
The logical operators are basically used to test the truths of conditions.
ALL – It returns true if all the set of operations are true.
AND – It returns true if the Boolean expressions are true.
IN – It returns true if the operand is equal to one of the lists of expressions.
ANY – It returns true if any of the set of comparisons is true.
BETWEEN – It returns true if the operand is within a range.
EXISTS – It returns true if the subquery contains any rows.
LIKE – It returns true if the operand matches a pattern
NOT – It reverses the values of any Boolean operator.
OR – It returns true if either of the Boolean expression is true.
SOME – It returns true if some of the set of comparisons are true.
Source: https://www.onlineinterviewquestions.com/mssql-interview-questions/
6. What is default port used by mssql?
The default port of SQL server is 1433.
Top comments (0)