Örnek kullanım:
|
1 |
SELECT DISTINCT column_name COLLATE sql_latin1_general_cp1_cs_as FROM dbo.Users |
Kategori: Sql Server
MSSQL’de Queryler ve Performansları
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT TOP 10 total_worker_time/execution_count AS Avg_CPU_Time ,execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END -statement_start_offset)/2 ) FROM sys.dm_exec_sql_text(sql_handle) ) AS query_text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC |
Entity Framework 6 Working with Transactions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
using (var context = new BloggingContext()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand( @"UPDATE Blogs SET Rating = 5" + " WHERE Name LIKE '%Entity Framework%'" ); var query = context.Posts.Where(p => p.Blog.Rating >= 5); foreach (var post in query) { post.Title += "[Cool Blog]"; } context.SaveChanges(); dbContextTransaction.Commit(); } catch (Exception) { dbContextTransaction.Rollback(); } } } |
Trigger ile Loglama İşlemi
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Create TRIGGER [dbo].[Applications_Logs_Trigger] ON [dbo].[Applications] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION AS SET NOCOUNT ON; DECLARE @operation as Varchar(10) DECLARE @Count as int SET @operation = 'Inserted' -- Setting operation to 'Inserted' SELECT @Count = COUNT(*) FROM DELETED if @Count > 0 BEGIN SET @operation = 'Deleted' -- Set Operation to 'Deleted' SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 SET @operation = 'Updated' -- Set Operation to 'Updated' END if @operation = 'Deleted' BEGIN Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID) SELECT GETDATE(),'SQL','Deleted','Applications',Id,NULL from deleted END ELSE BEGIN SELECT GETDATE(),'Inserted','SQL','Inserted','Applications',Id,NULL from inserted if @operation = 'Inserted' BEGIN Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID) SELECT GETDATE(),'SQL','Inserted','Applications',Id,NULL from inserted END ELSE BEGIN Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID) SELECT GETDATE(),'SQL','Updated','Applications',Id,NULL from inserted END END GO |
SQL IDENTİTY RESEED
|
1 |
dbCC CHECKIDENT ('[Applications]', RESEED, 1000000); |
Sql Server’da Identity Column ID Reset
|
1 2 |
DBCC CHECKIDENT ('[UserProfile]', RESEED, 0); GO |
Database’de Bulunan Tüm Tabloları Silmek
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec SP_EXECUTESQL @Sql FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor GO EXEC sp_MSForEachTable 'DROP TABLE ?' GO |