22 Haziran 2010 Salı

FIX: Error Code 29506, While installing SQL Management Studio Express on Windows Vista x64


Merhaba windows Vista 64 bit işletimli bir makinaya SQL Server Management Studio kurar iken karşınıza yukarıdaki resimdeki gibi bir hata çıkarsa yapmanız gerekenler aşağıaki gibi

1. CMD komut istemcisini yönetici olarak çalıştırın .
2. daha sonra kurulum paketini buradan komut olarak çalıştırın :
Örnek
C:\>E:\Downloads\SSMS_x64.msi
3.Sonra gelen yönergelere göre işleminiz tamamlayın

11 Haziran 2010 Cuma

IndexFragmentation

Merhaba Index fragmantasyonu ve ayrıntılı bilgiler bigün lazım olur
Veri tabanınızda indexlerin berbat durumda olup olmadığını,external veya internal fragmentation gerçekleşip gerçekleşmediğini anlamak istiyor iseniz aşağıdaki karışık T-SQL komutunu çalıştırmanız gerekiyor.

SELECT OBJECT_NAME(dt.object_id), si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'), NULL, NULL, NULL,'DETAILED')
WHERE index_id <> 0) as dt --does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id


Çıkan sonuca baktığımızda
· Eğer avg_fragmentation_in_percent değeri 10’un üstündeyse o index için external fragmentation vardır denir.
· Eğer avg_page_space_used_in_percent değeri 75’in altında ise bu durumda da internal fragmentation vardır denir.

Tabloya bakarak AdventureWorks veri tabanının indexlerinin halinin pekde içaçıcı olmadığı söylenebilir.


ayrıntılı anlatımı aşağıdaki sayfada bulabilirsiniz
http://www.msegitim.net/sql/IndexFragmentation.aspx


http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

8 Haziran 2010 Salı

SQL Server Alt Toplam Almak için güzel örnekler

Merhaba Arkadaşlar
internette gezinirken ilgimi çeken bir kaç güzel SQL
Kaynak site : http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/


Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the architecting of subtotal, total and grand total queries.

Grand Total definition- the sum of the TOTALs of several groups of numbers.

—SQL grand total simple, quick syntax - isnull is used to make report readable
SELECT isnull(convert(VARCHAR,ProductID),'GRAND TOTAL') AS ProductID,
sum(Quantity) AS 'Total In Inventory'
FROM AdventureWorks2008.Production.ProductInventory
WHERE ProductID >= 800
GROUP BY ProductID WITH ROLLUP
ORDER BY ProductID;
GO
/* Partial results
ProductID Total In Inventory
996 970
997 153
998 155
999 194
GRAND TOTAL 65709
*/

------------

-- SQL sales subtotal, total, grand total - simple logic - month, year, all times
SELECT isnull(convert(VARCHAR,Year(OrderDate)),'GRAND TOTAL') AS YEAR,
CASE
WHEN YEAR(OrderDate) IS NOT NULL
THEN isnull(convert(VARCHAR,Month(OrderDate)),'YEARLY TOTAL')
ELSE ''
END AS MONTH,
-- SQL currency formatting
'$'+CONVERT(varchar,SUM(TotalDue),1) AS [MONTHLY SALES SUBTOTAL]
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY Year(OrderDate),
Month(OrderDate) WITH ROLLUP
ORDER BY YEAR,
MONTH
/* Partial results

YEAR MONTH MONTHLY SALES SUBTOTAL
2004 4 $4,722,890.74
2004 5 $6,518,825.23
2004 6 $6,728,034.99
2004 7 $56,178.92
2004 YEARLY TOTAL $32,196,912.42
GRAND TOTAL $140,707,584.82
*/

------------

First complex example: GROUP BY..WITH ROLLUP is used to generate subtotal at the Quarter level, total at the Year level and grand total for all times AdventureWorks Cycle in business. While the output is easily understandable, the total query is on the complex side.

------------
-- Microsoft SQL grand total, total, subtotal generation with T-SQL GROUP BY ROLLUP
------------
-- MSSQL grouping function
USE AdventureWorks2008;

