SQL SERVER

The following applications have been developed using SQL Server.

Click on a title to view the source code.

Select Statement Examples
The Basic SELECT Statement, Selecting Specific Columns for Every Row, Selecting Every Column for Every Row, Using the WHERE clause to specify rows returned in the result set, Negating a Search Condition, Combining Search Conditions, Keeping your WHERE Clause Unambiguous, Using BETWEEN for Date Range Searches, Using Comparisons, Checking for NULL Values, Returning Rows based on a List of Values, Using Wildcards with LIKE, Using the ORDER BY clause, Using the TOP keyword with Ordered Results, Using the GROUP BY Clause, Using GROUP BY ALL, Selectively Query Grouped data using HAVING, Using DISTINCT to Remove Duplicate Values, Using DISTINCT in Aggregate Functions, Using Column Aliases, Using SELECT to Create a Script, Performing String Concatenation, Creating a Comma Delimited List using SELECT, Using the INTO Clause, Using Subqueries to Check for the Existence of Matches, Using INNER Joins, Using OUTER Joins, Using CROSS Joins, Performing Self-Joins, Using Derived Tables, Combine Result Sets with UNION, Using CROSS APPLY, Using OUTER APPLY, Using the TABLESAMPLE to Return Random Rows, Normalizing Data with UNPIVOT, Returning Distinct or Matching Rows using EXCEPT and INTERSECT, Summarizing Data with CUBE, Using GROUPING with CUBE, Summarizing Data with ROLLUP, Using Join Hints, Using Query Hints, Using Table Hints, Using a Non-Recursive Common Table Expression (CTE), Using a Recursive Common Table Expression (CTE)

Insert, Update, Delete Examples
Inserting a Row into a Table, Inserting a Row Using Default Values, Explicitly Inserting aValue Into an IDENTITY Column, Inserting a Row into a Table with a Uniqueidentifier column, Inserting Rows Using an INSERT...SELECT statement, Inserting data from a Stored Procedure Call, Updating a Single row, Updating Rows based on a FROM and WHERE Clause, Updating Large Value Data Type Columns, Inserting or Updating an Image File Using OPENROWSET and BULK, Deleting Rows, Truncating a table, Using the OUTPUT clause with INSERT,UPDATE, DELETE, Deleting Rows in Chunks

Transactions, Locking, Blocking, Deadlocking Examples
Using Explicit Transactions, Displaying the Oldest Active Transaction with DBCC OPENTRAN, Viewing Lock Activity, Using SET LOCK TIMEOUT

Table Examples
Creating a Table, Adding a Column to an Existing Table, Changing a Column Definition, Creating a Computed Column, Dropping a Table Column, Dropping a Table, Viewing Collation Metadata, Designating a Column's Collation, Creating a Table with a Primary Key, Adding a Primary Key Constraint to an Existing Table, Creating a Table with a Foreign Key Reference, Adding a Foreign Key to an Existing Table, Creating Recursive Foreign Key References, Allowing Cascading Changes in Foreign Keys, Using the IDENTITY Property During Table Creation, Using DBCC CHECKIDENT to View and Correct IDENTITY SeedValues, Using the ROWGUIDCOL Property, Creating a Unique Constraint, Adding a UNIQUE Constraint to an Existing Table, Adding a CHECK Constraint to an Existing Table, Disabling and Enabling a Constraint, Using a DEFAULT Constraint During Table Creation, Dropping a Constraint from a Table, Using a Temporary Table for Multiple Lookups Within a Batch, Creating a Table Variable to Hold a Temporary Result Set, Implementing Table Partitioning, Determining the Location of Data in a Partition, Adding a New Partition, Removing a Partition, Moving a Partition to a Different Table, Removing Partition Functions and Schemes, Placing a Table on a Filegroup

Index Examples
Create a Table Index, Enforce Uniqueness on Non-Key Columns, Create a Composite Index, Define Index Column Sort Direction, View Index Meta Data, Disable an Index, Dropping Indexes, Change an Existing Index with DROP_EXISTING, Intermediate Index Creation in Tempdb, Control Parallel Plan Execution for Index Creation, Allow User Table Access During Index Creation, Using an Index INCLUDE, Use PAD_INDEX and FILLFACTOR DROP INDEX, Disable Page and/or Row Index Locking, Creating an Index on a Filegroup, Implement Index Partitioning

