What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate - All operations can be rolled back.
All the statements (Delete, Truncate and Drop) are logged operations, but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, where as Truncate Table logs only the Page deallocations.Hence, truncate is a little faster than Delete.
You can have a where clause in Delete statement where as Truncate statement cannot have a where clause. Truncate will delete all the rows in a Table, but the structure of the table remains. Drop would delete all the rows including the structure of the Table.
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
Eg:SQL> DROP TABLE emp;
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.
Eg:SQL> TRUNCATE TABLE emp;
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table.
Eg:SQL> DELETE FROM emp WHERE job = 'CLERK';
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
How to import the data into the Share Point list
Add an app->Import Spreadsheet , through this you can import
SQL Server 2005/2008 to 2012 migration guide
There are two approaches when upgrading:
- In-Place: The SQL Server is upgraded where it is currently installed
- Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.
The approach I am going to take is the second, since I have a new hardware and I am going to do a fresh SQL Server 2012 installation.
The migration of the integration services (SSIS) packages
- Open the solution (sln) file with the packages to migrate
- The Visual Studio Conversion wizard appears. It is very simple and after a few next's pressed, the Package Management options appear.
- In the Package Management options select validate upgraded packages, so that the packages are validated and only the ones that pass validation are saved.
- Disable Ignore configurations, so that the configurations are validated during the upgrade process.
- The wizard ends the conversion and you can close it.
- Test each package and verify that it is working as expected.
- The options for migrating are:
- Use only the multidimensional model
- Convert to the tabular model
- Use the multidimensional model for existing cubes and use the tabular model for new developments
- Use the tabular model or the multidimensional model depending on the project requirements
The reporting services migration steps are:
- Open the solution (sln) file with the reports to migrate
- The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish.
- An information message may appear asking if you want to upgrade the report server project to the latest version. Press Yes
- Let the wizard execute.
- Open each data source and test the connection string. If there is an error fix it.
- Deploy the reports
- Test the reports and confirm that everything is working as expected
The general guide for upgrading the database.
- Run upgrade adviser on the databases for the instance you want to upgrade.
- If any issues are found, talk to your dev team/ Vendor for fixing the same.
- If issues are fixed to proceed with the actual upgrade. -- Do check for the other dependencies on the database which might affect the functionality if the instance gets upgraded or something like that.
- Get the downtime for the database.
- Create the latest full backup of the database -- any other strategy will also work, just make sure you can recover the database.
- Take a clone of the server if it's a virtual server to roll back any changes.
- Run the upgrade on the SQL 2005 instance from the 2012 set up. -- do check the pre-requirements for an upgrade to SQL 2012.
- If everything is fine then you have an upgraded instance.
- Also, check the compatibility of the databases.
- If all is fine then congrats you have upgraded your SQL server instance.
Is there a combination of “LIKE” and “IN” in SQL?
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
Use an inner join instead:
SELECT ... FROM SomeTable JOIN (SELECT 'bla%' AS Pattern UNION ALL SELECT '%foo%' UNION ALL SELECT 'batz%' UNION ALL SELECT 'abc' ) AS Patterns ON SomeTable.SomeColumn LIKE Patterns.Pattern
What is the use of an Index in SQL Server?
Relational databases like SQL Server use indexes to find data quickly when a query is processed. Creating the proper index can drastically increase the performance of an application.
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
What is cluster index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of aclustered index contain the data pages
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
what is Nested Trigger
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so When the trigger is fired because of data modification it can also cause another data Modification, thereby firing another trigger. A trigger that contains data modification logic Within itself is called a nested trigger.
A view can be modified if one can insert/update/delete data but if it contains aggregate functions,group by,set operators,some joins,distinct,subqueries,connect by it is not modifiable inherently.Either you can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.The elements of a nested table column in a view cannot be modified directly, but it can be done using INSTEAD OF trigger on that particular column.The trigger fires if a nested table is inserted/updated/deleted and the underlying tables are accordingly modified.In fact this trigger fires before SQL SERVER executes the action which resulted in it firing.If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
what is constraints in sql server and types of constraints
SQL constraints are used to specify rules for the data in a table.
If there is any violation between the constraint and the data action, the action is aborted by the constraint.
In SQL, we have the following constraints:
- NOT NULL – Indicates that a column cannot store NULL value
- UNIQUE – Ensures that each row for a column must have a unique value
- PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
- FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
- CHECK – Ensures that the value in a column meets a specific condition
- DEFAULT – Specifies a default value for a column
Explain DML, DDL, DCL and TCL statements with examples?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.
DDL: DDL stands for Data Definition Language. DDL is used to create and modify the structure of database objects.
Examples: CREATE, ALTER, DROP statements.
DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements
TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements
DML (Data Manipulation Language):
DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.
DML statements include the following:
SELECT – select records from a table
INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DDL (Data Definition Language):
DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.
CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DCL (Data Control Language):
DCL statements control the level of access that users have on database objects.
GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects
TCL (Transaction Control Language):
TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.
BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error