Designing and Creating Views, Inline Functions, and Synonyms

Group Members  Snober Naseer  Tayba Farooqui  Safa Tariq  Qaria Zainab  Anum Malik

At the end of this lesson you should be able to:  Understand the concept of views  Understand why views are required in a database  Understand how to create and work with the views and inline functions

 In SQL Server, you can use views to store and re-use queries in the database.  View is a database object which contains query.  Views appear for almost all purposes as tables:  Every view is defined by a SELECT statement, which can reference multiple base tables as well as other views.  View can be used as a security mechanism to restrict the data available to the end users.

 Views are created by defining the SELECT statement that retrieves the data to be presented by the view.

 The data associated with views are not physically stored in the view, but it is stored in the base tables of the view.  The data tables referenced by the SELECT statement are known as the base tables for the view

 To create a view, you name the view and then specify the SELECT statement that will constitute the view. CREATE VIEW Sales.OrderTotalsByYear WITH SCHEMABINDING AS SELECT YEAR(O.orderdate) AS orderyear, SUM(OD.qty) AS qty FROM Sales.Orders AS O JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid GROUP BY YEAR(orderdate); GO

 The basic syntax for the CREATE VIEW statement: CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ]

 System Defined Views • Information Schema View • Catalog View • Dynamic Management View  User Defined Views • Simple View • Complex View

 When we create a view on a single table, it is called simple view.  In a simple view we can insert,update,delete data.  When we create a view on more than one table, it is called complex view. CREATE VIEW HumanResources.EmployeeList AS SELECT BusinessEntityID, JobTitle, HireDate, VacationHours FROM HumanResources.Employee; SELECT * FROM HumanResources.EmployeeList

You can add any combination of three view options, as follows:  WITH ENCRYPTION • It specify that the view text should be stored in an obfuscated manner.  WITH SCHEMABINDING • It binds the view to the table schemas of the underlying tables.  WITH VIEW_METADATA • When specified, returns the metadata of the view instead of the base table.

 Every view is a database object, and its name is scoped to the database.  Therefore, in a database, every view name, in its database schema, must be unique, which includes:  Views  Tables  Stored procedures  Functions  Synonyms

 There is only one SELECT statement in the syntax.  A key requirement is that a view will return only one result set so that the view can always appear to most SQL statements as though it were a table.  Only one SELECT statement is allowed in a view.  However, you can combine SELECT statements that return the same result sets by using a UNION or UNION ALL clause in the SELECT statement.

 After you have created a view, you can use the ALTER VIEW command to change the view's structure and add or remove the view properties.  An ALTER VIEW simply redefines how the view works by reissuing the entire view definition. ALTER VIEW Sales.OrderTotalsByYear WITH SCHEMABINDING AS SELECT O.shipregion, YEAR(O.orderdate) AS orderyear, SUM(OD.qty) AS qty FROM Sales.Orders AS O JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid GROUP BY YEAR(orderdate), O.shipregion; GO

 Now you can change the way you SELECT from the view, just as you would a table to include the new column.  You can optionally order the results with an ORDER BY: SELECT shipregion, orderyear, qty FROM Sales.OrderTotalsByYear ORDER BY shipregion;

 You drop a view in the same way you would a table.  Syntax of droping a view is as follow: DROP VIEW Sales.OrderTotalsByYear;

 When you need to create a new view and conditionally replace the old view, you must first drop the old view and then create the new view.  The following example shows one method.  The 'V' parameter in the OBJECT_ID() function looks for views in the current database and then returns an object_id if a view with that name is found. IF OBJECT_ID('Sales.OrderTotalsByYear', 'V') IS NOT NULL DROP VIEW Sales.OrderTotalsByYear; GO CREATE VIEW Sales.OrderTotalsByYear

