SQL 2005 - Outer apply, Cross apply

SQL - using apply : Outer apply, Cross apply and example

There are two forms of APPLY: CROSS APPLY and OUTER APPLY.

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.

OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.


Example: We have EmployeeMaster and sales table we want know which through they year the sales person achieve the highest sales and which month

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)


Create Table Sales
(
SalesId int identity(1,1),
EmpId int,
SalesAmount int,
Month varchar(3)
)
Go


Insert into Sales values(1,32000,'Jan')
Insert into Sales values(1,12000,'Feb')
Insert into Sales values(1,42000,'Mar')
Insert into Sales values(1,32000,'Apr')
Insert into Sales values(1,52000,'May')
Insert into Sales values(1,32000,'Jun')
Insert into Sales values(2,2000,'Jan')
Insert into Sales values(2,7000,'Feb')
Insert into Sales values(2,15600,'Mar')
Insert into Sales values(2,9000,'Apr')
Insert into Sales values(2,7500,'May')
Insert into Sales values(2,560,'Jun')

set statistics io on

Normally we will code as

Select E.EmpName,
(select top 1 SalesAmount from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as SalesAmount,
(select top 1 month from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as month1
from EmployeeMaster E

Table 'Sales'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


In SQL 2005, we can use OUTER Apply

Select E.EmpName, A.SalesAmount,[Month] from EmployeeMaster E
OUTER APPLY
(
Select top 1 SalesAmount,[Month] from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc
) A

Table 'Sales'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Result

EmpName SalesAmount Month
-------------------------------------------------- ----------- -----
James 52000 May
May 15600 Mar
Henry NULL NULL


set statistics io off

Refer to above,

1. The query cost for first statement (66%) is double then second statement (34%)
which using OUTER APPLY.
(Highlight the above 2 query in query analyzer and click on Display Estimation Execution Plan)

2. Page Read for Sales (Using set statistics io on) , the first statement is 6 and second
statement is 3. Page read for first statement is double that second statement



Notes:

To use APPLY, the database compatibility level must be at least 90.

Reference:

Using APPLY

Cross Apply and Outer Apply in SQL Server 2005

Isolation level in SQL2005?

If you don't know what is the isolation level in SQL 2005

you can run this command


DBCC useroptions

and exam the row labeled "Isolation Level".

my setting is
"isolation level read committed"

Compare delimiter data from 2 table

Table 1
ID | Country
---------------
1 | French， China，Japan
2 | Malaysia, Singapore

Table 2
ID | Country1
---------------
1 | Japan， China


Result i want :
ID | Country
---------------
1 | French, Malaysia, Singapore


create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values (1,'French, China,Japan')
insert into test1 values (2,'Malaysia, Singapore')
insert into test2 values (1,'Japan, China')


WITH extract1 (country_Attributes) AS
(
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test1
union all
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test2
)
SELECT
country_Attributes.value('/country[1]/Attribute[1]','varchar(25)') AS a,
country_Attributes.value('/country[1]/Attribute[2]','varchar(25)') AS b,
country_Attributes.value('/country[1]/Attribute[3]','varchar(25)') AS c,
country_Attributes.value('/country[1]/Attribute[4]','varchar(25)') AS d
into #temp
FROM extract1;

select country into #temp2 from
(SELECT rtrim(ltrim(colval)) as country
FROM
(SELECT a, b,c,d
FROM #temp) p
UNPIVOT
(ColVal FOR Col IN
(a, b, c,d)
)AS unpvt) tmp
group by country
having count(1)<2

declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + country
from #temp2
print @retstr

drop table #temp
drop table #temp2

SQL 2005 execute as

I need to login as user when i want to test their permission against the database object and most of time I forgot/ never record user password and need request password from them

with SQL 2005, I don't need relogin, I can do this as below

login as sa
execute as user='user'
--select from login table with "user" permission
select * from login

revert
--revert back to sa

Default Value

Add default value in table schema

alter table [table_name]
add CONSTRAINT [DF_tableName_columnname] DEFAULT (0) for columnname

Vulnerabilities in GDI+ Could Allow Remote Code Execution (954593)

SQL 2005 Service Pack

MS08-052 - Vulnerabilities in GDI+ Could Allow Remote Code Execution (954593)
download from here

If your system already upgrade to
"Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (941203) MS08-040"
You need to apply MS08-052
refer to Microsoft Website
"The update for the selected product has been replaced by the update in the following bulletin(s): MS08-052". Selected Product refer to "MS08-040"

Change SQL 2000 port from 1433 to Dynamic Port

Yesterday, I install SQL 20005 as second instance on top of SQL 2000 in my server and testing. After testing successful, I shutdown SQL 2000 and change SQL 2005 port from dynamic to 1433.

Today, I need check job task in SQL 2000 and try to start the SQLServer and it fail. Both SQL server using same port 1433 and only one SQLServer can start.

How to start SQL 2000 without shutdown SQL 20005 now?
First thing in my mind is change port of SQL 2000 to dynamic port but I can't find how to change to dynamic port.

Google and Microsoft article :
To configure your instance of SQL Server to use a dynamic port, follow these steps:
1. Start the Server Network Utility. To do this, do either one of the following:
• Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.
• Click Start, and then click Run. In the Open box, type svrnetcn.exe, and then click OK.
The SQL Server Network Utility dialog box appears.
2. In the SQL Server Network Utility dialog box, click the General tab.
3. In the Instance(s) on this server list, select your instance of SQL Server.

Note If TCP/IP protocol is disabled, enable it now. To do this, click TCP/IP in the Disabled Protocols list box, and then click Enable.
4. In the Enabled Protocols list box, click TCP/IP, and then click Properties.
5. In the Default port box, type 0, and then click OK.
6. Click OK, and then click OK again.
7. Restart the instance of SQL Server.
8. View the SQL Server error logs to verify whether the instance of SQL Server is currently using the dynamic port.

How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port

SQL 20005 Second Instance

We have install SQL 2005 with second instance on top on SQL 2000 Server. When we try connecting to SQL 2005 server through our pc, we can't. I check the SQL login and try connecting from SQL Server itself, connection successful. I shut down SQL 2000 and try telnet new instance from my pc, “telnet servername 1433” fail.

Check and found, If we install second instance of SQL Server, the listen port of SQL server will be dynamic.

We can check from:
1. Protocol for SQL 2005
- From Start->All Programs->Microsoft SQL 2005 Server ->Configuration Tools-> SQL - Server Configuration Manager -> SQL Server 2005 Network Configuration -> TCP/IP
- Double click on TCP/IP, check on IP Address TAB, TCP Port. If TCP port is empty, then SQL will listen on dynamic IP.
- Check on IPAll, TCP Dynamic Ports or TCP port.

2. Check from SQL Server Log
- From Start->All Programs->Microsoft SQL 2005 Server ->SQL Management Studio
- > SQLServerName -> Management -> SQL Server Log
Right Click on SQL Server Logs -> Select on View
- >Look for Source = Server, Message = Server is listening on [ 'any' 1433]
- 1433 is the port SQL Server listens on

SQL 2005 Services Pack 3 coming soon?

SQL 2005 Services Pack 3 coming soon? SQL Server 2005 Service Pack 3 - CTP already release for testing at 27/10/2008.

"The release have been made available for general testing purposes only. Do not deploy the CTP software in production."

Click here for more information

SQL Exam - Second Shot now gives you two incentives to get certified

SQL Exam - Second Shot now gives you two incentives to get certified

I found this in Microsoft Learning Portal

Second Shot now gives you two incentives to get certified!

In the real world, you might need more than one chance at certification. With Second Shot, if you do not pass a Microsoft Certification exam on your first try, we’ll let you retake the exam for free.

As an additional incentive to work toward a Microsoft Certification, from October 15, 2008, to December 31, 2008, if you pass a Microsoft Certification exam on your first try, you get a 25 percent discount on a different exam.

Follow these three easy steps:

1. Register for the Second Shot offer, and take the Microsoft Certification exam by December 31, 2008.

2. If you do not pass the exam, you get a free offer of a retake exam until June 30, 2009.

3. If you do pass your first exam, you get a 25 percent discount on a different exam. You have until February 28, 2009 to take the discounted exam. (Note that you do not receive a free retake offer on this discounted exam if you do not pass it on the first try.)


visit this site
http://www.microsoft.com/learning/mcp/offers/secondshot/default.mspx

Free SQL 2008 training material

Go to SQL 2008!!

Material include :
Overview session
Database Infrastructure Track
Business Intelligence Intro Track Materials
Developer Track
AppCompat & Upgrade Track
Database Infrastructure & Developer Track HOLs VPC Images

and register needed. Most important free registration!!!

URL

SQL 2005 Service Pack 3?

I received daily email from sqlservercentral,. The daily email mention Microsoft won't come out Service Pack 3 for SQL 2005.

Few website also mention about this topic
* Hugo Kornelis: Want a Service Pack? Ask for it!
* Steve Jones: The Easy Poll
* Denis Gabo: Vote for SQL Server 2005 SP3
more information refer to this site

Today, I just read this in blog
"SQL Server 2005 SP3 - coming soon"


It seem a good news for SQL 2005 users

SQL Server 2005 Best Practices Analyzer

SQL 2005 Best Practices Analyzer,
You can download from


SQL Server 2005 Best Practices Analyzer (August 2008)


SQL 2005 Server Development Customer Advisor Team
talking about SQL 2005 Best Practices Analyzer