본문 바로가기

DB

[Joins]SQL [Two Tables] Student Subject Tables Innter Join 교집합: INNER JOIN can use EXISTSSELECT A.* from TA as A where EXISTS (Select * from TB b where C1 = A.C1) Outer Join : Outer Join is A-B(B- A∩B) == In Oracle use MINUS operator, others can use NOT EXIST or NOT INThere are three type of joins, LEFT, RIGHT, FULL OUTER JOINIn ANSI style can use *= (left), =* (right), full outer(for full)ex) SELECT * FRO.. 더보기
[Where Clause] Operators Keywords Meaning Example > greater than returns the rows WHERE DocumentSummary LIKE '%provides%'; -->return nothing 더보기
[Cursor] is loop for a list, how to use where is the table structure with data. Now Create a Cursor which travels each record by TransactionId. -- ================================================ -- This is an example of Cursor -- ================================================ USE [ApressFinancial]--.[TransactionDetails].[Transactions] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= .. 더보기
[DMV] Missing Index It help you to find which column you usually use without declaring index field. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost] , s.avg_user_impact , d.statement AS TableName , d.equality_columns , d.inequality_columns , d.included_columns FROM sys.dm_db_missing_index_groups g INNER .. 더보기
[DMV] Slowest queries Here is example to find which query is the slowest query in my db. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Elapsed Duration (s)] , qs.execution_count , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statem.. 더보기