1) What is the use of SQL.BSQ Script?
SQL.BSQ Script is run to create base tables,roles at the database creation time in sql server database.
sql.bsq script creates the following roles in the database - connect,resource,dba,select_catalog_role,delete_catalog_role,execute_catalog_role
Role is the grouping of privileges that can be granted to users that restricts access at database level
As privileges are grouped they can be granted and revoked simultaneously
2) In SQL Server how will you export Logins to Report RPT file?
SQL Server offers unique benefit of exporting all its logins onto a report file which is usually stored in rpt format. This is an important task to be completed before SQL Server update install (or) upgrading SQL server as a whole. This proactive measure, preserves logins in case of upgrade failure (Mostly updates have less impact on logins but can't be discounted)
Perform the following steps to import logins onto report file
1) First try to find details on logins in server as follows
select * from syslogins
2) Now highlight the above SQL, right click and choose option that says, results to file. Click Execute
3) Save the report file that is of rpt format
To get back results in SSMS choose results grid
3) What are many different Startup Options in SQL Server?
Startup options are the values that are installed as part of system registry when we install sql server software using microsoft sql server installer. These parameters can be modified using SQL Server configuration manager
Changes to startup options require system restart
Startup options resemble init.ora/spfile.ora parameters in oracle and session variables in mysql databases
To access sql server configuration manager go to :
Start->Microsoft SQL Server 2016->SQL Server Configuration Manager
4) Why does SQL Server Differential Restore Fails with lsn missing error?
SQL server comes with interesting differential backup and restore option using which with a fullbackup, followed by differential backup and transactional logs we will be able to perform restore and recovery
Differential backups are cumulative (ie) created from last full backup and hence consume space. Design backup destination of differential backups acordingly
Using SQL Server Management Studio (SSMS) we get LSN chain breaking problem on a differential restore following full restore. This is a result of bug in this interface. Try using Query window and perform differential restore using query and we see that it works fine
5) How to enable Filestream At Database Level Using T-SQL in a sql server?
By default filestream feature is not enabled at database level upon installation or upgrade of SQL Server. filestream is needed if we wish to make use of interesting SQL Server feature like Filetables. Here is the simple step to enable filestream feature at database level using T-SQL
exec sp_configure filestream_Access_level,2
reconfigure
go
The sp_configure package will modify the values and enable filestream access. The output will show that filestream_access_level parameter value has been changed from original 0 to latest 2 value
6) Are shared locks compatible with exclusive locks?
Exclusive locks are used with DML whereas shared locks are related to read only operations like select
7) What is syntax of query? Which statement is used to query DB?
select [column|* for all columns] from tablename;
8) How to get details on pattern in sql server DB?
The simple function PATINDEX can be used
PATINDEX('pattern','string') provides details on occurence of pattern in the given string
9) What function is used for case conversion in sQL SERVER?
lower() - converts the given data to lower case
upper() - converts given data to upper case
10) How do you concatenate two or more strings in sql server?
SQL server comes with a function concat() that helps us accomplish it real easy
11) What happens when ansi_nulls are set to on?
In case of a select statemetn that has where condition column=null will return zero even if there are null values in column name. To set this use
set ansi_nulls=off
12) How is charindex() used?
This function searches an expression (set of words) for another word and returns details on its starting position
13) What is basic difference between delete and truncate?
Delete is DML and truncate is DDL and executes fast as it is irreversible and doesnt log informaiton on every row deleted
14)Give details on some sql server ranking functions
rank(), dense_rank(),over(),ntile(), are good examples of this
15) What happens during creation of a unique constraint?
An uniqueindex is created by default when a unique contraint is created. This prevents duplicate keys. Same is the case with primary key creation
16) What is SQL Server Piecemeal Restore ?
This is an interesting restore and recovery option that allows quick availability of DB
How does piecemeal restore expedite DB availability?
Piecemeal restore starts with initial restore of important filegroups followed by secondary filegroups restore later on
We can use with partial option to perform piecemeal restore
Query the master_files table from sys database to get more details during this piecemeal restore
select name,state_desc,create_lsn,redo_start_lsn from sys.master_files where database_id=db_id('DBNAME')
