Tutorials,Articles,Algorithms,Tips,Examples about Database

Change page: < 1 2 3 4 5 6 7 8 9 10 11 12 > | Displaying page 6 of 12, items 201 to 240 of 441.

Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Views

Views are objects within SQL Server that provide a layer of abstraction between the end users and the underlying schema. Rather than directly access the base table, the users query a "virtualized table" that holds only the data that is specific to their needs.

Creating and Managing Views in SQL Server 2008 : Indexed Views

You establish indexed views by creating a unique clustered index on the view itself, independent of the member tables that it references. The creation of this unique index transforms a view from an object that is virtual in nature to one that has physical storage associated with it.

Transact-SQL in SQL Server 2008 : Row Constructors

SQL Server 2008 provides a new method to insert data to SQL Server tables, referred to as row constructors. Row constructors are a feature that can be used to simplify data insertion, allowing multiple rows of data to be specified in a single DML statement.

Transact-SQL in SQL Server 2008 : GROUP BY Clause Enhancements

The old-style CUBE and ROLLUP syntax is still supported for backward-compatibility purposes but is being deprecated. You should convert any existing queries using the pre-2008 WITH CUBE or WITH ROLLUP syntax to the new syntax to ensure future compatibility.

Creating and Managing Views in SQL Server 2008 : Partitioned Views

Partitioned views are used to access data that has been horizontally split, or partitioned, across multiple tables. These tables can be in the same or different databases—or even spread across multiple servers.

Creating and Managing Views in SQL Server 2008 : Managing Views, Data Modifications and Views

After creating your view, you can manage the view via T-SQL or the View Designer. The T-SQL commands for managing views are the ALTER VIEW and DROP VIEW statements.

Creating and Managing Views in SQL Server 2008 : Creating Views

One of the most amazing features of the View Designer is the capability to render a SQL statement into its graphical form. You can copy T-SQL into the SQL pane, and the View Designer reverse-engineers the tables into the Diagram pane, giving you a graphical display of the query

Creating and Managing Views in SQL Server 2008 : Definition of Views & Using Views

Views are a logical way of viewing data in the underlying physical tables. They are tied to a SELECT statement that retrieves data from one or more tables or views in the same database or a different database.

Transact-SQL in SQL Server 2008 : Insert over DML

The OUTPUT clause allows you to return data from a modification statement (INSERT, UPDATE, MERGE, or DELETE) as a result set or into a table variable or an output table.

Transact-SQL in SQL Server 2008 : MERGE Statement

With the MERGE statement, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table, all in just a single statement, minimizing the number of times that rows in the source and target tables need to be processed

SQL Server 2008 : Transact-SQL Programming - The TABLESAMPLE Clause

You can use TABLESAMPLE to quickly return a sample from a large table when the sample does not have to be a truly random sample at the level of individual rows.

SQL Server 2008 : Transact-SQL Programming - TRY...CATCH Logic for Error Handling

SQL Server 2005 also introduced the TRY...CATCH construct, which you can use within T-SQL code to provide a more graceful mechanism for exception handling than was available in previous versions of SQL Server.

SQL Server 2008 : Transact-SQL Programming - The APPLY Operator

The APPLY relational operator allows you to invoke a table-valued function once per each row of an outer table expression. You specify APPLY in the FROM clause of a query, similarly to the way you use the JOIN operator. APPLY can take two forms: CROSS APPLY and OUTER APPLY.

SQL Server 2008 : Transact-SQL Programming - PIVOT and UNPIVOT

SQL Server 2005 added the PIVOT clause to T-SQL. A typical analytical use of the PIVOT command is to convert temporal data into categorized data to make the data easier to view and analyze.

SQL Server 2008 : Transact-SQL Programming - Ranking Functions

SQL Server 2005 introduced four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions allow you to analyze data and provide ranking values to result rows of a query.

SQL Server 2008 : Transact-SQL Programming - Common Table Expressions

Nonrecursive CTEs are ANSI SQL-99 compliant expressions that provide T-SQL coding flexibility. However, for each nonrecursive CTE, there is usually another T-SQL construct that can be used to achieve the same results (for example, derived tables)

SQL Server 2008 : Transact-SQL Programming - The OUTPUT Clause

In SQL Server 2005, the INSERT, UPDATE, and DELETE statements were enhanced to support an OUTPUT clause to be able to identify the actual rows affected by the DML statement.

SQL Server 2008 : Transact-SQL Programming - TOP Enhancements

The TOP clause allows you to specify the number or percentage of rows to be returned by a SELECT statement. SQL Server 2005 introduced the capability for the TOP clause to also be used in INSERT, UPDATE, and DELETE statements.

SQL Server 2008 : Transact-SQL Programming - The max Specifier

In SQL Server 2000, the most data that could be stored in a varchar, nvarchar, or varbinary column was 8,000 bytes. If you needed to store a larger value in a single column, you had to use the large object (LOB) data types: text, ntext, or image.

SQL Server 2008 : T-SQL Tips and Tricks (part 3) - Generating T-SQL Statements with T-SQL & De-Duping Data with Ranking Functions