Through a view you can perform following operations: • Update data • Insert data • Delete data But there are numerous restrictions.  Accordingly, you cannot modify a view column that has an aggregate function, such as SUM(), MAX(), or MIN(), applied to the table's column.  DML statements must reference exactly one table at a time.  You cannot modify a view column that is computed from a UNION/UNION ALL, CROSS JOIN, EXCEPT, or INTERSECT.  You cannot modify a view column whose values result from grouping, such as DISTINCT, or the GROUP BY and HAVING clause.

 Normally, a view is just a definition by a SELECT statement of how the results should be built: no data is stored.  It is possible to create a unique clustered index on a view and materialize the data.  In that case, more than the view definition is stored.  The actual results of the view query are stored on disk, in the clustered index structure.  To be indexed, a view must satisfy a number of important restrictions.

 SQL Server supports the use of views to partition large tables, on one server, in one or more tables across multiple databases, and across multiple servers is called a portioned view.  If you are not able to use table partitioning, you can manually partition your tables and create a view that applies a UNION statement across those tables. The result is called a partitioned view.

Local Partitioned View:  If the tables are in one database or at least on one instance of SQL Server, it is called a Local partitioned view. Distributed Partitioned View:  If the tables are spread across multiple SQL Server instances, the view is called a distributed partitioned view.  If the tables are at least on one instance of SQL Server or in one database, the view is called a Local partitioned view.  If the tables are spread across multiple SQL Server instances, the view is called a distributed partitioned view.

 Views were designed to appear as tables.  To ensure that users in a database can see the metadata for views, grant them VIEW DEFINITION on the views.  To explore view metadata by using T-SQL, you can query the sys.views catalog view, as follows. USE TSQL2012; GO SELECT name, object_id, principal_id, schema_id, type FROM sys.views;

 You can also query the INFORMATION_SCHEMA.TABLES system view, but it is slightly more complex.  Using sys.views is more informative, and from it, you can join to other catalog views such as sys.sql_modules to get further information. SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW';

Views have a number of restrictions, such as the following:  You cannot add an ORDER BY to the SELECT statement in a view  You cannot pass parameters to views.  Similarly, a view cannot reference a variable inside the SELECT statement.  A view cannot create a table, whether permanent or temporary. In other words, you cannot use the SELECT/INTO syntax in a view.  A view can reference only permanent tables; a view cannot reference a temporary table.

A view provides following advantages:  Focuses data for users.  Restrict a user to specific rows in a table.  Hides data complexity.  Simplifies permission management.  Organizes data for export to other applications.  Reduces object size.  data abstraction, the end user is not aware of all the data present in database table.

 Inline table-valued functions allow you to use parameters where views cannot.  However, the body of the function can only be a select statement so your variables must be declared in the header.  Inline functions can be used to achieve the functionality of parameterized views.

 You can create an inline function that would operate just like the Sales.OrderTotalsByYear view, with no parameters, as follows. USE TSQL2012; GO IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL DROP FUNCTION Sales.fn_OrderTotalsByYear; GO CREATE FUNCTION Sales.fn_OrderTotalsByYear () RETURNS TABLE AS RETURN ( SELECT YEAR(O.orderdate) AS orderyear, SUM(OD.qty) AS qty FROM Sales.Orders AS O JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid GROUP BY YEAR(orderdate) ); GO

 The SELECT statement was just as complex as the original Sales. OrderTotalsByYear view.  If you don't need any additional columns from the table, you could actually simplify the function by selecting from the view directly. USE TSQL2012; GO IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL DROP FUNCTION Sales.fn_OrderTotalsByYear; GO CREATE FUNCTION Sales.fn_OrderTotalsByYear () RETURNS TABLE AS RETURN ( SELECT orderyear, qty FROM Sales.OrderTotalsByYear ); GO

 Consider that if you only wanted to see the year 2007, you would just put that in a WHERE clause when selecting from the view.  To make the WHERE clause more flexible, you can declare a variable and then filter based on the variable. SELECT orderyear, qty FROM Sales.OrderTotalsByYear WHERE orderyear = 2007; DECLARE @orderyear int = 2007; SELECT orderyear, qty FROM Sales.OrderTotalsByYear WHERE orderyear = @orderyear

 You can query the function but pass the year you want to see, as follows.  What you effectively have done is created a parameterized view, using an inline function.  The inline function is more flexible than a view, because it returns results based on the parameter value that is supplied.  You don't have to add an additional WHERE clause. SELECT orderyear, qty FROM Sales.fn_OrderTotalsByYear(2007);

