مباحث مربوط به SQlServer2005

[سید مسعود موحد]

سلام بر دوستان عزیز
با اجازه بزرگترها میخواستم که مبحثی هم در این مورد در مباحث بانکهای اطلاعاتی باز شود و مقالات خود را دوستان در این قسمت قرار دهند
با تشکر - موحد

[سید مسعود موحد]

اولین چراغ رو خودم روشن میکنم دوستان کم لطفی نکنید و شما ها هم ادامه بدین






WITH simpleExample as
(
SELECT 'hi' AS columnName
)
SELECT columnName
FROM simpleExample





حالا کاربردش را با یک مثال ببینید کاهش کد نویسی و سرعت بالای این نوع کد که بنام CTEs ها در SQL معرفی میشوند




--In SQL Server 2000
SELECT cast(c.LastName + ', ' + c.FirstName as varchar(30)) as SalesPerson
,
--YEAR TO DATE SALES
(SELECT amount
FROM ( SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID) as YTDSalesPerson
where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) as YTDSales,
--PERCENT OF TOTAL
(SELECT amount
FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID) as YTDSalesPerson
where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) /
(SELECT sum(amount)
FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID) as YTDSalesPerson
) as percentOfTotal,
--COMPARE TO QUOTA
(SELECT amount
FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID)as YTDSalesPerson
where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) -
salesPerson.SalesQuota as MetQuota
FROM sales.SalesPerson as salesPerson
join HumanResources.Employee as e
on salesPerson.salesPersonId = e.employeeId
join Person.Contact as c
on c.contactId = e.contactId







-- SQL Server 2005 CTE syntax
WITH YTDSalesPerson
AS
(
SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID
),
SalesPersonInfo
AS
(
SELECT salesPersonId, SalesQuota as salesQuota,
cast(c.LastName + ', ' + c.FirstName as varchar(30)) as SalesPerson
FROM sales.SalesPerson as s
JOIN HumanResources.Employee as e
on s.salesPersonId = e.employeeId
JOIN Person.Contact as c
on c.contactId = e.contactId
)
SELECT SalesPersonInfo.SalesPerson,
(SELECT amount
FROM YTDSalesPerson
WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID)
as YTDSales,
(SELECT amount
FROM YTDSalesPerson
WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID)
/ (SELECT sum(amount) FROM YTDSalesPerson) as percentOfTotal,
(SELECT amount
FROM YTDSalesPerson
WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID) -
salesPersonInfo.SalesQuota as MetQuota
FROM SalesPersonInfo



با مقاسیه دو کد متوجه میشوید که کد دوم چقدر ساده تر و خوانا تر میباشد.

[Elham_gh]

ممنون آقای موحد. من که هنوز رو SQL Server2005 , سوئیچ نکردم. پس فقط خوانندم .
اما یه سوال . شما اولش گفتین CTE ها. اول اینکه CTE مخفف چیه؟ دوم اینکه شامل چه چیرهای دیگه میشه؟
ممنون.

[سید مسعود موحد]