The system catalogs in SQL Server 2008 contain a wealth of information you can use to save a lot of time and effort when generating SQL statements that need to be run repeatedly on a large number of database objects or when trying to build a column list for a query.

SQL Server 2008 : T-SQL Tips and Tricks (part 2) - Using CONTEXT_INFO & Working with Outer Joins

An outer join is used to return all the rows from the specified outer table (specified with LEFT OUTER, RIGHT OUTER, or FULL OUTER), even if the other table has no match. Rows returned from the outer table that have no corresponding match in the inner table display the value NULL for any columns retrieved from the inner table.

SQL Server 2008 : T-SQL Tips and Tricks (part 1) - Date Calculations & Sorting Results with the GROUPING Function

As you may know, working with the datetime data type in SQL Server can be a bit of a challenge. You probably already know how to use the datepart() function to extract specific components of a date (for example, year, month, day).

SQL Server 2008 : General T-SQL Performance Recommendations

How you write T-SQL queries can often have an effect on the performance of those queries. The following sections provide some general guidelines to keep in mind to help ensure that you are getting optimal performance from your queries.

SQL Server 2008 : General T-SQL Coding Recommendations (part 2) - Avoid SQL Injection Attacks When Using Dynamic SQL & Comment Your T-SQL Code

SQL injection is usually the result of faulty application design—usually an unvalidated entry field in the application user interface.

SQL Server 2008 : General T-SQL Coding Recommendations (part 1) - Provide Explicit Column Lists & Qualify Object Names with a Schema Name

In SQL Server 2005, the behavior of schemas was changed from earlier versions of SQL Server. SQL Server 2005 introduced definable schemas, which means schemas are no longer limited to database usernames only.

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Using Extended Stored Procedures

Extended stored procedures are invoked and managed similarly to regular stored procedures. You can grant and revoke permissions on extended stored procedures as you do for normal stored procedures

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Installing and Using .NET CLR Stored Procedures

Prior to SQL Server 2005, the only way to extend the functionality of SQL Server beyond what was available using the T-SQL language was to create extended stored procedures or COM components

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Using Dynamic SQL in Stored Procedures

SQL Server allows the use of the EXEC statement in stored procedures to execute dynamic SQL statements. This capability allows you to do things such as pass in object names as parameters and dynamically execute a query against the table name passed in

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Stored Procedure Performance

By default, SQL Server sends a message back to the client application after each statement is completed within the stored procedure to indicate the number of rows affected by the statement.

SQL Server 2008 : Using Remote Stored Procedures

The processing done by the remote stored procedure is, by default, not done in the local transaction context. If the local transaction rolls back, modifications performed by the remote stored procedure are not undone.

SQL Server 2008 : Using Temporary Tables in Stored Procedures

Temporary tables are commonly used in stored procedures when intermediate results need to be stored in a work table for additional processing. Local temporary tables created in a stored procedure are automatically dropped when the stored procedure exits

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Nested Stored Procedures

Stored procedures can call other stored procedures, and any of those procedures can call other procedures, up to a maximum nesting level of 32 levels deep.

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Using Cursors in Stored Procedures

Cursors in SQL Server can be declared as local or global. A global cursor defined in a stored procedure is available until it is deallocated or when the connection closes. A local cursor goes out of scope when the stored procedure that declared it terminates or the procedure scope changes.

SQL Server 2008 : T-SQL Stored Procedure Coding Guidelines

Transact-SQL (T-SQL) stored procedures should be treated just like reusable application code. You should follow these suggested guidelines to ensure that your stored procedures are solid and robust

SQL Server 2008 : Leveraging the Microsoft Sync Framework

Microsoft Sync Framework (MSF) is a complete platform enabling synchronization of data, files, feeds, or other critical information between clients, peers, and servers over common transport protocols and across networks

SQL Server 2008 : Using ADO.NET Data Services

ADO.NET Data Services (ADODS) is a platform for providing SQL Server data to websites, RIAs (such as Silverlight and Flash applications), and other Internet clients over standard HTTP using modern web development conventions.

SQL Server 2008 : Developing with LINQ to SQL (part 2) - Uncovering LINQ to SQL with Linqpad

To use Profiler, launch the application from the Windows Start menu, create a new trace using the File menu (select New Trace), and connect to your database of choice

SQL Server 2008 : Developing with LINQ to SQL (part 1)

LINQ enables developers to write code in either C# or VB.NET using the same set of syntactic conventions to query object collections (known as LINQ to Objects), XML documents (known as LINQ to XML), SQL Server data (known as LINQ to SQL), and other queryable resources.

Getting Comfortable with ADO.NET 3.5 and SQL Server 2008

To start coding with ADO.NET and SQL Server, you first need to connect to an instance of SQL Server. To do this, you need a connection string. A connection string is simply a string literal that contains all the parameters necessary to locate and log in to a server in a semicolon-delimited format

SQL Server System and Database Administration : System Views

System views are virtual tables that expose metadata that relates to many different aspects of SQL Server.