To create an inline table-valued function:  Specify parameters (Parameters are optional, but the parentheses that would enclose parameters are not optional).  Add the clause RETURNS TABLE to signal to SQL Server that this is a table- valued function.  Following the AS block, enter a single RETURN statement. This acts like an internal function to return the embedded SELECT statement.  Embed the SELECT statement that will define what you want the function to return as a rowset to the caller.  The semicolon following the last parenthesis is optional, but if present, it must follow the closing parenthesis.

 Inline functions have two significant options, both shared with views: • You can create a function by using WITH ENCRYPTION, making it difficult for users to discover the SELECT text of the function. • You can add WITH SCHEMABINDING, which binds the table schemas of the under lying objects, such as tables or views, to the function. • The referenced objects cannot be altered unless the view is dropped or the WITH SCHEMABINDING option is removed.

 Views are stored T-SQL SELECT statements that can be treated as though they were tables.  A view can be created only in the current database. A view can reference a maximum of 1,024 columns.  If a view depends on a table (or view) that was dropped, SQL Server produces an error message if anyone tries to use the view.  If the new table (or view) structure changes, then the view must be dropped and recreated.

 When a view is created, the name of the view is stored in the sysobjects table.  Information about the columns defined in a view is added to syscolumns table.  Information about the view dependencies is added to the sysdepends table.  In addition, the text of the CREATE VIEW statement is added to the syscomments table.  SQL Server saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created.

 Inline functions can be used to simulate parameterized views.  T-SQL views cannot take parameters.  These original settings are restored when the view is used.  However, an inline table-valued function can return the same data as a view.  An inline table-valued function can accept parameters that can filter the results

SQL Server provides synonyms, which can provide an abstraction layer for all schema-scoped database objects. Synonyms are names stored in a database that can be used as substitutes for other object names.

 A synonym is an alias or alternate name for a table, view, sequence, or other schema object.  They are used mainly to make it easy for users to access database objects owned by other users.

 To create a synonym, you simply assign a synonym name and specify the name of the database object it will be assigned to.  Then the end user can select from Categories without needing to specify a schema. USE TSQL2012; GO CREATE SYNONYM dbo.Categories FOR Production.Categories; GO SELECT categoryid, categoryname, description FROM Categories;

 Then the end user can select from Categories without needing to specify a schema. USE TSQL2012; GO CREATE SYNONYM dbo.Categories FOR Production.Categories; GO SELECT categoryid, categoryname, description FROM Categories;

 The basic syntax for creating a synonym is quite simple. CREATE SYNONYM schema_name.synonym_name FOR object_name

 Synonyms do not store any data or any T-SQL code.  If you don't specify a schema, SQL Server will use the default schema associated with your user name.  When you actually use the synonym in a T-SQL statement, SQL Server will check for the object’s existence.

