Acesse:
<div><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">O famoso
"SELECT *" é um antigo problema dos DBAs.<br>
Sempre tentamos convencer as pessoas a nunca utilizar, neste post mostrarei
mais um problema causado por ele.<o:p></o:p></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><br></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><span style="font-size: 12px; line-height: normal;"><img src="https://s3-us-west-2.amazonaws.com/ecode10-image/heman.gif" width=217 class=block_img height="150"></span><br></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><span style="font-size: 12px; line-height: normal;"><br></span></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><span style="font-size: 12px; line-height: normal;"><img src="</span><a href="http://gamebang.com.br/wp-content/uploads/2012/10/heman.gif">http://gamebang.com.br/wp-content/uploads/2012/10/heman.gif</a><span style="font-size: 12px; line-height: normal;">" width=217 class=block_img height="150"></span><span style="font-size: 12px; line-height: normal;"><br></span></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><br></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Para
reproduzir o erro vamos criar um ambiente simples.<o:p></o:p></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:blue;mso-ansi-language:pt"=""></span></p><pre><p></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:blue;mso-ansi-language:pt"="">CREATE TABLE Produto (</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:#333333;mso-ansi-language:pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"=""> Nome varchar(10),</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";color:#333333;="" mso-ansi-language:pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"=""> Valor decimal(8,2),</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";color:#333333;="" mso-ansi-language:pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"=""> Data smalldatetime</span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"="">)</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";color:#333333;="" mso-ansi-language:pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"="">GO</span><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><o:p></o:p></span></p><p style="line-height:14.25pt"><br></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:blue;mso-ansi-language:pt"="">INSERT INTO Produto<span> </span></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";color:#333333;mso-ansi-language:="" pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"="">Values('Caneta', 12,'02/05/03'),('Caderno',21, '06/07/08')</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:#333333;mso-ansi-language:pt"=""><br></span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"="">GO</span><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><o:p></o:p></span></p><p style="line-height:14.25pt"><span style="font-size: 10pt; line-height: 14.25pt; "><br></span></p><p style="line-height:14.25pt"><span style="font-size: 10pt; line-height: 14.25pt; ">CREATE VIEW VW_Produto</span><br></p><p style="line-height:14.25pt"><span lang="EN-US" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue"="">AS</span><span lang="EN-US" style="font-size:10.0pt;font-family:" georgia","serif";="" color:#333333"=""><br></span><span lang="EN-US" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue"="">SELECT * FROM Produto</span><span lang="EN-US" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333"=""><br></span><span lang="EN-US" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue"="">GO</span><span lang="EN-US" style="font-size:10.0pt;font-family:" georgia","serif";="" color:#333333"=""><o:p></o:p></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""></span></p></pre><p></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Durante um
tempo, a maioria das tabelas sofrem alterações, por exemplo, inserção de campos
em posições intermediárias.<o:p></o:p></span></p>
<span lang="PT" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:#333333;mso-ansi-language:pt;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"=""><div><span lang="PT" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:#333333;mso-ansi-language:pt;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"=""><br></span></div>Usando o Management Studio(SSMS), vamos criar uma coluna Categoria na
posição 3.</span><br></div><div><br></div><img src="http://www.ecode10.com/artigos/dc1f5ddf-28a7-4688-bfb0-8325c15efdb1Post35-Altera.JPG"><div><br></div><div><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Vamos inserir
valores neste novo campo.<o:p></o:p></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:blue;mso-ansi-language:pt"="">UPDATE Produto</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";color:#333333;="" mso-ansi-language:pt"=""> </span><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:blue;mso-ansi-language:pt"="">SET Categoria =
'Papelaria'</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:#333333;mso-ansi-language:pt"=""><o:p></o:p></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><br></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Agora vamos
testar<o:p></o:p></span></p>
<span lang="PT" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:pt;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">SELECT Nome, Valor, Data FROM Produto</span><span lang="PT" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:#333333;mso-ansi-language:pt;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"=""><br>
</span><span lang="PT" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:pt;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">SELECT Nome, Valor, Data FROM VW_Produto</span><br></div><div><br></div><div><img src="https://s3-us-west-2.amazonaws.com/ecode10-image/e260a67b-7704-4e0b-ac69-3acaf716c648Post35-Select.JPG"></div><div><br></div><div><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Veja que o
campo Data trouxe o valor do campo Categoria.<o:p></o:p></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Para corrigir
este problema podemos utilizar o comando sp_refreshview.<br><br>
</span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Exemplo:<br>
</span><span lang="PT" style="font-size:10.0pt;font-family:" georgia","serif";="" color:blue;mso-ansi-language:pt"=""></span></p><pre><span style="font-size: 10pt; line-height: 14.25pt; ">EXEC sp_refreshview 'VW_Produto'</span><span style="line-height: 14.25pt; "></span></pre><p></p><p style="line-height:14.25pt"><span style="line-height: 14.25pt; "><br></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">Para evitar
este problema, jamais use o "SELECT *" dentro de uma View.<br>
Para te forçar a isto use a cláusula "WITH SCHEMABINDING" na criação
das views, pois ela não deixará usar o "SELECT *"<o:p></o:p></span></p><p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"=""><br></span></p>
<p style="line-height:14.25pt"><span lang="PT" style="font-size:10.0pt;
font-family:" georgia","serif";color:#333333;mso-ansi-language:pt"="">O exemplo
abaixo irá gerar um erro:<o:p></o:p></span></p>
<span lang="EN-US" style="font-size:10.0pt;line-height:115%;font-family:" georgia","serif";="" mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:en-us;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">CREATE VIEW VW_Produto</span><span lang="EN-US" style="font-size:10.0pt;
line-height:115%;font-family:" georgia","serif";mso-fareast-font-family:calibri;="" mso-bidi-font-family:"times="" new="" roman";color:#333333;mso-ansi-language:en-us;="" mso-fareast-language:en-us;mso-bidi-language:ar-sa"=""><br>
</span><span lang="EN-US" style="font-size:10.0pt;line-height:115%;font-family:
" georgia","serif";mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:en-us;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">WITH SCHEMABINDING</span><span lang="EN-US" style="font-size:10.0pt;
line-height:115%;font-family:" georgia","serif";mso-fareast-font-family:calibri;="" mso-bidi-font-family:"times="" new="" roman";color:#333333;mso-ansi-language:en-us;="" mso-fareast-language:en-us;mso-bidi-language:ar-sa"=""><br>
</span><span lang="EN-US" style="font-size:10.0pt;line-height:115%;font-family:
" georgia","serif";mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:en-us;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">AS</span><span lang="EN-US" style="font-size:10.0pt;line-height:115%;
font-family:" georgia","serif";mso-fareast-font-family:calibri;mso-bidi-font-family:="" "times="" new="" roman";color:#333333;mso-ansi-language:en-us;mso-fareast-language:="" en-us;mso-bidi-language:ar-sa"=""><br>
</span><span lang="EN-US" style="font-size:10.0pt;line-height:115%;font-family:
" georgia","serif";mso-fareast-font-family:calibri;mso-bidi-font-family:"times="" new="" roman";="" color:blue;mso-ansi-language:en-us;mso-fareast-language:en-us;mso-bidi-language:="" ar-sa"="">Select * From Produto</span><br><div><br></div><div><br></div></div>
