What is a correlated sub-query?

What is a correlated sub-query? A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from
the outside query, creating a situation similar to a loop.

Correlated subquery in sql Part 60

SELECT NAME,

(SELECT SUM(QUANTITYSOLD) FROM tblProductSales Where ProductID=tblProducts.Id) as QtySold FROM tblProducts

What to choose for performance SubQuery or Joins Part 62

To drop query cache:


CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS; – -clears query cache
GO
DBCC FREEPROCACHE; – clears execution plan cache
GO

Coalesce function

Coalesce function in sql server Part 16



select Id, COALESCE(FirstName, MiddleName, LastName) as Name FROM  tblEmployee

Get the first Nonnull value.
------------------------------------------------------------------------------------------------------------------------------------------------------------

COALESCE (Transact-SQL)



Running a complex example
In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.


SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
   salary,
   commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO

CAST and CONVERT (Transact-SQL)

CAST and CONVERT (Transact-SQL)


Output Collation
When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the
input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default.
For more information, see Collation Precedence (Transact-SQL).
To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

SQL Server Collation Fundamentals

Batch Processing (OLTP) - Background or Asynchronous

Batch Processing (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

Source: SQL Server 2012



Batch (sometimes also called Background or Asynchronous):
--------------------------------------------------------

Batch (sometimes also called Background or Asynchronous) processing is generally characterized by the absence of user interactivity.
Few systems are purely transactional or batch-oriented; even systems that are designed primarily as batch systems may have an online component.
For example:


Most month-end or year-end application processing is performed in batches.

Utility (electric, gas) bills are generally produced via batch processing for mailing to clients, while current month usage computation for review by the
online user is likely to be computed independently online and not via batch processing, even though most of the application logic may be very similar in
batch and online processing.


Best Practices:

Batch processing may require scheduling help. The SQL Server 2008 Books Online article Implementing Jobs1 describes how SQL Server Agent can be used for
scheduling jobs.


...MORE INFO IS THIER

Consider using partitioned tables for large extraction, transformation, and load (ETL) operations. Loading data to an empty partition, creating the indexes,
and switching the partition into the table can be faster than normal insert operations by orders of magnitude. For more information, see the following



SQL 2008 Series: SQL Partitions / partitioning

XML

<Movies>

<Movie MovieName=”ABC” TicketPrice=”10” Time=”3rdshow”>

<Title>ABC By ABC corp </Title>

<Actors>

<Actor>

<First_Name>Xxxxx</First_Name>

<Last_Name>Yyyyy</Last_Name>

</Actor>

<Actor>

<First_Name>Aaaaa</First_Name>

<Last_Name>Bbbbb</Last_Name>

</Actor>

</Actors>

</Movie>

<Movie MovieName=”DEF” Price=”100”>

<Remark>A Great Movie To Be Released!</Remark>

<Title>DEF By ABC corp </Title>

<Actors>

<Actor>

<First_Name>Zenith</First_Name>

<Last_Name>Jackson</Last_Name>

</Actor>

</Actors>

</Movie>

<Movie MovieName=”GHI” Price=”85” Edition=”2ndshow”>

<Title>GHI By ABC corp </Title>

<Actors>

<Actor>

<First_Name>Corolla</First_Name>

<Last_Name>Manson</Last_Name>

</Actor>

<Actor>

<First_Name>Mindy</First_Name>

<Last_Name>Kailing</Last_Name>

</Actor>

<Actor>

<First_Name>Jennifer</First_Name>

<Last_Name>Anderson</Last_Name>

</Actor>

</Actors>

</Movies>