Eu tenho uma enorme string de consulta que estou tentando separar para um projeto no qual estou trabalhando. Estou procurando alguma orientação sobre como desmontá-lo (formatá-lo para legível por humanos). Além disso, quais são os sinais de adição? BTW: Eu testei e funciona.
SELECT Name ,
Company ,
ID ,
Type = CASE Person.IsClient
WHEN NULL THEN 'Other'
WHEN '' THEN 'Other'
WHEN 'Contact' THEN 'Other'
WHEN 'True' THEN 'Client'
ELSE ISNULL(Person.IsClient, 'Other')
END ,
SortOrder = CASE ISNULL(CompanyIndividual, 'nullstr')
WHEN 'company' THEN ISNULL(ClientName, Name)
ELSE LTRIM(ISNULL(LastName, '') + ISNULL(FirstName, '')
+ ISNULL(Company, Name) + ISNULL(Name, ''))
END ,
SubType ,
UDF1 ,
UDF2 ,
UDF3 ,
UDF4 ,
UDF5 ,
UDF6 ,
UDF7 ,
UDF8 ,
UDF9 ,
UDF10 ,
SuperGroup ,
Notes ,
WebSite ,
UserName ,
LastNoteDateX = CASE ISNULL(UserName, '')
WHEN '' THEN LastNoteDate
ELSE ( SELECT MAX(LastNoteDate)
FROM PersonOrganization WITH ( NOLOCK )
WHERE Person = Person.ID
AND Organization = '04202000000000000066'
)
END ,
LastNotePersonX = CASE ISNULL(UserName, '')
WHEN '' THEN LastNotePerson
ELSE ( SELECT MAX(LastNotePerson)
FROM PersonOrganization WITH ( NOLOCK )
WHERE Person = Person.ID
AND Organization = '04202000000000000066'
)
END ,
ItemType = ISNULL(PersonUnread.ItemType, '') ,
Flag = ISNULL(Flag.Flag, 'Grey') ,
PopupReminderPerson = ISNULL(PopupReminder.Person, 'F') ,
Person.FirstName ,
Person.LastName ,
Person.CompanyIndividual ,
PersonUnreadContactItemType = ISNULL(PersonUnreadContact.ItemType, '') ,
-1 ,
Description ,
Person.ShortName ,
PhoneNumber = ISNULL(( SELECT PhoneNumber
FROM Phone WITH ( NOLOCK )
JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
WHERE PersonPhone.Person = Person.ID
AND Phone.DisplayOrder = 1
), '') ,
PersonView
FROM Person WITH ( NOLOCK )
LEFT JOIN PersonUnread WITH ( NOLOCK ) ON Person.ID = PersonUnread.Ref
AND PersonUnread.Person = '38808000000000001177'
AND PersonUnread.ItemType = 'ContactNote'
LEFT JOIN Flag WITH ( NOLOCK ) ON Person.ID = Flag.Ref
AND Flag.Person = '38808000000000001177'
LEFT JOIN PopupReminder WITH ( NOLOCK ) ON Person.ID = PopupReminder.Ref
AND PopupReminder.Person = '38808000000000001177'
AND PopupReminder.Status = 'Pending'
LEFT JOIN PersonUnread PersonUnreadContact WITH ( NOLOCK ) ON Person.ID = PersonUnreadContact.Ref
AND PersonUnreadContact.ItemType = 'Contact'
AND PersonUnreadContact.Person = '38808000000000001177'
WHERE ( ( ISNULL(Person.PersonView, 'A') = 'A'
OR Person.PersonView = ''
)
OR ( ISNULL(Person.PersonView, 'A') = 'C'
AND ( Person.Creator = '38808000000000001177'
OR ISNULL(Person.Creator, '') = ''
)
)
OR ( ISNULL(Person.PersonView, 'A') = 'P'
AND ( ( Person.Creator = '38808000000000001177'
OR ISNULL(Person.Creator, '') = ''
)
OR EXISTS ( SELECT Assignee
FROM PersonAssignee WITH ( NOLOCK )
WHERE PersonAssignee.Person = Person.ID
AND PersonAssignee.Assignee = '38808000000000001177' )
)
)
)
AND ( (( Person.SuperGroup = '04202000000000000066'
OR Person.ID IN (
SELECT Person
FROM PersonOrganization WITH ( NOLOCK )
WHERE Organization = '04202000000000000066' )
)
AND EXISTS ( SELECT Person
FROM PersonOrganization
WHERE Person = '38808000000000001177'
AND Organization = '04202000000000000066'
AND ( ISNULL(RestrictContacts, 'False') <> 'True'
OR EXISTS ( SELECT Person
FROM PersonAssignee
WITH ( NOLOCK )
WHERE Person = Person.ID
AND Assignee = '38808000000000001177' )
OR Person.ID = '38808000000000001177'
OR Person.Creator = '38808000000000001177'
) ))
)
AND ( Person.SuperGroup = '04202000000000000066'
OR Person.ID IN ( SELECT Person
FROM PersonOrganization WITH ( NOLOCK )
WHERE Organization = '04202000000000000066' )
)
AND ( ( ISNULL(Person.Name, '') + ' ' + ISNULL(Person.Company, '')
+ ' ' + ISNULL(Person.FirstName, '') + ' '
+ ISNULL(Person.LastName, '') + ' ' + ISNULL(Person.MiddleName,
'') + ' '
+ ISNULL(Person.Title, '') + ' ' + ISNULL(Person.Prefix, '')
+ ' ' + ISNULL(Person.SubType, '') + ' '
+ ISNULL(Person.WebSite, '') + ' ' + ISNULL(Person.Suffix, '')
+ ' ' + ISNULL(Person.UDF1, '') + ISNULL(Person.UDF2, '')
+ ISNULL(Person.UDF3, '') + ISNULL(Person.UDF4, '')
+ ISNULL(Person.UDF5, '') + ISNULL(Person.UDF6, '')
+ ISNULL(Person.UDF7, '') + ISNULL(Person.UDF8, '')
+ ISNULL(Person.UDF9, '') + ISNULL(Person.UDF10, '') LIKE '%divorce%' )
OR ( ISNULL(Description, '') LIKE '%divorce%' )
OR EXISTS ( SELECT ID
FROM Note WITH ( NOLOCK )
WHERE Note.Item = Person.ID
AND SuperGroup = '04202000000000000066'
AND ( ( Note.Title LIKE '%divorce%' )
OR ( Note.Note LIKE '%divorce%' )
) )
OR EXISTS ( SELECT ID
FROM Phone WITH ( NOLOCK )
JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
WHERE PersonPhone.Person = Person.ID
AND ( Phone.PhoneNumber LIKE '%divorce%' ) )
OR EXISTS ( SELECT ID
FROM Address WITH ( NOLOCK )
JOIN PersonAddress WITH ( NOLOCK ) ON PersonAddress.Address = Address.ID
WHERE PersonAddress.Person = Person.ID
AND ( ( Description LIKE '%divorce%' )
OR ( Street LIKE '%divorce%' )
OR ( City LIKE '%divorce%' )
OR ( State LIKE '%divorce%' )
OR ( Zip LIKE '%divorce%' )
OR ( Country LIKE '%divorce%' )
) )
OR EXISTS ( SELECT ID
FROM Email WITH ( NOLOCK )
WHERE Email.Person = Person.ID
AND ( Email.Email LIKE '%divorce%' ) )
OR EXISTS ( SELECT FCGroup.ID
FROM FCGroup WITH ( NOLOCK )
JOIN FCGroupPerson WITH ( NOLOCK ) ON FCGroup.ID = FCGroupPerson.FCGroup
WHERE FCGroupPerson.Person = Person.ID
AND ( FCGroup.Name LIKE '%divorce%' ) )
OR EXISTS ( SELECT ID
FROM Note WITH ( NOLOCK )
WHERE Note.Item = Person.ID
AND ( ( Note.Title LIKE '%divorce%' )
OR ( Note.Note LIKE '%divorce%' )
) )
OR EXISTS ( SELECT P2.ID
FROM Person P2 WITH ( NOLOCK )
JOIN PersonAssignee WITH ( NOLOCK ) ON P2.ID = PersonAssignee.Assignee
WHERE PersonAssignee.Person = Person.ID
AND ( P2.Name + ' ' + P2.FirstName + ' '
+ P2.MiddleName + ' ' + P2.LastName
+ ' ' + P2.Title + ' ' + P2.Company LIKE '%divorce%' ) )
)
ORDER BY 5
Como os comentários apontaram, existem várias ferramentas disponíveis para formatar a consulta e algumas sugestões.
http://format-sql.com
http://poorsql.com
Além disso, existem várias sugestões para melhorar a consulta além de apenas torná-la "legível por humanos". - Pare de usar o NOLOCK - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx ou http://www.sqlservercentral .com/articles/Performance+Tuning/2764/
Explicando o operador "+". Os sinais + são concatenações de strings neste caso, por exemplo, SELECT 'a' + 'b' retorna 'ab'.
Esta resposta é principalmente extraída dos comentários com algumas adições. Obrigado a: @ypercube, @spaghettidba, @Kin, @gpatterson