Synonyms can be used for the following types of objects:  Tables (including temporary tables)  Views  User-defined functions (scalar, table-valued, inline)  Stored procedures

 Following are the major uses of synonyms:  Object invisibility • Synonyms can be created to keep the original object hidden from the user.  Location invisibility • Synonyms can be created as aliases for tables and other objects that are not part of the local database.

 Public synonyms are owned by special schema.  Public synonyms can be referenced by all users in the database.  Private synonyms, unlike public synonyms, can be referenced only by the schema that owns the table or object. CREATE PUBLIC SYNONYM employees For hr.employees; CREATE SYNONYM addresses FOR hr.locations;

 You can drop a synonym by using the DROP SYNONYM statement.  There is no ALTER SYNONYM, to change a synonym, you must drop and recreate it.  The ALL_SYNONYMS (or DBA_SYNONYMS) view provides information on all synonyms in your database. DROP SYNONYM dbo .Categories

 Synonyms can refer to objects in other databases.  Suppose the database DB01 has a view called Sales.Reports, and it is on the same server as TSQL2012.  To query it from TSQL2012, you must write something like the following. SELECT report_id, report_name FROM ReportsDB.Sales.Reports

 To create a synonym, you must have the CREATE SYNONYM permission.  Which inherits from the CONTROL SERVER permission.  After you've created a synonym, you can grant other users permissions.  Such as EXECUTE or SELECT to the synonym.  It depends on the type of object the synonym stands for.

 Synonyms are unusual in that, although they are technically database objects belonging to the same namespace as other database objects.  They don't contain any data or any code.  It’s interesting to compare synonyms with the other database objects.

 Unlike views, synonyms can stand in for many other kinds of objects, not just tables.  Synonyms can provide an abstraction layer: • Allowing you to present a logical view of a system without having to expose the physical names of the database objects to the end user.  If the underlying object is altered, the synonym will not break.  A synonym is just an alternate name for an object, it requires no storage other than its definition.

 Unlike views, synonyms cannot simplify complex logic like a view can simplify complex joins.  Synonyms are really just names.  A view can refer to many tables but a synonym can only ever refer to just one object.  A view can reference another view, but a synonym cannot reference another synonym; synonym chaining is not allowed.

 A synonym is a name that refers to another database object such as a table, view, function, or stored procedure.  No T-SQL code or any data is stored with a synonym.  Only the object referenced is stored with a synonym.  Synonyms are scoped to a database.  Therefore are in the same namespace as the objects they refer to.  Consequently, you cannot name a synonym the same as any other database object.

 Synonyms can be used to provide an abstraction layer to the user by presenting different names for database objects.  You can modify data through a synonym, but you cannot alter the underlying object.  To change a synonym, you must drop and recreate it.  Synonym chaining is not allowed; a synonym cannot refer to another synonym.  Synonyms do not expose any metadata of the objects they reference

