Já faz algum tempo que trabalho com banco de dados e programação. Durante a minha carreira já encontrei muita instrução SQL rui… err estranha. Neste artigo resolvi listar as 10 piores instruções SQL com as quais já me deparei ou ouvi falar.
Antes de começar a apresentar as instruções, é importante dizer que o SQL é uma linguagem de domínio específico e de alto nível. Isso quer dizer que você deve especificar quais dados você deseja obter e não como. Esta característica aliada com os diversos elementos da sintaxe (cláusulas, opções, operadores etc) faz com que o SQL seja muito versátil e adequado para a manipulação de dados. Contudo, muitas pessoas acabam abusando da linguagem e cometendo verdadeiras atrocidades quando acreditam que apenas uma instrução SELECT deve ser escrita para resolver um problema.
Para facilitar a compreensão das instruções SQL, eu as formatei utilizando o ótimo SQL Formatter. Desta maneira, a instrução fica legível e pode-se ao menos tentar compreender o que ela faz.
10. O mistério das tabela BR
A instrução SQL abaixo foi enviada a mim por um leitor dos meus conteúdos em 2004. A dúvida era em relação a um produto cartesiano. Esta preciosidade abaixo contém joins em 5 tabelas, três funções de agregação e nove colunas não agregadas. Outro destaque vai para os nomes pouco explicativos das colunas e dos aliases das tabelas.
SELECT T1."dt_ciclo" "c1",
T2."cd_categoria"
|| ' - '
|| T2."ds_categoria" "c2",
T3."cd_marca"
|| ' - '
|| T3."ds_marca" "c3",
T4."cd_sku"
|| ' - '
|| T4."ds_sku" "c4",
T5."cd_regiao" "c5",
Sum(T1."qt_caixas_trade") "c6",
Sum(T1."qt_caixas_campo") "c7",
Sum(T1."qt_caixas_campo") - Sum(T1."qt_caixas_trade") "c9"
FROM "BRPI05P"."tb_estimativa" T1,
"BRPI05P"."tb_sku_produto" T4,
"BRPI05P"."tb_categorias_produto" T2,
"BRPI05P"."tb_marcas_produto" T3,
"BRPI05P"."tb_regioes" T5
WHERE T1."dt_ciclo" = T4."dt_ciclo"
AND T1."cd_sku" = T4."cd_sku"
AND T4."dt_ciclo" = T2."dt_ciclo"
AND T4."cd_categoria" = T2."cd_categoria"
AND T4."dt_ciclo" = T3."dt_ciclo"
AND T4."cd_categoria" = T3."cd_categoria"
AND T4."cd_grupo_produto" = T3."cd_grupo_produto"
AND T4."cd_marca" = T3."cd_marca"
AND T2."cd_categoria" IN ( '3' )
GROUP BY T1."dt_ciclo",
T2."cd_categoria"
|| ' - '
|| T2."ds_categoria",
T3."cd_marca"
|| ' - '
|| T3."ds_marca",
T4."cd_sku"
|| ' - '
|| T4."ds_sku",
T5."cd_regiao"
ORDER BY 1 ASC,
2 ASC,
3 ASC,
4 ASC,
5 ASC
9. O ERP. É sempre o ERP
Instruções SQL incompreensíveis geradas por ERPs são um clássico. Houve uma época que eu até me preocupava com isso, mas atualmente já nem perco o meu tempo. Basta dizer que o nome das tabelas é críptico, que há um excesso de colunas desnecessárias, o modelo de dados é confuso, que há diversos problemas relacionados à usabilidade da instrução e que deve ser analisada por um ser humano e não uma máquina. A instrução SELECT abaixo é um dos exemplos mais ‘simples’ de como queries geradas para trabalhar com modelos de ERP podem ser um pesadelo do ponto de vista de manutenção.
SELECT d2_grupo grupo,
Cast(Sum(d2_total) AS NUMERIC(10,2))total,
pa1_tptrat tipo
FROM sd2tb0 D2,
se1tb0 E1,
pa1tb0 PA1
WHERE d2_emissao>='20060201'
AND d2_emissao<='20060228'
AND d2.d_e_l_e_t_<>'*'
AND pa1.d_e_l_e_t_<>'*'
AND e1.d_e_l_e_t_<>'*'
AND d2_cliente>=' '
AND d2_cliente<='zzzzzz'
AND Substring(e1_tipo,1,2)='NF'
AND e1_contr<>''
AND d2_loja>=' '
AND d2_loja<=' '
AND e1_emissao=d2_emissao
AND e1_parcela IN ('',
'A')
AND e1_naturez NOT IN ('COFINS',
'PIS',
'CSLL',
'INSS')
AND e1_num=d2_doc
AND e1_contr<>''
AND pa1_contr=e1_contr
AND d2_filial='EC'
AND e1_contr>=' '
AND e1_contr<='zzzzzzzzz'
AND sa1->a1_regiao>='1 '
AND sa1->a1_regiao<='1 '
GROUP BY pa1_tptrat,
d2_grupo
ORDER BY pa1_tptrat,
d2_grupo
8. Subconsultas. Subconsultas Everywhere
O uso de subconsultas em uma instrução SQL é algo que possui um potencial enorme para adicionar complexidade à instrução. Em particular se subconsultas são utilizadas na lista de colunas. A instrução abaixo é um desses exemplos onde provavelmente o SELECT foi montado por várias pessoas diferentes ou por uma ferramenta, pois fica difícil imaginar alguém que tenha um linha de pensamento tão confusa quanto a instrução, que foi obtida a partir desta thread do StackOverflow.
SELECT '%c%' AS Chapter,
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status IN ( 'new', 'assigned' )) AS 'New',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'document_interface') AS 'Document\ Interface',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'interface_development') AS 'Inter\ face Development'
,
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'interface_check') AS 'Interface C\ heck',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'document_routine') AS 'Document R\ outine',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'full_development') AS 'Full Devel\ opment',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'peer_review_1') AS 'Peer Review O\ ne',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'peer_review_2') AS 'Peer Review Tw\ o',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'qa') AS 'QA',
(SELECT Count(ticket.id) AS Matches
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
AND ticket.status = 'closed') AS 'Closed',
Count(id) AS Total,
ticket.id AS _id
FROM engine.ticket
INNER JOIN engine.ticket_custom
ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.NAME = 'chapter'
AND ticket_custom.value LIKE '%c%'
AND type = 'New material'
AND milestone = '1.1.12'
AND component NOT LIKE 'internal_engine'
7. O pesadelo outer join
Muitas pessoas tem dificuldade de entender outer joins e, por isso, muita gente acaba usando este recurso errado. A Oracle, em particular, possui uma sintaxe muito estranha ( (+) = e = (+) ) para lidar com outer joins, o que pode acabar gerando verdadeiras atrocidades. A instrução abaixo foi obtida a partir de um fórum da Oracle e o que mais impressiona nem é tanto a instrução, mas o plano de execução insano que foi gerado.
SELECT sid ,
Max(gsid) gsid ,
Max(firstname) firstname ,
Max(lastname)lastname ... <-lines removed FOR sanity ... ,
max(manager_name)manager_name
FROM (
SELECT s1.objectid sid ,
s1.paramvalue gsid ,
s2.paramvalue firstname ,
s3.paramvalue lastname ... <- ines removed FOR sanity ...
FROM (
SELECT objectid,
paramvalue
FROM param p
WHERE typeentity='USER'
AND paramname='GSID'
AND p.startdate =
(
SELECT max(startdate)
FROM param p1
WHERE typeentity='USER'
AND paramname='GSID'
AND p1.objectid = p.objectid )) s1 ,
(
SELECT objectid,
paramvalue
FROM param p
WHERE typeentity='USER'
AND paramname='FIRSTNAME'
AND p.startdate =
(
SELECT max(startdate)
FROM param p1
WHERE typeentity='USER'
AND paramname='FIRSTNAME'
AND p1.objectid = p.objectid )) s2 ,
(
SELECT objectid,
paramvalue
FROM param p
WHERE typeentity='USER'
AND paramname='LASTNAME'
AND p.startdate =
(
SELECT max(startdate)
FROM param p1
WHERE typeentity='USER'
AND paramname='LASTNAME'
AND p1.objectid = p.objectid )) s3 .... <- same thing FOR ALL 22 attibutes ) s22
WHERE s1.objectid = s2.objectid (+)
AND s1.objectid= s3.objectid (+)
AND s1.objectid= s4.objectid (+)
AND s1.objectid= s5.objectid (+)
AND s1.objectid= s6.objectid (+)
AND s1.objectid= s7.objectid (+)
AND s1.objectid= s8.objectid (+)
AND s1.objectid= s9.objectid (+)
AND s1.objectid= s10.objectid (+)
AND s1.objectid= s11.objectid (+)
AND s1.objectid= s12.objectid (+)
AND s1.objectid= s13.objectid (+)
AND s1.objectid= s14.objectid (+)
AND s1.objectid= s15.objectid (+)
AND s1.objectid= s16.objectid (+)
AND s1.objectid= s17.objectid (+)
AND s1.objectid= s18.objectid (+)
AND s1.objectid= s19.objectid (+)
AND s1.objectid= s20.objectid (+)
AND s1.objectid= s21.objectid (+)
AND s21.paramvalue = s22.objectid (+) )alldata GROUP BY alldata.sid
6. Views e joins: você está fazendo isso da maneira errada
Eu gosto muito de views quando estou simplificando uma instrução SQL. Elas permitem a abstração de algumas instruções complexas e podem facilitar muito a vida de quem trabalha com SQL. Contudo, muitas pessoas acabam usando este recurso de forma errada. O número 6 desta lista traz um uso perigoso conjunto de uma view com left outer joins no SQL Server, como pode ser visto na instrução abaixo que foi obtida a partir desta thread do StackOverflow, onde o usuário que a postou pediu ajuda para reduzir este ‘monstro’.
CREATE VIEW [dbo].[V_BIGGEST_VIEW_EVER]
AS
SELECT {many many columns}
FROM (SELECT *
FROM dbo.t_cus_tsk_task
WHERE is_deleted = 0) T
INNER JOIN dbo.v_cus_grp_group G
ON ( T.group_id = G.group_id )
INNER JOIN dbo.t_bkk_discount_type DT
ON ( DT.discount_type_id = T.discount_type_id )
INNER JOIN dbo.t_bkk_currency DC
ON ( T.debit_currency_id = DC.currency_id )
INNER JOIN dbo.t_bkk_currency PC
ON ( T.payback_currency_id = PC.currency_id )
INNER JOIN dbo.t_bkk_currency FC
ON ( T.final_debit_currency_id = FC.currency_id )
INNER JOIN dbo.t_global_counter D1C
ON ( D1C.company_id = T.company_id
AND D1C.counter_name = 'PROFORMA_INVOICE_COUNTER' )
INNER JOIN dbo.t_global_counter D2C
ON ( D2C.company_id = T.company_id
AND D2C.counter_name = 'TAX_INVOICE_COUNTER' )
INNER JOIN dbo.t_global_counter D3C
ON ( D3C.company_id = T.company_id
AND D3C.counter_name = 'INVOICE_RECEIPT_COUNTER' )
INNER JOIN dbo.t_global_counter D4C
ON ( D4C.company_id = T.company_id
AND D4C.counter_name = 'DELIVERY_NOTE_COUNTER' )
INNER JOIN dbo.t_global_counter D5C
ON ( D5C.company_id = T.company_id
AND D5C.counter_name = 'BILL_OF_LADING_COUNTER' )
INNER JOIN dbo.t_global_counter D6C
ON ( D6C.company_id = T.company_id
AND D6C.counter_name = 'CREDIT_INVOICE_COUNTER' )
LEFT JOIN dbo.v_sys_branch BR
ON ( T.branch_id = BR.branch_id )
LEFT JOIN dbo.t_cus_tsk_tasks_array AR
ON ( T.array_id = AR.array_id )
LEFT JOIN dbo.t_driver D
ON ( T.driver_id = D.driver_id )
LEFT JOIN dbo.t_vehicle V
ON ( T.vehicle_id = V.vehicle_id )
LEFT JOIN dbo.t_stf_inviter I
ON ( T.inviter_id = I.inviter_id )
LEFT JOIN dbo.t_stf_subcontractor SC1
ON ( SC1.subcontractor_id = D.subcontractor_id )
LEFT JOIN dbo.t_stf_subcontractor SC2
ON ( SC2.subcontractor_id = T.subcontractor_id )
LEFT JOIN dbo.t_cus_tsk_task_status S
ON ( S.task_status_id = T.task_status_id )
LEFT JOIN dbo.v_stf_sub_location SL1
ON ( SL1.sub_location_id = T.start_sub_location_id )
LEFT JOIN dbo.v_stf_sub_location SL2
ON ( SL2.sub_location_id = T.end_sub_location_id )
LEFT JOIN dbo.t_stf_customer CU
ON ( CU.customer_id = T.customer_id )
LEFT JOIN dbo.t_stf_customer_splitting_code SP
ON ( SP.splitting_id = T.splitting_id )
LEFT JOIN dbo.v_cus_tsk_credit_for_task CR
ON CR.task_id = T.task_id
LEFT JOIN dbo.t_bkk_proforma_invoice D1
ON ( T.proforma_invoice_id = D1.proforma_invoice_id )
LEFT JOIN dbo.t_bkk_tax_invoice D2
ON ( T.tax_invoice_id = D2.tax_invoice_id )
LEFT JOIN dbo.t_bkk_invoice_receipt D3
ON ( T.invoice_receipt_id = D3.invoice_receipt_id )
LEFT JOIN dbo.t_bkk_delivery_note D4
ON ( T.delivery_note_id = D4.delivery_note_id )
LEFT JOIN dbo.t_bkk_bill_of_lading D5
ON ( T.bill_of_lading_id = D5.bill_of_lading_id )
LEFT JOIN dbo.v_cus_tsk_container CONTAINER1
ON ( CONTAINER1.container_id = T.container1_id )
LEFT JOIN dbo.v_cus_tsk_container CONTAINER2
ON ( CONTAINER2.container_id = T.container2_id )
LEFT JOIN dbo.v_stf_trailer TRAILER1
ON ( TRAILER1.trailer_id = T.trailer1_id )
LEFT JOIN dbo.v_stf_trailer TRAILER2
ON ( TRAILER2.trailer_id = T.trailer2_id )
LEFT JOIN dbo.t_stf_luggage_type LUGGAGE_TYPE
ON ( LUGGAGE_TYPE.luggage_type_id = T.luggage_type_id )
5. Joins e mais joins no WordPress
Já descrevi em um artigo algumas características do modelo de dados do WordPress. Infelizmente muita gente que acaba escrevendo instruções SQL para consultar dados no MySQL, acaba pecando por fazer muitos joins, como é o caso da instrução abaixo que contém 10 joins chegando muito próximo do limite máximo recomendado no padrão SQL (que é 16). Esta instrução SQL foi obtida neste artigo que descreve quais foram os passos tomados pelo desenvolvedor para simplificá-la.
SELECT p.id,
p.post_content,
p.post_title,
p.post_name,
p.guid,
m1.meta_value AS date,
m2.meta_value AS alt_dates,
m3.meta_value AS days,
t1.name AS company,
t2.name AS asset
FROM $wpdb->posts p
JOIN $wpdb->postmeta m1
ON (
p.id = m1.post_id
AND m1.meta_key = 'date')
LEFT JOIN $wpdb->postmeta m2
ON (
p.id = m2.post_id
AND m2.meta_key = 'alt_dates')
JOIN $wpdb->postmeta m3
ON (
p.id = m3.post_id
AND m3.meta_key = 'number_days')
JOIN $wpdb->term_relationships tr1
ON p.id = tr1.object_id
JOIN $wpdb->term_taxonomy tt1
ON tr1.term_taxonomy_id = tt1.term_taxonomy_id
JOIN $wpdb->terms t1
ON tt1.term_id = t1.term_id
JOIN $wpdb->term_relationships tr2
ON p.id = tr2.object_id
JOIN $wpdb->term_taxonomy tt2
ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
JOIN $wpdb->terms t2
ON tt2.term_id = t2.term_id
WHERE p.post_type = 'companies'
AND p.post_status = 'publish'
AND tr1.term_taxonomy_id={$pageinfo['term_ID']}
AND tt2.parent = {$pageinfo['term_ID']}
AND m1.meta_value > '$old_dates'
ORDER BY date
4. Dado duplicado? Bota o DISTINCT!
Um erro muito comum em instruções SELECT acontece quando se utiliza outer joins e acaba-se ficando com um conjunto duplicado de linhas. A solução? Bota um DISTINCT! Isso é tão comum, que toda a vez que eu analiso uma instrução SELECT e já vejo um DISTINCT logo de cara, tenho a impressão de que existe um uso errado do outer join, como na instrução SELECT abaixo (obtida aqui), que consegue misturar os três: inner join, left outer join e right outer join. E ainda tem estruturas CASE na lista de colunas!
SELECT DISTINCT t_order_main.orderid,
t_order_ship.payment,
t_order_main.orderdate,
t_customer.fullname,
t_customer.country,
t_customer.emailaddress,
t_order_ship.remark,
t_order_remark.remarktxt,
v_ordercountcustomer.ordercount,
v_ordercustomerreturned.orderreturned,
v_ordernotshipped.notshipped,
v_order_inreadytosend.ready,
v_order_neworders.neworder,
Chargeback = CASE
WHEN V_OrderChargeback_2.ip <> ''
AND V_OrderChargeback_2.ip <> '0' THEN 1
WHEN v_orderchargeback.emailaddress <> '' THEN 1
WHEN V_OrderChargeback_1.fullname <> '' THEN 1
ELSE 0
END,
Westernunion = CASE
WHEN t_order_ship.payment = 'westernunion'
OR t_order_ship.payment = 'btransfer'
OR t_order_ship.payment = 'cash' THEN 1
ELSE 0
END,
t_order_main.customerid,
Blacklisted = CASE
WHEN s.street IS NOT NULL THEN 1
WHEN i.ip IS NOT NULL
AND i.ip <> '0' THEN 1
WHEN f.fullname IS NOT NULL THEN 1
WHEN t_customer.blacklisted = 1 THEN 1
ELSE 0
END,
CouponID = CASE
WHEN t_order_main.couponid = 0 THEN 0
ELSE 1
END,
t_customer.site
FROM t_order_main
INNER JOIN t_customer
ON t_order_main.customerid = t_customer.customerid
INNER JOIN t_order_ship
ON t_order_main.orderid = t_order_ship.orderid
LEFT OUTER JOIN v_orderchargeback V_OrderChargeback_1
ON t_customer.fullname = V_OrderChargeback_1.fullname
LEFT OUTER JOIN v_orderchargeback V_OrderChargeback_2
ON t_customer.ip = V_OrderChargeback_2.ip
LEFT OUTER JOIN t_order_remark
ON t_order_main.orderid = t_order_remark.orderid
LEFT OUTER JOIN v_ordercountcustomer
ON t_order_main.customerid = v_ordercountcustomer.customerid
LEFT OUTER JOIN v_ordercustomerreturned
ON t_order_main.customerid =
v_ordercustomerreturned.customerid
LEFT OUTER JOIN v_ordernotshipped
ON t_order_main.customerid = v_ordernotshipped.customerid
LEFT OUTER JOIN v_order_inreadytosend
ON t_order_main.customerid =
v_order_inreadytosend.customerid
LEFT OUTER JOIN v_order_neworders
ON t_order_main.customerid = v_order_neworders.customerid
LEFT OUTER JOIN v_orderchargeback
ON t_customer.emailaddress = v_orderchargeback.emailaddress
LEFT OUTER JOIN v_blacklist s
ON t_customer.street = s.street
LEFT OUTER JOIN v_blacklist i
ON t_customer.ip = i.ip
LEFT OUTER JOIN v_blacklist f
ON t_customer.fullname = f.fullname
WHERE ( t_order_main.orderstatus = 1 )
ORDER BY t_order_main.orderid ASC
3. CTE é evil, muito evil.
Um CTE (Commom Table Expression) é um recuso do SQL muito mal compreendido pelas pessoas, especialmente por desenvolvedores que não possuem sólidos conhecimentos de recursividade. Portando, é de se esperar que este recurso leve as instruções SQL confusas, mal escritas e que são muito difíceis de serem compreendidas e reescritas. Este é o caso da instrução abaixo, que além de ter um tamanho descomunal, ainda usa um query hint (OPTION (MAXDOP 1)).
WITH sys_partitions
AS (SELECT partition_scheme_name = SPS.NAME,
partition_function_name = SPF.NAME,
data_space_id = SPS.data_space_id
FROM sys.partition_schemes AS SPS
INNER JOIN sys.partition_functions AS SPF
ON SPS.function_id = SPF.function_id),
sys_indexes
AS (SELECT O.[object_id],
I.index_id,
I.is_disabled,
I.fill_factor,-- fullness at page level
I.is_padded,-- affects upper levels of b-tree
[Type] = Cast(CASE WHEN I.index_id = 1 THEN 'clustered' WHEN
I.index_id
= 0 THEN
'heap'
ELSE
'nonclustered' END + CASE WHEN I.[ignore_dup_key]
<> 0
THEN
', ignore duplicate keys' ELSE '' END + CASE WHEN
I.is_unique <> 0
THEN
', unique' ELSE '' END + CASE WHEN
I.is_primary_key <>
0
THEN
', primary key'
ELSE '' END + CASE WHEN I.has_filter = 1 THEN
', filtered'
ELSE ''
END +
CASE
WHEN FI.[object_id] IS NOT NULL THEN ', fulltext'
ELSE
''
END AS
VARCHAR(
210)),
[table_name] = O.[name],
[schema] = SC.[name],
[table_created] = O.create_date,
[table_modified] = O.modify_date,
[table_has_quotedident_on] =
Objectproperty(O.object_id, 'IsQuotedIdentOn')
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON O.[object_id] = I.[object_id]
INNER JOIN sys.schemas AS SC
ON O.[schema_id] = SC.[schema_id]
LEFT OUTER JOIN sys.fulltext_indexes AS FI
ON I.[object_id] = FI.[object_id]
AND I.index_id = FI.unique_index_id
WHERE O.[type] IN ( 'U', 'V' )),
sys_index_operational_stats
AS (SELECT [object_id],
index_id,
leaf_allocation_count,
-- this equates to page splits, and it counts both good and bad
range_scan_count,
singleton_lookup_count,
forwarded_fetch_count,
lob_fetch_in_pages,
lob_fetch_in_bytes,
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms,
page_latch_wait_count,
page_io_latch_wait_count
FROM [sys].[Dm_db_index_operational_stats](Db_id(), NULL, NULL, NULL)
),
sys_index_usage_stats
AS (SELECT o.[object_id],
i.index_id,
index_name = i.[name],
user_seeks = u.user_seeks,
last_user_seek = u.last_user_seek,
last_system_seek = u.last_system_seek,
user_scans = u.user_scans,
last_user_scan = u.last_user_scan,
last_system_scan = u.last_system_scan,
user_lookups = u.user_lookups,
last_user_lookup = u.last_user_lookup,
last_system_lookup = u.last_system_lookup,
user_updates = u.user_updates
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON I.[object_id] = O.[object_id]
INNER JOIN sys.dm_db_index_usage_stats AS U
ON I.[object_id] = U.[object_id]
-- Statistics are zeroed during online rebuilds in 2012
AND I.index_id = U.index_id
WHERE U.database_id = Db_id()
AND O.[type] IN ( 'U', 'V' )),
sys_index_physical_stats
AS (SELECT [object_id],
index_id,
pages = Sum(page_count),
[page_density] = Sum(Round(avg_page_space_used_in_percent, 2)),
page_fragmentation = CASE
WHEN Max(index_type_desc) <> 'HEAP' THEN
Sum(Round(avg_fragmentation_in_percent, 2
))
ELSE NULL
END,
extent_fragmentation = CASE
WHEN Max(index_type_desc) = 'HEAP' THEN
Sum(
Round(avg_fragmentation_in_percent, 2))
ELSE NULL
END,
[rows] = Sum(record_count),
fw_records = Sum(forwarded_record_count),
partition_number
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL,
'SAMPLED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
GROUP BY [object_id],
index_id,
partition_number),
indexspaceused
AS (SELECT [object_id] = I.[object_id],
[index_ID] = I.index_id,
[Size (KB)] = Sum(S.used_page_count) * 8
FROM sys.dm_db_partition_stats AS S
INNER JOIN sys.indexes AS I
ON S.[object_id] = I.[object_id]
AND S.index_id = I.index_id
INNER JOIN sys.objects AS O
ON O.[object_id] = I.[object_id]
INNER JOIN sys.schemas AS SC
ON O.schema_id = SC.schema_id
WHERE O.[type] IN ( 'U', 'V' )
GROUP BY I.[object_id],
I.index_id,
I.NAME,
S.used_page_count,
O.create_date,
O.modify_date,
SC.NAME)
SELECT [Database] = Db_name(Db_id()),
[Schema] = SI.[schema],
[Table] = SI.[table_name],
[Tbl object_id] = Object_id(SI.[schema] + '.' + SI.[table_name]),
[Tbl Created] = CONVERT(CHAR(10), SI.[table_created], 101),
[Tbl Modified] = CONVERT(CHAR(10), SI.[table_modified], 101),
[Tbl QuotedIdentOn] = SI.[table_has_quotedident_on],
[IX] = Isnull(IX.NAME, ''),
[IX Type] = SI.[type],
[Key Columns] = Isnull(Replace(Replace(Replace((SELECT columnName =
c.NAME +
CASE WHEN
sic.is_descending_key = 0
THEN
' ASC'
ELSE
' DESC'
END
FROM [sys].[index_columns] AS sic
INNER JOIN [sys].[columns] AS c
ON c.column_id = sic.column_id
AND c.[object_id] =
sic.[object_id]
WHERE sic.[object_id] = ix.[object_id]
AND sic.index_id = ix.index_id
AND is_included_column = 0
ORDER BY sic.index_column_id
FOR xml raw), '"/><row columnName="', ', '),
'<row columnName="', ''), '"/>',
''), ''),
[Included Columns] = Isnull(Replace(Replace(Replace(
(SELECT columnName = c.NAME
FROM [sys].[index_columns] AS sic
INNER JOIN [sys].[columns] AS c
ON c.column_id =
sic.column_id
AND c.[object_id] =
sic.[object_id]
WHERE sic.[object_id] = ix.[object_id]
AND sic.index_id = ix.index_id
AND is_included_column = 1
ORDER BY sic.index_column_id
FOR xml raw), '"/><row columnName="', ', ')
,
'<row columnName="', '')
,
'"/>', ''), ''),
[Filtered Columns] =
Isnull(
Replace(
Replace(Replace(
Replace(IX.filter_definition, ']', ''), '[', ''), '(', ''), ')', ''), ''),
[Reads] = Sum(DUS.user_seeks + DUS.user_scans
+ DUS.user_lookups),
[Seeks] = DUS.user_seeks,
[Scans] = DUS.user_scans,
[Lookups] = DUS.user_lookups,
[Writes] = DUS.user_updates,
[Reads Per Write] = CONVERT(DECIMAL(10, 2), Max(CASE
WHEN DUS.user_updates < 1 THEN
100.00
ELSE 1.00 * ( DUS.user_seeks +
DUS.user_scans
+
DUS.user_lookups ) /
DUS.user_updates
END)),
[Last Read] = CONVERT(CHAR(10), (SELECT Max(v)
FROM (VALUES (DUS.last_user_seek),
(DUS.last_user_scan),
(DUS.last_user_lookup)) AS value
(v)), 101),
[Last Seek] = DUS.last_user_seek,
[Last Scan] = DUS.last_user_scan,
[Last Lookup] = DUS.last_user_lookup,
[Pages] = FR.pages,
[Page Density] = FR.[page_density],
[Page Fragmentation] = FR.page_fragmentation,
[Rows] = (SELECT Sum(P.[rows])
FROM [sys].[partitions] AS P
WHERE P.index_id = IX.index_id
AND IX.[object_id] = P.[object_id]),
[IX Size (KB)] = SU.[size (kb)],
[Last Stats Update] = CONVERT(CHAR(10), Stats_date(IX.[object_id], IX.index_id),
101),
[IX ID] = SI.index_id,
[IX IsDisabled] = SI.is_disabled,
[IX Fill Factor] = SI.fill_factor,
[IX IsPadded] = SI.is_padded,
[Forwarded Records] = FR.fw_records,
[IX Page Splits] = DOS.leaf_allocation_count
FROM [sys].[indexes] AS IX
INNER JOIN [sys].[objects] AS O
ON IX.[object_id] = O.[object_id]
LEFT OUTER JOIN sys_indexes AS SI
ON O.[object_id] = SI.[object_id]
AND IX.index_id = SI.index_id
LEFT OUTER JOIN sys_index_operational_stats AS DOS
ON DOS.index_id = IX.index_id
AND DOS.[object_id] = IX.[object_id]
LEFT OUTER JOIN sys_index_usage_stats AS DUS
ON IX.NAME = DUS.index_name
AND DUS.[object_id] = O.[object_id]
LEFT OUTER JOIN sys_index_physical_stats AS FR
ON FR.[object_id] = IX.[object_id]
AND fr.index_id = ix.index_id
LEFT OUTER JOIN indexspaceused AS SU
ON FR.[object_id] = SU.[object_id]
AND SU.index_id = FR.index_id
LEFT OUTER JOIN sys_partitions AS PT
ON IX.data_space_id = PT.data_space_id
WHERE O.is_ms_shipped = 0
AND O.[type] IN ( 'U', 'V' )
GROUP BY SI.[schema],
SI.table_name,
SI.table_created,
SI.table_modified,
SI.table_has_quotedident_on,
IX.NAME,
SI.[type],
ix.[object_id],
ix.[index_id],
IX.filter_definition,
DUS.user_updates,
FR.pages,
FR.[page_density],
FR.page_fragmentation,
SU.[size (kb)],
SI.index_id,
SI.is_disabled,
SI.fill_factor,
SI.is_padded,
FR.fw_records,
DUS.last_user_seek,
DUS.last_user_scan,
DUS.last_user_lookup,
DOS.leaf_allocation_count,
DUS.user_seeks,
DUS.user_scans,
DUS.user_lookups
ORDER BY SI.[schema],
SI.table_name,
FR.pages DESC
OPTION (maxdop 1);
2. Acentos em lugares inapropriados
Muitos modelos de dados contam com nomes de colunas e tabelas que contêm acentos. Não gosto muito desta prática, pois é possível que durante a geração de um script possa haver algum problema na conversão de caracteres. Este SELECT que recebi por um e-mail, em 2011, deixa isso bem claro. Destaque para o fato da instrução SELECT ler os dados de uma arquivo do Access ao invés de uma tabela do SQL Server.
SELECT `dados conint`.superintend�ncia AS 'SUP',
`dados conint`.malha AS 'MALHA',
`dados conint`.ger�ncia_sd AS 'GERÊNCIA',
`dados conint`.p�lo AS 'PÓLO',
Max(`dados conint`.munic�pio) AS 'Max CIDADE'
,
`dados conint`.`n doc` AS
'N CONDIS',
`dados conint`.`n int` AS 'N CONINT',
`dados conint`.in�cio AS 'DT INÍCIO'
,
`dados conint`.t�rmino AS
'DT TÉRMINO',
1 AS 'INT TOTAL',
Max(Ccur(dura��o / 60)) AS
'Max DURAÇÃO',
Abs(dura��o < 420) AS 'INT < 7H',
Ccur(( Abs(dura��o < 420) ) * dura��o / 60) AS 'TA < 7H',
Abs(( dura��o >= 420 ) <> ( ( Datevalue(t�rmino) > Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) AS 'INT > 7H',
Ccur(( Abs(( dura��o >= 420 ) <> ( ( Datevalue(t�rmino) >
Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) ) *
dura��o / 60
) AS 'TA > 7H',
Abs(( ( Datevalue(t�rmino) > Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) AS 'INT DORM',
Ccur(( Abs(( ( Datevalue(t�rmino) > Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) ) * dura��o / 60) AS 'TA DORM'
FROM `f:\cemig\tma - novo_gerint_2011.mdb`.`dados conint` `DADOS CONINT`
WHERE ( `dados conint`.causa = 'ACIDENTAL' )
AND ( `dados conint`.`grupo causa` <> '1/1'
AND `dados conint`.`grupo causa` <> '4/7' )
AND ( `dados conint`.`redes/linhas` = 'REDES MT / BT' )
GROUP BY `dados conint`.superintend�ncia,
`dados conint`.malha,
`dados conint`.ger�ncia_sd,
`dados conint`.p�lo,
`dados conint`.`n doc`,
`dados conint`.`n int`,
`dados conint`.in�cio,
`dados conint`.t�rmino,
1,
Abs(dura��o < 420),
Ccur(( Abs(dura��o < 420) ) * dura��o / 60),
Abs(( dura��o >= 420 ) <> ( ( Datevalue(t�rmino) >
Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )),
Ccur(( Abs(( dura��o >= 420 ) <> ( ( Datevalue(t�rmino) > Datevalue
(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) ) *
dura��o / 60
),
Abs(( ( Datevalue(t�rmino) > Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )),
Ccur(( Abs(( ( Datevalue(t�rmino) > Datevalue(in�cio)
AND Hour(in�cio) < 19
AND Hour(t�rmino) >= 7 )
OR dura��o >= 1440 )) ) * dura��o / 60),
`dados conint`.dura��o
HAVING ( `dados conint`.dura��o >= 3 )
AND ( `dados conint`.`n doc` = 113353985 )
ORDER BY `dados conint`.`n doc`,
`dados conint`.`n int`
1. Aquele que não pode ser digitado
Como o número 1 desta lista, fiz questão de indicar aquela que é considerada a pior instrução SQL de todos os tempos. Ela foi postada em um fórum da Oracle e contém um tamanho ridículo de 26 páginas, 4.826 palavras, 43.624 caracteres, 79 ocorrências da cláusula GROUP BY, 67 ocorrências da palavra FROM, 56 ocorrências do operador =, 54 ocorrências do operador AND dentre muitos outros problemas. Esta query foi gerada por uma ferramenta para uma consulta OLAP (imagina o desempenho dela!), mas mesmo assim não acredito que é algo que sequer deva ser mencionado em algum lugar. Por questões de espaço, me nego a colocá-la aqui e recomendo a todos que não percam tempo tentando compreendê-la.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?