컴퓨토피아

if object_id('ConnectLog','U') is not null
drop table ConnectLog
create table ConnectLog (
id varchar(10),
login datetime,
logout datetime
)

declare @i int
set @i = 0
while @i < 365
begin
insert into ConnectLog
values('beatchoi',
        dateadd(ss,rand()* 1440,dateadd(d,@i,'2006-01-01')),
        case when @i = 364 then null
                when convert(int,rand() * 100) % 100 = 1 then null
            else dateadd(ss,(rand()+10) * 1440,
                    dateadd(d,@i,'2006-01-01'))
        end)
set @i = @i + 1
end

create view vw_ConnectLog
as
select top 1 Id,Logout
    from ConnectLog
  order by Login desc

update vw_ConnectLog
   set Logout = getdate()
  where Id = 'beatchoi'

서버: 메시지4427, 수준16, 상태1, 줄1
뷰또는함수'vw_ConnectLog' 정의에TOP 절이있어서업데이트할수없습니다.

exec sp_serveroption '[원격서버명]','data access', true

create view vw_ConnectLog_Backdoor as
SELECT a
FROM OPENROWSET('SQLOLEDB','[원격서버명]';'[로그인]';'[암호]'
    'SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login') AS a

update vw_ConnectLog_Backdoor
    set Logout = getdate()
  where Id = 'beatchoi'

서버: 메시지7320, 수준16, 상태2, 줄1
OLE DB 공급자'SQLOLEDB'에대해쿼리를실행할수없습니다. 공급자가필요한행조회인터페이스를지원할수없습니다. 공급자가다른속성또는요구사항과의충돌이발생했음을나타냅니다.
[OLE/DB provider returned message: 여러단계OLE DB 작업을하는동안오류가발생했습니다. 각OLE DB 상태값이있으면확인해보십시오. 완료된작업이없습니다.]
OLE DB 오류추적[OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login[PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].

--고유한 색인 추가
alter table ConnectLog add idx int identity primary key

update vw_ConnectLog_Backdoor
     set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

update a
    set Logout = getdate()
  from ConnectLog a
    join ( select id, max( login ) as login
              from ConnectLog
          group by id) b
     on a.id = b.id and a.login = b.login

(1개행적용됨)

create view vw_ConnectLog
as
select top 1 Id,Logout
   from ConnectLog
   order by Login desc

update vw_ConnectLog
    set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
          expression_name [ ( column_name [ ,...n ] ) ]
       AS
          ( CTE_query_definition )

;with cte_ConnectLog (id,logout) as (
select top 1 id,logout
    from ConnectLog
  order by login desc
)
update cte_ConnectLog
    set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
update a
     set Logout = getdate()
   from ConnectLog a
     join ( select id,max(login) as login
               from ConnectLog
            group by id) b
      on a.id = b.id and a.login = b.login

SQL Server 구문분석및컴파일시간:
      CPU 시간 = 16ms, 경과시간 = 16ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
      CPU 시간 = 0ms, 경과시간 = 4ms.

(1개행적용됨)

update vw_ConnectLog
      set logout = getdate()
   where id = 'beatchoi'

SQL Server 구문분석및컴파일시간:
     CPU 시간 = 5ms, 경과시간 = 5ms.
SQL Server 구문분석및컴파일시간:
     CPU 시간 = 0ms, 경과시간 = 1ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
     CPU 시간 = 0ms, 경과시간 = 2ms.

(1개행적용됨)

;with cte_ConnectLog (id,Logout) as (
select top 1 id,Logout
      from ConnectLog
   order by login desc
)
update cte_ConnectLog
      set logout = getdate()
   where id = 'beatchoi'

SQL Server 구문분석및컴파일시간:
     CPU 시간 = 0ms, 경과시간 = 4ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
     CPU 시간 = 15ms, 경과시간 = 2ms.

(1개행적용됨)