http___programlamanotlari.blogspot.com.tr_2010_10_10_archive.html_1.xml
1.1 2.1 3.1 4.1 5.1 6.1 7.1 7.2 8.1 9.1 10.1 11.1 12.1 12.2 13.1 14.1 14.2 14.3 15.1 15.2 15.3 16.1 17.1 18.1 18.2 19.1 19.2 20.1 21.1 22.1 23.1 24.1 25.1 26.1 27.1 28.1 29.1 30.1 31.1 32.1 33.1 35.1 36.1 37.1 38.1 40.1 40.2 41.1 42.1 43.1 44.1 45.1 46.1 47.1 48.1 49.1 50.1 51.1 52.1 54.1 55.1 56.1 57.1 58.1 58.2 59.1 60.1 61.1 62.1 63.1 64.1 65.1 66.1 67.1 68.1 69.1 69.2 70.1 70.2 71.1 72.1 73.1 74.1 75.1 76.1 77.1 78.1 80.1 81.1 82.1 84.1 85.1 86.1 87.1 88.1 89.1 90.1 91.1 92.1 93.1 94.1 95.1 96.1 97.1 98.1 99.1 100.1 101.1 102.1 103.1 103.2 104.1 104.2 105.1 106.1 107.1 108.1 109.1 111.1 112.1 113.1 114.1 115.1 116.1 117.1 118.1 119.1 120.1 121.1 122.1 123.1 124.1 125.1 126.1 127.1 128.1 128.2 129.1 130.1 131.1 132.1 133.1 134.1 135.1 136.1 137.1 138.1 139.1 140.1 141.1 142.1 143.1 144.1 145.1 146.1 147.1 148.1 149.1 150.1 150.2 151.1 152.1 153.1 154.1 154.2 155.1 156.1 156.2 157.1 158.1 159.1 160.1 161.1 162.1 163.1 164.1 165.1 166.1 167.1 168.1 169.1 170.1 171.1 172.1 172.2
What is Computed Column ? If you have columns in your sql database tables such that their values depend on other columns, you may use computed columns. Using computed columns or calculated columns will enable you save the calculation logic of such computed values in database. And will save you from extra coding everytime you have to do when you require the computed item. A computed column can be defined as an expression which can use other columns in the same table as well as using other table values via user-defined functions. The expression of the computation can be directly a name of a noncomputed column (*), constants, functions including sql or user-defined functions or can contain all in the computed column definition. It is important that the computed column expression can not be a subquery which means you can not use SELECT statements in the expression. Even if you are selecting a constant value like "SELECT 1" . (*) As a note : A computed column in a table is not allowed to be used in another computed-column definition in the same table. You are reference a computed column in another table by using a user defined function in the expression of the calculated column. Computed columns are actually not physically created on the related database table unless they are defined as "Persisted" computed columns. You can think of computed columns as virtual columns. They are not physically stored in the related sql database table. They are calculated every time they are referenced in a sql statement. If you want to create an index on the computed column, then If you require the computed column to be stored in the database instead of calculating everytime, you should define the computed column as persisted using the "PERSISTED" keyword in CREATE TABLE ... ADD column or ALTER TABLE ... ALTER COLUMN statements. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. Computed columns just like an ordinary table column can be used in a select list, can take place in WHERE clauses and in ORDER BY clauses. But computed columns cannot be the directly updated or values can not be inserted into computed columns using INSERT or UPDATE statements. Subqueries are not allowed in this context. Only scalar expressions are allowed. A computed column is computed from an expression. But the expression cannot be a subquery. If you need to execute SELECT statement to gather required data for the computed column value expression, then a user-defined function can be used. You can build a user-defined function which can run SELECT statements. But if you decide to create or alter your calculated column to be persisted, you may experience some other problems relating with determinism especially for XML data types and XQuery. You may solve the problem for creating a persisted computed column by defining the user defined function (udf) which is used in the expression by using the "WITH SCHEMABINDING" with user sql function. Cannot Create Persisted Computed Columns using User-Defined Functions with Select From Other Tables You can not create calculated columns / computed columns as persisted if there is a user-defined function in the computed column expression and this function runs SELECT queries from database tables. This is because if you are using a user defined sql function in the calculated column expression, to make the computed column persisted, udf must be deterministic. For determinism for such a sql function, you must define the user defined t-sql function with "WITH SCHEMABINDING" . In this case, schema binding will require the table names in two-part format. And when you alter the sql function with tables names in two part format including the schema name and "WITH SCHEMABINDING" hint, this time the function will not be able for use in an expression of a persisted column. Because then the column will be defined with user data access which is not suitable for a persisted computed column. T-SQL Computed Column Sample Codes for SQL Server Tables Here you can find samples where computed column properties are being illustrated with sample t-sql code. CREATE TABLE Authors AuthorId int IDENTITY(1,1) NOT NULL, FirstName nvarchar(100), LastName nvarchar(100), FullName AS (FirstName + SPACE(1) + LastName) -- computed column This is a simple expression sample. We define the computed column by "AS" clause and see that we do not define a column type since the type is defined implicitly by the calculation expression. INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter') INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka') SELECT * FROM Authors Let's add an other computed column where a CASE expression is used in the definition of the calculated column value. ALTER TABLE Authors ADD FullName2 AS (CASE WHEN AuthorId < 3 THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END) INSERT INTO Authors (FirstName, LastName) VALUES (N'Itzik', N'Ben-Gan') SELECT * FROM Authors Let's now create a subsidiary SQL Server database table which we will use in our following samples. Create Table DBParameters ( ParameterId int Identity(1,1) NOT NULL, ParameterDescription nvarchar(1000), ParameterValue nvarchar(1000) GO insert into DBParameters select N'First Name is before Last Name', N'1' ALTER TABLE Authors ADD FullName3 AS (CASE WHEN EXISTS(SELECT * FROM DBParameters) THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END) Msg 1046, Level 15, State 1, Line 1 Subqueries are not allowed in this context. Only scalar expressions are allowed. CREATE FUNCTION GetBooleanParameterValue( @ParameterId as int ) RETURNS bit AS BEGIN DECLARE @result bit SELECT @result = CAST(ParameterValue AS bit) FROM DBParameters WHERE ParameterId = @ParameterId RETURN @result END GO SELECT dbo. GetBooleanParameterValue(1) ALTER TABLE Authors ADD FullName3 AS (CASE WHEN dbo. GetBooleanParameterValue(1) = 1 THEN (FirstName + SPACE(1) + LastName) ELSE (LastName + SPACE(1) + FirstName) END) UPDATE DBParameters SET ParameterValue = 1 WHERE ParameterId = 1 SELECT FirstName, LastName, FullName3 FROM Authors UPDATE DBParameters SET ParameterValue = 0 WHERE ParameterId = 1 SELECT FirstName, LastName, FullName3 from Authors The first select displays the firstname before the lastname in the fullname. And after the parameter table is updated, the second select displays the lastnames before the last. Let's create an other table which will help us in the next computed columns. CREATE TABLE [Author Expertise] Id int Identity(1,1) NOT NULL, AuthorId int, Expertise nvarchar(1000) NOT NULL GO INSERT INTO [Author Expertise] SELECT 1, N'T-SQL' INSERT INTO [Author Expertise] SELECT 1, N'CLR' INSERT INTO [Author Expertise] SELECT 2, N'CLR' INSERT INTO [Author Expertise] SELECT 2, N'XML' INSERT INTO [Author Expertise] SELECT 3, N'T-SQL' INSERT INTO [Author Expertise] SELECT 3, N'CLR' INSERT INTO [Author Expertise] SELECT 3, N'Service Broker' GO CREATE FUNCTION GetAuthorExpertiseCount( @AuthorId as int ) RETURNS int AS BEGIN DECLARE @result int SELECT @result = COUNT(*) FROM [Author Expertise] WHERE AuthorId = @AuthorId RETURN @result END GO SELECT dbo. GetAuthorExpertiseCount(3) ALTER TABLE Authors ADD Expertise_Topics_Count AS ( dbo. GetAuthorExpertiseCount(AuthorId) ) SELECT * FROM Authors ALTER TABLE Authors ADD BooksInfo XML GO UPDATE Authors SET BooksInfo = N' Microsoft SQL Server 2005 Microsoft SQL Server 2008 WHERE AuthorId = 3 GO CREATE FUNCTION GetLastBookTitle ( @BooksInfo XML ) RETURNS nvarchar(1000) AS BEGIN DECLARE @Title nvarchar(1000) select @Title = i.value('@title', 'nvarchar(1000)') from @BooksInfo.nodes('/books/book') n(i) order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc RETURN @Title END GO ALTER TABLE Authors ADD LastBookTitle AS dbo. GetLastBookTitle(BooksInfo) GO SELECT AuthorId, Fullname3, BooksInfo, LastBookTitle FROM Authors ALTER TABLE Authors ADD BirthDate datetime GO UPDATE Authors SET BirthDate = '19700101' WHERE AuthorId = 1 GO ALTER TABLE Authors ADD Age AS DATEDIFF(yy, BirthDate, GETDATE()) ALTER TABLE Authors ADD Age2 AS DATEADD(yy, Age, BirthDate) The SQL Server error message will be: Msg 1759, Level 16, State 0, Line 1 Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition. Also a user- defined function which is using the computed table column in its t-sql code cannot be used in another computed column expression. CREATE FUNCTION TestUDF( @Age int, @BirthDate datetime ) RETURNS datetime AS BEGIN RETURN DATEADD(yy, @Age, @BirthDate) END GO ALTER TABLE Authors ADD Age2 AS dbo. TestUDF(Age, BirthDate) Msg 1759, Level 16, State 0, Line 1 Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition. What about if we want to define the computed column as PERSISTED column in order to create indexes on these columns. ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo. GetLastBookTitle(BooksInfo) Persisted Computed column 'LastBookTitlePersisted' in table 'Authors' cannot be persisted because the column is non-deterministic. The reason while we can not define the LastBookTitlePersisted column as PERSISTED is the computed column is non-deterministic because of the user-defined function dbo. GetLastBookTitle() is an XML based function. But the solution for this problem is simple for this udf. If we alter the procedure by adding the "WITH SCHEMABINDING" hint, we can manage creating the related column persisted. CREATE FUNCTION GetLastBookTitle2 ( @BooksInfo XML ) RETURNS nvarchar(1000) WITH SCHEMABINDING -- The "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column AS BEGIN DECLARE @Title nvarchar(1000) select @Title = i.value('@title', 'nvarchar(1000)') from @BooksInfo.nodes('/books/book') n(i) order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc RETURN @Title END ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo. GetLastBookTitle2(BooksInfo) Persisted
1.1 2.1 3.1 4.1 5.1 6.1 7.1 8.1 9.1 10.1 11.1 12.1 13.1 14.1 15.1 16.1 17.1 18.1 19.1 20.1 21.1 22.1 23.1 24.1 25.1 26.1 27.1 28.1 29.1 30.1 31.1 32.1 33.1 34.1 35.1 36.1 37.1 38.1 39.1 40.1 41.1 42.1 43.1 45.1 46.1 47.1 48.1 49.1 50.1 51.1 52.1 53.1 54.1 55.1 56.1 57.1 58.1 59.1 60.1 61.1 62.1 63.1 64.1 65.1 66.1 67.1 68.1 69.1 70.1 71.1 72.1 73.1 74.1 75.1 76.1 77.1 78.1 79.1 80.1 81.1 82.1 83.1 84.1 85.1 86.1 87.1 88.1 89.1 90.1 91.1 92.1 93.1 94.1 95.1 96.1 97.1 98.1 99.1 100.1
"DBCC INPUTBUFFER", SQL Server'a client'dan gelen son sql cümleciğini gösterir. Syntax : DBCC INPUTBUFFER (session_id [ , request_id ]) [WITH NO_INFOMSGS ] Parametreleri : session_id Son aktif connection'daki oturumun id'sidir. @@SPID global değişkeni ile, sql server'daki son request'in id si ni alabilirsiniz. SELECT @@SPID kodunu çalıştırarak örneğini görebilirsiniz. request_id Aktif oturumdaki, son istedğin id sidir. Son gelen isteğin id sini aşağıdaki kodu kullanarak alabilirsiniz. SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@spid Aşağıdaki kod ise, son request ile ilgili daha fazla detay verir. Durumu ne, hangi veri tabanına gelmiş, kim çalıştırmış, ne kadar süre beklemiş gibi. SELECT * FROM sys.dm_exec_requests WHERE session_id = @@spid WITH With, diğer DDL komutlarında olduğu gibi bir özellik vereceğimizi ifade eder. NO_INFOMSGS Hata seviyesi 0-10 arasında olan yani server tarafında bilgi mesajı olarak kabul edilen seviyede bir mesaj gelirse, bunu gizler. Bu kadar teorik bilgiden sonra, "DBCC INPUTBUFFER" ın ne işimize yarayacağına ve nasıl kullanılacağımıza bakalım. CDC kullanmadan, bir tablodaki değişiklikleri log'luyor isek, bu değişikliğin hangi sql cümlesi kullanılarak (Bu bir update/insert/delete statement olabileceği gibi, bir stored procedure de olabilir) yapıldığını saklayabiliriz. Veya bir tablo, nedenini bulamadığımız bir şekilde güncelleniyor ise, bu güncelleme işleminin hangi kod tarafından yapıldığını yakalayabiliriz. Aşağıdaki örnek SQL cümlesi, testCustomer isimli bir tablodaki her değişikliği loglayıp, değişiklikleri de testCustomer_Log isimli bir dosya da saklayacağız. Önce kodlarımızı hazırlayalım. CREATE TABLE testCustomer(CustomerID int identity(1,1), FirstName varchar(100), LastName varchar(100)) GO --DROP TABLE testCustomer_Log CREATE TABLE testCustomer_Log(ID int identity(1,1), CustomerID int, OldFirstName nvarchar(100), OldLastName nvarchar(100), NewFirstName nvarchar(100), NewLastName nvarchar(100), SqlCommand nvarchar(4000), ChangedOn datetime default(GetDate()) GO CREATE TRIGGER trTestCustomer ON testCustomer AFTER UPDATE,INSERT,DELETE AS BEGIN DECLARE @InputBuffer TABLE(EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000)) INSERT INTO @InputBuffer exec ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS') DECLARE @SQLCommand nvarchar(4000) SELECT @SQLCommand = EventInfo FROM @InputBuffer INSERT INTO testCustomer_Log(CustomerID,OldFirstName,OldLastName, NewFirstName,NewLastName,SqlCommand) SELECT isNull(i.CustomerID,d.CustomerID),d.FirstName,d.LastName, i.FirstName,i.LastName,@SQLCommand FROM inserted i FULL JOIN deleted d on d.CustomerID = i.CustomerID END GO CREATE PROCEDURE spTEST_UpdateCustomerName @FirstName nvarchar(100),@CustomerID int AS BEGIN UPDATE testCustomer SET FirstName = @FirstName WHERE CustomerID = @CustomerID END Data ve Log tablolarını yarattıktan sonra, Data tablomuz olan testCustomer üzerine bir trigger yazıyoruz. Bu trigger de @@SPID ile son oturumun id sini alıp, DBCC INPUTBUFFER a veriyoruz. Bize uyarı mesajları vermesin diye de mesajları "WITH NO_INFOMSG" yazarak kapatıyoruz. DBCC INPUTBUFFER 3 kolondan, 1 satırdan oluşan bir tablo döndürür. Bu tablodan dönen değerleri @InputBuffer isimli bir değişken tabloya yazıyoruz ki, sonra DML kodunu çalıştıran kodu alabilelim. EventInfo kolonunda, nvarchar(4000) tipinde bir bilgi gelir. İşte datayı değiştiren kod da bu kolonda bulunur. Yani çalışan script eğer 4000 karakterden fazla ise, ilk 4000 karakterini görebiliriz. Bu kodu @SQLCommand değişkenine atıyoruz. Sonra değişen bilgilerin eski ve yeni bilgilerini yazabilmek için de, trigger içerisinde kullanılan inserted ve deleted tablolarını full join ile bağlıyoruz. Unutmamak gerekir ki, bir tabloya insert kodu ile bir kayıt ekliyorsak, sadece inserted tablosunda kayıtlar olur, deleted tablosundan birşey gelmez. Aynı mantıkla, delete ile kayıt siliyorsak, sadece deleted tablosunda silinen kaydın bilgisi olur, inserted tablosunda birşey olmaz. Update ile tabloyu güncelliyor isek, yeni bilgiler inserted tablosunda, eski bilgiler de deleted tablosunda bulunur. Birkaç örnek ile trigger ın log tablosuna neler yazdığını bulalım. INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF') INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf') GO INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN','TestLN') GO INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN2','TestLN2') GO UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc' GO DELETE FROM testCustomer WHERE FirstName='TestFN' GO DECLARE @CustomerID int SELECT @CustomerID = CustomerID FROM testCustomer WHERE FirstName='TestFN2' exec spTEST_UpdateCustomerName @CustomerID = @CustomerID, @FirstName = 'TestFN3' GO SELECT * FROM testCustomer SELECT * FROM testCustomer_Log İşte Customer tablosunun son hali. 4 kayıt ekledik. Kayıtlardan birini update cümlesi ile güncelledik. Sonra başka bir kayıt sildik. Daha sonra da adı TestFN2 olan kaydın adını yazdığımız stored procedure ile güncelledik. Acaba log tablomuzda neler olmuş? İşte tüm yaptığımız değişikliker burada görünüyor. İlk başta çalıştırdığımız insert cümlesi 2 kayıt birden insert edecek bir batch şeklinde yazılmıştı. Customer 1 ve Customer 2 kayıtlarında bu her iki cümlenin de yazdığını görebiliyoruz. En ilginci de, 4 nolu CustomerID nin update cümlesi. Burada ilgili müşteri numarasını bulduktan sonra bunu bir procedure e veriyor ve onun güncelleme yapmasını bekliyorduk. Yazdığımız tüm kod da burada aynen görünebiliyor. Peki testCustomer tablosunu TRUNCATE TABLE ile temizlersek ne olur? Log tablosuna hiç bir şey gelmez. Çünkü truncate kodu herhangi bir trigger'ı tetiklemez. Biz de işlerimizi trigger ile yaptığımız için log tablosunun herhangi birşeyden haberi olmaz. Bir sonraki post'da DBCC INPUTBUFFER a benzer iş yapan fn_get_sql sistem fonksiyonundan ve ikisi arasındaki farklardan bahsedeceğiz.