Artigo

Windows 8 Oficial Quase de graça


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;">&lt;img src="https://s3-us-west-2.amazonaws.com/ecode10-image/heman.gif" width=217 class=block_img height="150"&gt;</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;">&lt;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"&gt;</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"="">&nbsp; &nbsp;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"="">&nbsp; &nbsp;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"="">&nbsp; &nbsp;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>&nbsp;</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"="">&nbsp;&nbsp;</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>

  • Ozimar Henrique

    Ozimar Henrique