SELECT TotalType = CASE
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)) = 1
THEN 'GRAND TOTAL'
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)) = 1
THEN 'TOTAL - YY'
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) +
right('0' + CONVERT(VARCHAR,month(OrderDate)),2)) = 1
THEN 'SUBTOTAL - QQ'
ELSE ''
END,
[Year] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4),''),
[Quarter] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),
''),
[Month] = COALESCE(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2),
''),
Purchases = '$' + CONVERT(VARCHAR,SUM(SubTotal),1)
FROM Purchasing.PurchaseOrderHeader
GROUP BY left(CONVERT(CHAR(8),OrderDate,112),4),
left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),
left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2) WITH ROLLUP
ORDER BY GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)),
[Year],
GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)),
[Quarter],
GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2)),
[Month]

/* Results

TotalType Year Quarter Month Purchases
2001 2001Q2 2001Q205 $103,895.82
SUBTOTAL - QQ 2001 2001Q2 $103,895.82
TOTAL - YY 2001 $103,895.82
2002 2002Q1 2002Q101 $299,239.98
2002 2002Q1 2002Q102 $700,406.63
2002 2002Q1 2002Q103 $328,572.46
SUBTOTAL - QQ 2002 2002Q1 $1,328,219.07
2002 2002Q2 2002Q204 $646,975.84
2002 2002Q2 2002Q205 $298,745.12
2002 2002Q2 2002Q206 $106,491.00
SUBTOTAL - QQ 2002 2002Q2 $1,052,211.96
2002 2002Q3 2002Q307 $655,405.08
2002 2002Q3 2002Q308 $116,119.65
2002 2002Q3 2002Q309 $499,186.65
SUBTOTAL - QQ 2002 2002Q3 $1,270,711.38
2002 2002Q4 2002Q410 $186,121.65
2002 2002Q4 2002Q411 $388,868.98
SUBTOTAL - QQ 2002 2002Q4 $574,990.63
TOTAL - YY 2002 $4,226,133.03
2003 2003Q1 2003Q103 $131,485.79
SUBTOTAL - QQ 2003 2003Q1 $131,485.79
2003 2003Q2 2003Q205 $641,097.40
2003 2003Q2 2003Q206 $924,127.80
SUBTOTAL - QQ 2003 2003Q2 $1,565,225.19
2003 2003Q3 2003Q307 $105,014.51
2003 2003Q3 2003Q308 $98,304.85
2003 2003Q3 2003Q309 $5,100,678.73
SUBTOTAL - QQ 2003 2003Q3 $5,303,998.10
2003 2003Q4 2003Q410 $3,449,392.92
2003 2003Q4 2003Q411 $1,775,204.15
2003 2003Q4 2003Q412 $3,424,287.10
SUBTOTAL - QQ 2003 2003Q4 $8,648,884.17
TOTAL - YY 2003 $15,649,593.25
2004 2004Q1 2004Q101 $4,266,022.62
2004 2004Q1 2004Q102 $4,137,584.18
2004 2004Q1 2004Q103 $4,704,085.56
SUBTOTAL - QQ 2004 2004Q1 $13,107,692.37
2004 2004Q2 2004Q204 $5,172,365.20
2004 2004Q2 2004Q205 $5,729,967.42
2004 2004Q2 2004Q206 $5,522,963.61
SUBTOTAL - QQ 2004 2004Q2 $16,425,296.24
2004 2004Q3 2004Q307 $6,710,223.01
2004 2004Q3 2004Q308 $6,824,989.46
2004 2004Q3 2004Q309 $743,151.66
SUBTOTAL - QQ 2004 2004Q3 $14,278,364.13
2004 2004Q4 2004Q410 $1,020.00
SUBTOTAL - QQ 2004 2004Q4 $1,020.00
TOTAL - YY 2004 $43,812,372.74
GRAND TOTAL $63,791,994.84
*/
------------

Second complex example: summary query generates Subtotal, Total and Grand Total for monthly, yearly and all times sales. In data warehousing, the dates are referred to as date (or time) dimension with 3 levels.

------------
-- GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (month) mssql select query
------------
-- Microsoft T-SQL derived table rpt generates the report - select from select
-- The outer query does the final filtering and sorting
USE AdventureWorks;