Lesson 1: • Inserting Data Lesson 2: • Updating Data Lesson 3: • Deleting Data

 The INSERT INTO statement is used to insert new records in a table.  It is DML statement.  Data manipulation language is used for managing data in database.  DML commands are not auto committed.

 It is possible to write the INSERT INTO statement in two forms.  The first form does not specify the column names where the data will be inserted, only their values:  The second form specifies both the column names and the values to be inserted: INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

 Below is a selection from the "Customers" table: Customer id Customer name Contact name Address City Postal code Country 1 Umer Mr. umer b-23 afzal road Mandra 1243 Pakistan 2 Akmal Mr. akmal house #13 Bilal colony Dehli 5644 India 3 Aslam Mr. aslam 18 civil lines Rawalpindi 4543 Pakistan 4 Uzma Ms.ali 305 - 14th Ave. S. Suite 3B Seattle 7896 USA 5 Barkha Ms. haroon House #23 MC colony Gujrat 4565 Pakistan

 Assume we wish to insert a new row in the "Customers" table.  We can use the following SQL statement: INSERT INTO Customers (Customer name, Contact name, Address, City, Postal Code, Country) VALUES (‘Nazia',‘Ms. barkat ','Haider colony‘ , ‘Karachi',‘4534',‘Pakistan');

 It is also possible to only insert data in specific columns.  The following SQL statement will insert a new row, but only insert data in the "Customer name", "City", and "Country" columns (and the Customer id field will of course also be updated automatically): INSERT INTO Customers (Customer name, City, Country) VALUES (‘Nazia', ‘Karachi', ‘Pakistan');

 Customer table The selection from the "Customers" table will now look like this: Customer id Customer name Contact name Address City Postal code Country 1 Umer Mr. umer b-23 afzal road Mandra 1243 Pakistan 2 Akmal Mr. akmal house #13 Bilal colony Dehli 5644 India 3 Aslam Mr. aslam 18 civil lines Rawalpindi 4543 Pakistan 4 Uzma Ms.ali 305 - 14th Ave. S. Suite 3B Seattle 7896 USA 5 Barkha Ms. haroon House #23 MC colony Gujrat 4565 Pakistan 6 Nazia null null Karachi null Pakistan

 T-SQL supports different statements that insert data into tables in your database.  Those are INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, and others.  With the INSERT VALUES statement, you can insert one or more rows based on value expressions into the target table.  With the INSERT SELECT statement, you can insert the result of a query into the target table.  You can use the INSERT EXEC statement to insert the result of queries in a dynamic batch or a stored procedure into the target table.

 When the database’s recovery model is not set to full, but instead to either simple or bulk logged, the statement uses minimal logging.  This can potentially result in a faster insert compared to when full logging is used.  You have only limited control over the definition of the target table.

 With the statements INSERT VALUES, INSERT SELECT, and INSERT EXEC, you can omit columns that get their values automatically.  A column can get its value automatically if it has a default constraint associated with it, or an IDENTITY property, or if it allows NULLs.  The SELECT INTO statement creates a target table based on the definition of the data in the source query, and inserts the result of the query into the target table.  It is considered a best practice in INSERT statements to specify the target column names in order to remove the dependency on column order in the target table definition.

At the end of this lesson you should be able to:  Use the UPDATE statement to modify rows.  Update data by using joins.  Update data through table expressions.  Update data by using variables.  Describe the implications of the all-at-once property of SQL on updates.

 T-SQL supports the UPDATE statement to enable you to update existing data in your tables.  In this lesson, you learn about both the standard UPDATE statement and also about a few  T-SQL extensions to the standard.

 The standard update statement has the following form: UPDATE <target table> SET <col 1> = <expression 1>,<col 2> = <expression 2>,..., <col n> = <expression n> WHERE <predicate>;

 Standard SQL doesn’t support using joins in UPDATE statements, but T-SQL does.  The idea is that you might want to update rows in a table, and refer to related rows in other tables for filtering and assignment purposes.

EXAMPLE  As an example, suppose that you want to add a 5 percent discount to order lines associated with orders placed by customers from Norway.  The rows you need to modify are in the Sales. My Order Details table.  But the information you need to examine for filtering purposes is in rows in the Sales. My Customers table.  In order to match a customer with its related order lines, you need to join Sales. My Customers with Sales.MyOrders, and then join the result with Sales. My Order Details.

 You should be aware that the proprietary T-SQL UPDATE syntax based on joins can be nondeterministic.  The statement is nondeterministic when multiple source rows match one target row.  Unfortunately, in such a case, SQL Server doesn’t generate an error or even a warning.  Instead, SQL Server silently performs a nondeterministic UPDATE where one of the source rows arbitrarily “wins.”

 With T-SQL, you can modify data through table expressions like CTEs and derived tables. This capability can be useful,  Example:  when you want to be able to see which rows are going to be modified and with what data before you actually apply the update.  Suppose that you need to modify the country and postal code columns of the Sales.  My-Customers table with the data from the respective rows from the Sales. Customers table.  But you want to be able to run the code as a SELECT statement first in order to see the data that you’re about to update.

 Sometimes you need to modify a row and also collect the result of the modified columns into variables.  You can handle such a need with a combination of UPDATE and SELECT statements, but this would require two visits to the row. T-SQL supports a specialized UPDATE syntax that allows achieving the task by using one statement and one visit to the row.  As an example, run the following query to examine the current state of the order line associatedwith order 10250 and product 51. SELECT * FROM Sales. MyOrderDetails WHERE order id = 10250 AND product id = 51;

 T-SQL supports the standard UPDATE statement as well as a few extensions to the standard.  You can modify data in one table based on data in another table by using an UPDATE based on joins.  T-SQL supports updating data by using table expressions.  If you want to modify a row and query the result of the modification, you can use a specialized UPDATE statement with a variable that can do this with one visit to the row.

At the end of this lesson you should be able to:  Delete and truncate statements to delete rows from a table.  Delete statement based on joins.  Delete statement based on table expression.

 Two types of statements are supported by t-sql for deleting data.  T-SQL supports two statements that you can use to delete rows from a table:  These two statements are as follow: • Delete • Truncate

 Use the following code to recreate tables and repopulate them.  with sample data. IF OBJECT_ID('Sales.MyOrderDetails', 'U') IS NOT NULL DROP TABLE Sales.MyOrderDetails; IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL DROP TABLE Sales.MyOrders; IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL DROP TABLE Sales.MyCustomers; SELECT * INTO Sales.MyCustomers FROM Sales.Customers; ALTER TABLE Sales.MyCustomers ADD CONSTRAINT PK_MyCustomers PRIMARY KEY(custid);

ALTER TABLE Sales.MyOrders ADD CONSTRAINT PK_MyOrders PRIMARY KEY(orderid); SELECT * INTO Sales.MyOrderDetails FROM Sales.OrderDetails; ALTER TABLE Sales.MyOrderDetails ADD CONSTRAINT PK_MyOrderDetails PRIMARY KEY(orderid, productid); SELECT * INTO Sales.MyOrders FROM Sales.Orders;

 Delete statement from rows  Predicate is optionally specified.  If you don’t specify a predicate, all rows from the target table are deleted. DELETE FROM <table> WHERE <predicate>; DELETE FROM Sales.MyOrderDetails WHERE productid = 11

 Split your large delete into smaller chunks.  Use DELETE statement with a TOP option that limits the number of affected rows in a loop.  Delete in chunks WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM Sales.MyOrderDetails WHERE productid = 12; IF @@rowcount < 1000 BREAK; END

 The TRUNCATE statement deletes all rows from the target table.  Unlike the DELETE statement,it doesn’t have an optional filter.  After executing the statement, the target table is empty TRUNCATE TABLE Sales.MyOrderDetails;

DELETE: TRUNCATE:  1.For DELETE, SQL Server records in the log the actual data that was deleted.  2. The DELETE statement doesn’t attempt to reset an identity property if one is associated with a column in the target table.  3. The DELETE statement is supported if there’s a foreign key pointing to the table in question as long as there are no related rows in the referencing table.  4. The DELETE statement is allowed against a table involved in an indexed view.  5. The DELETE statement requires DELETE permissions on the target table.  1.For TRUNCATE, SQL Server records information only about which pages were reallocated.(FASTER)  2.Truncate statement does.  3.TRUNCATE is not allowed if a foreign key is pointing to the table—even if there are no related rows in the referencing table, and even if the foreign key is disabled.  4.A TRUNCATE statement is disallowed in such a case.  5.The TRUNCATE statement requires ALTER permissions on the target table.

 T-SQL supports a proprietary DELETE syntax based on joins similar to the UPDATE syntax.  Enable to delete rows from one table based on information that you evaluate in related rows in other tables.  Output:  This statement generates the following output indicating that 122 rows were deleted.  (122 row(s) affected) DELETE FROM O FROM Sales.MyOrders AS O INNER JOIN Sales.MyCustomers AS C ON O.custid = C.custid WHERE C.country = N'USA';

DELETE FROM Sales.MyOrders WHERE EXISTS ( SELECT * FROM Sales.MyCustomers WHERE MyCustomers.custid = MyOrders.custid AND MyCustomers.country = N'USA‘ );

 T-SQL supports deleting rows by using table expressions.  Use a table expression such as a CTE or a derived table to define the rows that you want to delete, and then issue a  DELETE statement against the table expression.  output:  This code generates the following output. • Indicating that 100 rows were deleted.(100 row(s) affected) WITH OldestOrders AS ( SELECT TOP (100) * FROM Sales.MyOrders ORDER BY orderdate, ordered ) DELETE FROM OldestOrders;

 When you are done run the following code for clean-up. IF OBJECT_ID('Sales.MyOrderDetails', 'U') IS NOT NULL DROP TABLE Sales.MyOrderDetails; IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL DROP TABLE Sales.MyOrders; IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL DROP TABLE Sales.MyCustomers;

 With the DELETE statement, you can delete rows from a table, and optionally limit the rows to delete by using a filter based on a predicate.  You can also limit the rows to delete using the TOP filter, but then you cannot control which rows get chosen.  With the TRUNCATE statement, you can delete all rows from the target table. This statement doesn’t support a filter.  The benefit of TRUNCATE over DELETE is that the former uses optimized logging, and therefore tends to be much faster than the latter.

 However, TRUNCATE has more restrictions than DELETE and requires stronger permissions.  T-SQL supports a DELETE syntax based on joins, enabling you to delete rows from one table based on information in related rows in other tables.  T-SQL also supports deleting rows through table expressions like CTEs and derived tables.

Designing and Creating Views, Inline Functions, and Synonyms

Designing and Creating Views, Inline Functions, and Synonyms

More Related Content

What's hot

Similar to Designing and Creating Views, Inline Functions, and Synonyms

Recently uploaded

Designing and Creating Views, Inline Functions, and Synonyms

Editor's Notes