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
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’)
1) Are shared locks compatible with exclusive locks?
Exclusive locks are used with DML whereas shared locks are related to read only operations like select
2) What is syntax of query? Which statement is used to query DB?
select [column|* for all columns] from tablename;
3) 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
4) 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
5) 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
6) 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
7) How is charindex() used?
This function searches an expression (set of words) for another word and returns details on its starting position
8) 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
9)Give details on some sql server ranking functions
rank(), dense_rank(),over(),ntile(), are good examples of this
10) 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
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 2012->SQL Server Configuration Manager