1) Give details on SQL Server Enable Filestream At Database Level Using SQL Server Configuration Manager:-
SQL Server configuration manager the tool that comes as an integral part of SQL Server installation helps us enable filestream feature. All Programs->Microsoft SQL Server 2012->Configuration Tools->SQL Server Configuration Manager->Database (say SQL EXPRESS)
Right click and choose properties. Click on Filestream tab. Enable filestream, enable I/O access on windows share and remote clients if needed. Click Apply then ok
This will enable filestream access across the SQL Express (for our example) database.
This is similar to executing the following T-SQL query at SQL Server Management Studio ->File-> New Query
exec sp_configure filestream_access_level,2
reconfigure
go
2) How to add adventureworks to sql server database?
Many features in SQL Server 2012 database can be tested easily if we have a good dataset in place. As it is often not possible to simulate a dataset microsoft has come up with simple easy to use solution AdventureWorks2012 database.
This is a easy plug-in type database that can be easily attached to current SQL Server 2012 infrastructure.
The first step is to download AdventureWorks2012 datafile from microsoft website
http://msftdbprodsamples.codeplex.com/releases/view/55330
1) Launch SQL Server Management Studio 2012
2) Right-click on database and click Attach option
3) Add the Adventureworks.mdf file from DATA folder of MSSQLSERVER
4) Click and remove the missing logfile
5) Click okie and the database automatically gets added to database explorer in SQL Server Management Studio
6) Expand the AdventureWorks2012 and make sure that all tables exist
7) In New query prompt, type use AdventureWorks2012; go will take us to this database
SQL interview is a common thing among all the technology and non-technology professionals. Here are some questions to make your preparation easy 1) Is it possible to Create Table No Column :? Table is the basic storage object in oracle database. It is illogical to create a table without any column (as we create it just to store data). Still I was curious to know the output/error while creating table without any column. Here is the result SQL> create table test_nocolumn( ); create table test_nocolumn( ) * ERROR at line 1: ORA-00904: : invalid identifier Here is the output while creating table with no column in oracle database. 2) Give details on object system privilege in databases :- Privileges are permissions which restrict the level of access of each and every user using Oracle database. This makes database access safe and secure. Only users having permission will access the database. Also all the users can't perform all the tasks. This restriction makes database a safe space to work with.There are two major set of privileges : 1) Object Privilege 2) System privileges Object Privilege - SELECT,INSERT,UPDATE,DELETE Major DML,SELECT privileges against many different objects like tables,views,indexes,synonym in the database lets the user perform the actions with the privilege they have. A simple demonstration of this privilege is going to be SQL> grant select on test to info; - Grants select permission to user A SQL> grant delete on test to user B; - B can delete the rows in test. He will not be able to select the rows System Privilege - Create Table,Alter System,Create User, Create Session DDL operations including object creation happens with the CREATE system privilege. Also if we need to tweak operations at SYSTEM level we need Alter System privilege 3) What is the reason behind ORA-00947: not enough values? I created a table in Oracle database and tried inserting values into it. I tried inserting two columns in a table made of three column. This poped the following error SQL> insert into employee values(1,'info'); insert into employee values(1,'info') * ERROR at line 1: ORA-00947: not enough values SQL> desc employee; Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NOT NULL NUMBER(38) EMPLOYEE_NAME VARCHAR2(20) SALARY NUMBER(38) SQL> insert into employee values(1,'info',10000); 1 row created. SQL> insert into employee values(1,'info',10000); What is the reason behind ORA-00907: missing right parenthesis? Oracle Database INT datatype can't have value specified. If we want to specify the length we can make use of number instead of INT. SQL> create table test (id int(10), phone number(10)); create table test (id int(10), phone number(10)) * ERROR at line 1: ORA-00907: missing right parenthesis The issue gets fixed if we rewrite the query as follows SQL> create table test (id number(10),phone number(10)); Table created. How to enable and disable triggers in oracle? Oracle triggers are PL/SQL procedures that are automatically fired by the database based on specified events.Triggers are used for performing audit and security related tasks.They can be fired before or after DML statements , system events(database startup, database shutdown, users logging in, users logging off). CREATE TRIGGER ... - This is the syntax When created triggers are enabled by default. We can temporarily disbale the triggers using the following command : SQL> ALTER TRIGGER trigger_name DISABLE; We can re-enable the trigger using the following command : SQL>ALTEr TRIGGER trigger_name ENABLE; Give details on union operator in sql :- SQL statements has a UNION clause support that lets us combine the information in two tables. they function the same way as mathematical set operator union. The output is going to be the combination of all the information in both the tables SQL> select * from t1; ID NAME ---------- ---------- 1 lr SQL> select * from t2; LOCATION PHONE ---------- ---------- globe 45623456 SQL> select name from t1 union select location from t2; NAME ---------- globe lr T-SQL to Check if Filestream feature is enabled at database level SQL Server :- Filetables the latest feature in SQL Server 2012 makes use of filestream feature at database level. Before creating filetables it is mandatory to know if the filestream is enabled at database level. Here is the simple T-SQL that helps us determine the same if exists( select * from sys.database_files where type_desc='FILESTREAM') PRINT 'Filestream Configured For Datbase' else print 'Filestream Not configured for database' If it is not enabled, the t-SQL cna be run in SSMS new query window to enable filestream execute sp_configure filestream_access_level,2 reconfigure How will you shrink database in sql server environment? Shrink database is often an option to reclaim the unused space from the database. This de-fragmentation will help us shrink the database to its originally created maximum size We can perform the shrink operation using SQL Server management studio and using T-SQL statement The dbcc command is used to perform the shrink operation of a database Say, to shrink a database by name demo issue the following command dbcc shrinkdatabase(demo,30); - This will shrink data and log files in demo database and will let 30% of space free in these database How will you perform PERFORM SQL SERVER BACKUP ONTO NETWORK SHARE? SQL Server offers maintenance plans that automate the full, differential, transactional log backups by creating jobs and automating using schedule. As such there comes a situation to backup the SQL Server databases onto network share directly Formally, this is not supported by microsoft. However, this is possible and can be done under guidance of sql server expert with approval from microsoft support personnel. Here are the simple steps 1) Enable the xp_cmdshell. This will change the value from 0 to 1 and the settings are made permanent using reconfigure command sp_configure ‘xp_cmdshell’,1; Go RECONFIGURE WITH OVERRIDE; Go 2) Set up the network share as backup destination exec xp_cmdshell ‘net use X: networkshare-name’; VErify the existence of drive as follows: exec xp_cmdshell ‘Dir X:’ 3) In computer look for X: and make sure this is accessible via unc path 4) Create maintenance plans, specify x: as backup destination and test backups Adding Logfile to AdventureWorks2012 Database: It is evident that we can simply attach AdventureWorks2012.mdf datafile to a database and make it accessible. However, logfile is not naturally added as a part of this porcess. Adding logfile to AdventureWorks2012 database is simple and straight forward by using alter databas edatabasename add log file command alter database AdventureWorks2012 add log file ( name=Adventureworks2012log1, filename='C:SQLSERVERDBNAMEMSSQL11.DBNAMEMSSQLDATAAdventureWorks2012.ldf',size=1024KB) Create New Database with Filestream Enabled SQL Server 2012: Here is the simple T-SQL query that cna be run in SQL Server 2012 Management studio and helps us create a database with filestream enabled create database TestFSDatabase on primary ( name=myTEstFSDatabaseDB, filename='C:SQLSERVERDBNAMEMSSQL11.DBNAMEMSSQLDATATestFSDatabase.mdf'), filegroup TestFSDatabase contains filestream( name=TestFSDatabase, filename='C:SQLSERVERDBNAMEMSSQL11.DBNAMEMSSQLDATATestFSDatabase') LOG ON ( name=TestFSDatabaseLog, filename='C:SQLSERVERDBNAMEMSSQL11.DBNAMEMSSQLDATATestFSDatabase.log') GO