{"id":23,"date":"2008-06-24T14:51:42","date_gmt":"2008-06-24T13:51:42","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=23"},"modified":"2020-04-01T13:08:10","modified_gmt":"2020-04-01T12:08:10","slug":"broken-sql-objects-views-functions-stored-procedures-triggers","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2008\/06\/broken-sql-objects-views-functions-stored-procedures-triggers\/","title":{"rendered":"Broken SQL objects: Views, functions, stored procedures, triggers"},"content":{"rendered":"<p>Designing a perfect <a href=\"http:\/\/en.wikipedia.org\/wiki\/Entity-relationship_model\">entity relationship diagram<\/a> for our database together with a marvellous table structure is never good enough&#8230;<\/p>\n<p>Have look at the following example (for MS SQL Server) <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190273.aspx\">creating two tables<\/a> and a view:<\/p>\n<pre lang=\"sql\">\r\nCREATE TABLE T1(\r\n  F1 INT\r\n, F2 VARCHAR(10)\r\n);\r\n\r\nCREATE TABLE T2(\r\n  F1 INT\r\n, F3 INT\r\n, F4 VARCHAR(30)\r\n);\r\n\r\nCREATE VIEW V12\r\nAS\r\n  SELECT\r\n    T1.*\r\n  , T2.F3\r\n  , T2.F4\r\n  FROM T1\r\n  INNER JOIN T2 \r\n  ON T1.F1 = T2.F1\r\n;\r\n<\/pre>\n<p>A view, a <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms186755.aspx\">function<\/a> or something else, we try to write re-usable code so that we do not have to repeat it again and again. Certainly, while we are developing a system, we keep changing things especially when the requirements are not so clearly defined at the beginning, or the client keeps asking for new features. In this case, let&#8217;s say another field is required in table T1 and we have to add it (<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190273.aspx\">ALTER TABLE<\/a>).<\/p>\n<pre lang=\"sql\">\r\nALTER TABLE T1\r\nADD F5 DATETIME\r\n;\r\n<\/pre>\n<p>This simply means that our view is now broken! It needs to be recompiled!<\/p>\n<p>1. One of the solutions to this problem is to create our view with the option of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187956.aspx\">WITH SCHEMABINDING<\/a>.<\/p>\n<pre lang=\"sql\">\r\nCREATE VIEW V12 WITH SCHEMABINDING\r\nAS\r\n  SELECT\r\n    T1.*\r\n  , T2.F3\r\n  , T2.F4\r\n  FROM T1\r\n  INNER JOIN T2 \r\n  ON T1.F1 = T2.F1\r\n;\r\n<\/pre>\n<p>Now this is supposed to be preventing any changes to the schema of the database that will damage our view.<\/p>\n<p>2. The second solution is to use fields explicitly without using asterisk (*)<\/p>\n<pre lang=\"sql\">\r\nCREATE VIEW V12 WITH SCHEMABINDING\r\nAS\r\n  SELECT\r\n    T1.F1\r\n  , T1.F2\r\n  , T2.F3\r\n  , T2.F4\r\n  , T1.F5\r\n  FROM T1\r\n  INNER JOIN T2 \r\n  ON T1.F1 = T2.F1\r\n;\r\n<\/pre>\n<p>If we have many fields in your tables and a list of views, functions that depend on each other, this will cause a headache. You will end up with recompiling the objects.<\/p>\n<p>Try to find the balance; avoid too many dependencies.<br \/>\nDo not create unnecessary objects such as:<\/p>\n<pre lang=\"sql\">\r\nCREATE FUNCTION F(@ID INT)\r\nRETURNS TABLE\r\nAS\r\n  RETURN\r\n     SELECT *\r\n     FROM T1\r\n     WHERE F1 = @ID\r\n;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Designing a perfect entity relationship diagram for our database together with a marvellous table structure is never good enough&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[56,33],"class_list":["post-23","post","type-post","status-publish","format-standard","hentry","category-technology","tag-ms-sql-server","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/23","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=23"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/23\/revisions"}],"predecessor-version":[{"id":1011,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/23\/revisions\/1011"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}