سرکار الهام
من خودم SQl2005 را با کتابهایی که از همین سایت دانلود کردم شروع کرده ام و نام کتاب هم
Pro Sqlserver 2005 میباشد و این (‍‍Common Table Expressions(CTS مخفف این جمله است
در آینده نزدیک اگر نفسی بود و در خدمت دوستان بودم میخوام بعد از عید نوروز مبحث Report Service رو شروع کنم اگر در حال حاضر کسی این مبحث را شروع کرده که میتواند بصورت موازی با همین مبحث جلو ببریم.

[بابک زواری]

دوست عزیز کار خوبی رو شروع کردید ولی ایکاش یک مقدار توضیحاتتون بیشتر باشه
خوندن توضیحات شما باعث میشه که سریع تر مطلب رو بگیریم تا اینکه ما کد رو بخونیم
تجزیه تحلیلش کنیم بعد متوجه بشیم.
رعایت حال ما کم سوادها رو هم بکن .
ولی کارت بیسته ؛ ادامه بده

[سید مسعود موحد]

دستور Except و INTERSECT

دستور اول EXCEPT مقادیری که در SELECT اول وجود دارد و در دومی وجود ندارد را نشان میدهد
و دستور INTERSECT که فقط مقادیری را که در هر دو SELECT وجود دارد را نشان میدهد

کاربرد دستورات




CREATE TABLE projectPerson
(
personId VARCHAR(10),
projectId VARCHAR(10),
PRIMARY KEY (personId, projectId)
)
go
INSERT INTO projectPerson VALUES ('joeb','projBig')
INSERT INTO projectPerson VALUES ('joeb','projLittle')
INSERT INTO projectPerson VALUES ('fredf','projBig')
INSERT INTO projectPerson VALUES ('homerr','projLittle')
INSERT INTO projectPerson VALUES ('stevegr','projBig')
INSERT INTO projectPerson VALUES ('stevegr','projLittle')
go
So we can see that joeb worked on projBig and projLittle. Now we can write the following
queries. Using UNION, we could see who worked on one project or both projects:


SELECT personId
FROM projectPerson
WHERE projectId = 'projBig'
UNION
SELECT personId
FROM projectPerson
WHERE projectId = 'projLittle'
which returns the following:
personId
----------
fredf
homerr
joeb
stevegr
Next, if you want to see who worked only on projLittle, but not projBig, this was pretty ugly in
SQL Server 2000.
SELECT personId
FROM projectPerson as projLittle
WHERE projectId = 'projLittle'
AND NOT EXISTS ( SELECT *
FROM projectPerson as projBig
WHERE projBig.projectId = 'projBig'
and projBig.personId = projLittle.personId)
In 2005, you can run the following:
--worked on projBig but not projLittle
SELECT personId
FROM projectPerson
WHERE projectId = 'projLittle'
EXCEPT
SELECT personId
FROM projectPerson
WHERE projectId = 'projBig'
This returns the following:
personId
----------
homerr
Finally, say we want to see who worked only on both projects. In 2000, we need to run the query
we did for INTERSECT, and UNION the result with the opposite, which is just too messy to be of value
here. In 2005, the query is very straightforward:
SELECT personId
FROM projectPerson
WHERE projectId = 'projBig'
INTERSECT
SELECT personId
FROM projectPerson
WHERE projectId = 'projLittle'


This returns
personId
----------
joeb
stevegr

[سید مسعود موحد]

دستور Synonyms
این دستور دیگه خیلی خدا کرده SQL2005 رو با این دستور میتوانید همانند Oracle و DB2 برای خود یک Alias تعریف کنید
این Alias بروی لایه های زیر ایجاد میشود


• Tables (including temporary tables)
• Views
• Stored procedures (CLR and T-SQL)
• Replication filter procedures
• Extended stored procedures
• Table-valued functions (CLR and T-SQL)
• SQL scalar functions (CLR and T-SQL)
• User-defined aggregate functions (CLR

برای ایجاد کافی است که آنرا با دستور CREATE ایجاد کنید با SYNTAX زیر

CREATE SYNONYM <synonym name> FOR <object name>

و برای پاک کردن آنرا DROP نمایید با Syntax زیر
DROP SYNONYM

[leily2000]

سلام
از مطالبی که فرستاده بودید مشخص بود که در database مهارت زیادی دارید چون به نکته هایی اشاره کرده بودید که ریز ولی مفید بودند.:تشویق:
می خواستم بپرسم شما با MySql کار کرده اید ؟ و اگر کار کرده اید آیا در مورد ساختن Temporary table ها تجربه ای داشته اید؟ چون من برای ساختن آنها دچار مشکل شده ام.
ممنون می شم اگر در این مورد مرا راهنمایی کنید.

[سید مسعود موحد]

نه دوست عزیز من mysql کار نکردم

[Mohammad S]

سلام
با تشکر از مطالب مفیدتون
چرا دستور اجرای Intersect در SQL Server Personal 2000 کار نمی کنه؟
Incorrect syntax near the keyword 'INTERSECT'.