SELECT *
FROM
(
SELECT YY=COALESCE(CONVERT(varchar,YEAR(OrderDate)),''),
MM=COALESCE(LEFT(CONVERT(varchar,OrderDate,111),7),''),
ORDERS = COUNT(*),
SALES = '$'+CONVERT(varchar,SUM(TotalDue),1),
GRPMM = CASE WHEN
GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 0
AND GROUPING(YEAR(OrderDate)) = 1
THEN 'SUBTOTAL' ELSE '' END,
GRPYY= CASE WHEN GROUPING(YEAR(OrderDate)) = 0
AND GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1
THEN 'TOTAL' ELSE '' END,
GRPALL = CASE WHEN
GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1
AND GROUPING(YEAR(OrderDate)) = 1
THEN 'GRAND TOTAL' ELSE '' END

FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), LEFT(CONVERT(varchar,OrderDate,111),7)
WITH CUBE
) rpt
WHERE
GRPMM != '' OR GRPYY !='' OR GRPALL !=''
ORDER BY
CASE WHEN GRPALL!= '' THEN 3
WHEN GRPYY != '' THEN 2
ELSE 1 END,
YY, MM
GO
/* Partial results

YY MM ORDERS SALES GRPMM GRPYY GRPALL
2004/04 2127 $4,268,473.54 SUBTOTAL
2004/05 2386 $5,813,557.45 SUBTOTAL
2004/06 2374 $6,004,155.77 SUBTOTAL
2004/07 976 $56,178.92 SUBTOTAL
2001 1379 $12,693,250.63 TOTAL
2002 3692 $34,463,848.44 TOTAL
2003 12443 $47,171,489.55 TOTAL
2004 13950 $28,887,306.04 TOTAL
31464 $123,215,894.65 GRAND TOTAL
*/

------------

Third complex example: the MonthlyOrderSummary stored procedure will generate Account Number Total (subtotal), Day Total (total)and Month Total (grand total) columns.
------------
-- T-SQL grand total, total, subtotal stored procedure
------------
-- T-SQL create stored procedure
USE AdventureWorks2008;
GO
CREATE PROCEDURE MonthlyOrderSummary
-- ALTER PROCEDURE MonthlyOrderSummary
@Year INT,
@Month INT
AS
BEGIN
SELECT soh.AccountNumber,
PO = isnull(soh.PurchaseOrderNumber,''),
convert(CHAR(10),soh.OrderDate,111) AS 'Order Date',
soh.TotalDue,
CASE
WHEN SalesOrderID = (
SELECT TOP 1 SalesOrderID
FROM Sales.SalesOrderHeader
WHERE convert(CHAR(10),OrderDate,111) =
convert(CHAR(10),soh.OrderDate,111)
ORDER BY SalesOrderID DESC) THEN (SELECT '$' +
convert(VARCHAR,sum(TotalDue),1)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID <= soh.SalesOrderID AND convert(CHAR(11),OrderDate,111) = convert(CHAR(10),soh.OrderDate,111)) ELSE ' ' END AS 'Day Total', CASE WHEN SalesOrderID = ( SELECT TOP 1 SalesOrderID FROM Sales.SalesOrderHeader WHERE year(OrderDate) = @Year AND month(OrderDate) = @Month ORDER BY OrderDate DESC) THEN (SELECT '$' + convert(VARCHAR,sum(TotalDue),1) FROM Sales.SalesOrderHeader WHERE year(OrderDate) = @Year AND month(OrderDate) = @Month) ELSE ' ' END AS 'Month Total' FROM Sales.SalesOrderHeader soh WHERE year(OrderDate) = @Year AND month(OrderDate) = @Month ORDER BY SalesOrderID END GO -- MSSQL execute stored procedure with 2 parameters EXEC MonthlyOrderSummary 2003, 7 GO /* Partial results - TotalDue is SUBTOTAL Day Total is TOTAL Month Total is GRAND TOTAL AccountNumber PO Order Date TotalDue Day Total Month Total 10-4030-011069 2003/07/30 2563.589 10-4030-011101 2003/07/30 2696.189 10-4030-026306 2003/07/30 830.2307 10-4030-024421 2003/07/30 2703.9903 10-4030-022398 2003/07/30 596.689 $23,476.44 10-4030-019435 2003/07/31 2715.3497 $4,681,520.64 */ ------------