Full-Text Search
Creating a Full-Text Catalog, Creating a Full-Text Index, Modifying a Full-Text Catalog, Modifying a Full-Text Index, Dropping a Full-Text Catalog, Dropping a Full-Text Index, Retrieving Full-Text Catalog and Index Metadata, Using FREETEXT to Search Full-Text Indexed Columns, Using CONTAINS for Word Searching, Use CONTAINS to Search with Wildcards, Using CONTAINS to Search for Inflectional Matches, Using CONTAINS for Searching Results by Term Proximity, Returning Ranked Search Results by Meaning, Returning Ranked Search Results by Weighted Value

Views
Creating a Basic View, Querying the View Definition, Reporting on Database Views, Refreshing a View’s Definition, Modifying a View, Dropping a View, Modifying Data Through as View, Encrypting a View, Creating an Indexed View, Forcing the Optimizer to use an Index for an Indexed View

Function Examples
Returning the Average of Values, Returning Row Counts, Finding the Lowest and Highest Values from an Expression, Returning the Sum of Values, Using Statistical Aggregate Functions, Using Mathematical Functions, Converting a Character Value to ASCII and Back to Character, Returning Integer and Character Unicode Values, Finding the Start Position of a String Within Another String, Finding the Start Position of a String Within Another String Using Wildcards, Determining the Similarity of Strings, Taking the Leftmost or Rightmost Part of a String, Padding a number for business purposes, Determining the Number of Characters or Bytes in a String, Replacing A Part of a String With Another String, Stuffing a String into a String, Changing Character Values to Lower,Upper, and Proper Case, Removing Leading and Trailing Blanks, Repeating an Expression N Number of Times, Repeating a Blank Space N Times, Outputting an Expression in Reverse Order, Returning a Chunk of an Expression, Replacing a NULL Value with an Alternative Value, Performing Flexible Searches Using ISNULL, Returning the First Non NULL Value in a List of Expressions, Returning a NULL Value When Two Expressions Are Equal: Otherwise Return the First Expression, Returning the Current Date and Time, Incrementing or Decrementing a Date’s Value, Finding the Difference between Two Dates, Displaying the String Value for Part of a Date, Displaying the Integer Value For Part of a Date Using DATEPART, Displaying the Integer Value For Part of a Date using YEAR, MONTH,DAY, Converting Data Types, Performing Date Conversions, Evaluating Whether An Expression Is a Date or Is Numeric, Using an Incrementing Row Number, Returning Rows by Rank, Returning Rows by Rank Without Gaps, Using NTILE, Using SQL Server’s First Day of the Week Setting, Viewing the Language Used in the Current Session, Viewing and Setting Current Connection Lock Timeout Settings, Displaying the Nesting Level for the Current Stored Procedure Context, Returning the Current SQL Server Instance Name and SQL Server Version, Returning the Current Connection’s Session ID (SPID), Returning Number of Open Transactions, Retrieving the Rows Affected By the Previous Statement, Using System Statistical Functions, Displaying Database and SQL Server Settings, Returning the Current Database ID and Name, Returning a Database Object Name and ID, Returning the Application and Host for the Current User Session, Reporting Current User and Login Context, Returning the Last Identity Value, Returning an Identity Column’s Seed and Incrementing Value, Creating a New uniqueidentifier Value

Conditional and Cursor Examples
Using the CASE to Evaluate a Single Input Expression, Using CASE to Evaluate Boolean Expressions, Using IF...ELSE, Using RETURN, Create a Stored Procedure that raises an error, Using WHILE, Using GOTO, Using WAITFOR, Creating and Using Transact-SQL Cursors

Stored Procedures
Creating a Basic Stored Procedure, Creating a Parameterized Stored Procedure, Using OUTPUT Parameters, Modifying a Stored Procedure, Dropping Stored Procedures, Executing Stored Procedures Automatically at SQL Server Startup, Reporting Stored Procedure Metadata, Encrypting a Stored Procedure, View the procedure's text, Using EXECUTE AS to Specify the Procedure’s Security Context, RECOMPILE a Stored Procedure Each Time It Is Executed

User-Defined Functions
Creating Scalar User-Defined Functions, Creating Inline User-Defined Functions, Creating Multi-Statement User-Defined Functions, Modifying User-Defined Functions, Viewing UDF Metadata, Dropping User-Defined Functions, Using Scalar UDFs to Maintain Reusable Code, Using Scalar UDFs to Cross Reference Natural Key Values, Replacing Views with Multi-Statement UDFs, Creating and Using User-Defined Types, Identifying Columns and Parameters that Use User-Defined Types

Triggers
Creating an AFTER DML Trigger, Creating An INSTEAD OF DML Trigger, Using DML Triggers and Transactions, Controlling DML Triggers Based on Modified Columns, Viewing DML Trigger Metadata, Creating a DDL Trigger that Audits Database-Level Events, Creating a DDL Trigger that Audits Server-Level Events, Modifying a Trigger, Enabling and Disabling Table Triggers, Limiting Trigger Nesting, Controlling Trigger Recursion, Setting Trigger Firing Order, Dropping a Trigger

XML
Creating xml Data Type Columns, Inserting xml Data into a Column, Validating XML Data Using Schemas, Retrieving XML Data, Modifying XML Data, Using XML Indexes, Using FOR XML, Using OPENXML

Error Handling
Viewing System Error Information, Invoking an Error Message Using RAISERROR, Old Style Error Handling, Error Handling with TRY...CATCH, Nesting TRY...CATCH Calls

Permissions
Reporting SQL Server Assignable Permissions, Managing Server Permissions, Managing Database Permissions, Managing Schemas, Managing Schema Permissions, Managing Object Permissions, Managing Schemas, Managing Schema Permissions, Managing Object Permissions, Determining a Current Connection’s Permissions to a Securable, Reporting the Permissions For a Principal by Securable Scope, Changing Securable Ownership, Allowing SQL Logins to Access Non-SQL Server Resources

Encryption
Using a Function to Encrypt By Passphrase, Backing Up and Restoring a Service Master Key, Creating, Regenerating, and Dropping a Database Master Key, Backing up and Restoring a Database Master Key, Removing Service Master Key Encryption from the Database Master Key, Creating an Asymmetric Key, Viewing Asymmetric Keys in the Current Database, Modifying the Asymmetric Key’s Private Key Password, Encrypting and Decrypting Data using an Asymmetric Key, Dropping an Asymmetric Key, Creating a Symmetric Key, Viewing Symmetric Keys in the Current Database, Changing how a Symmetric Key is Encrypted, Using Symmetric Key Encryption and Decryption, Dropping a Symmetric Key, Creating a Database Certificate, Viewing Certificates in the Database, Backing Up and Restoring a Certificate, Managing a Certificate’s Private Key, Using Certificate Encryption and Decryption

Create a Temporary Table
How to create a temporary SQL table

Find a Value in a SQL Server Database
Find a value in all columns of all tables in a database

Find Missing Records in a SQL Server Table
Search for missing records in a table

Find Text in Stored Procedures of a SQL Server Database
Search all the stored procedures in a database and locate specific text

Find Columns and Tables in a SQL Server Database
List all tables within a SQL Server database, List all columns within a SQL Server database, Find specific columns within a SQL Server database, Find specific tables within a SQL Server database

Performing a Basic Full Backup
Naming and Describing your Backups and Media, Configuring Backup Retention, Striping Backup Sets, Using a Named Backup Device, Mirroring Backup Sets, Performing a Transaction Log Backup, Using COPY ONLY Backup Sets, Performing a Differential Backup, Backing up Individual Files or Filegroups

Creating and Querying Database Snapshots
Removing a Database Snapshot, Recovering Data with a Database Snapshot


© 1997-2017 codehacker.com - All Rights Reserved            RapidSSL