Congratulations!

[Valid RSS] This is a valid RSS feed.

Recommendations

This feed is valid, but interoperability with the widest range of feed readers could be improved by implementing the following recommendations.

Source: https://www.sqlservercentral.com/feed

  1. <?xml version="1.0" encoding="UTF-8"?><rss version="2.0"
  2. xmlns:content="http://purl.org/rss/1.0/modules/content/"
  3. xmlns:wfw="http://wellformedweb.org/CommentAPI/"
  4. xmlns:dc="http://purl.org/dc/elements/1.1/"
  5. xmlns:atom="http://www.w3.org/2005/Atom"
  6. xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
  7. xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
  8. >
  9.  
  10. <channel>
  11. <title>Articles - SQLServerCentral</title>
  12. <atom:link href="https://www.sqlservercentral.com/articles/feed" rel="self" type="application/rss+xml" />
  13. <link>https://www.sqlservercentral.com</link>
  14. <description>The #1 SQL Server community</description>
  15. <lastBuildDate>Mon, 28 Apr 2025 23:30:24 +0000</lastBuildDate>
  16. <language>en-GB</language>
  17. <sy:updatePeriod>
  18. hourly </sy:updatePeriod>
  19. <sy:updateFrequency>
  20. 1 </sy:updateFrequency>
  21. <generator>https://wordpress.org/?v=6.0.3</generator>
  22. <item>
  23. <title>How to Load a Date Dimension in Microsoft Fabric using Macros in dbt?</title>
  24. <link>https://www.sqlservercentral.com/articles/how-to-load-a-date-dimension-in-microsoft-fabric-using-macros-in-dbt</link>
  26. <dc:creator><![CDATA[Additional Articles]]></dc:creator>
  27. <pubDate>Wed, 30 Apr 2025 00:00:14 +0000</pubDate>
  28. <category><![CDATA[Microsoft Fabric (Azure Synapse, Data Engineering, etc.)]]></category>
  29. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4583665</guid>
  30.  
  31. <description><![CDATA[<p>The article A gentle introduction to dbt explains how you can get dbt in the cloud version, how you can set up a free account, and how to create a connection to a Microsoft Fabric warehouse.</p>
  32. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/how-to-load-a-date-dimension-in-microsoft-fabric-using-macros-in-dbt">How to Load a Date Dimension in Microsoft Fabric using Macros in dbt?</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  33. ]]></description>
  34. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/how-to-load-a-date-dimension-in-microsoft-fabric-using-macros-in-dbt">How to Load a Date Dimension in Microsoft Fabric using Macros in dbt?</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  35. ]]></content:encoded>
  39. </item>
  40. <item>
  41. <title>Navigating Your Test Data Challenges: Insights and Solutions</title>
  42. <link>https://www.sqlservercentral.com/articles/navigating-your-test-data-challenges-insights-and-solutions</link>
  44. <dc:creator><![CDATA[Press Release]]></dc:creator>
  45. <pubDate>Mon, 07 Apr 2025 00:00:59 +0000</pubDate>
  46. <category><![CDATA[Uncategorized]]></category>
  47. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4572820</guid>
  48.  
  49. <description><![CDATA[<p>Join Redgate on May 1st for an insightful webinar cohosted with analyst firm, Bloor Research, featuring independent Senior Analyst Daniel Howard and Redgate's Steve Jones. Discover the biggest test data challenges, the latest approaches like AI synthetic data generation, and how effective test data management can benefit your entire organization. Don't miss out on this opportunity to gain valuable insights and solutions.</p>
  50. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/navigating-your-test-data-challenges-insights-and-solutions">Navigating Your Test Data Challenges: Insights and Solutions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  51. ]]></description>
  52. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/navigating-your-test-data-challenges-insights-and-solutions">Navigating Your Test Data Challenges: Insights and Solutions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  53. ]]></content:encoded>
  57. </item>
  58. <item>
  59. <title>How to Forecast Data in Power BI</title>
  60. <link>https://www.sqlservercentral.com/articles/how-to-forecast-data-in-power-bi</link>
  62. <dc:creator><![CDATA[Daniel Calbimonte]]></dc:creator>
  63. <pubDate>Mon, 27 Feb 2023 00:00:30 +0000</pubDate>
  64. <category><![CDATA[Power BI]]></category>
  65. <category><![CDATA[forecast]]></category>
  66. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4148481</guid>
  67.  
  68. <description><![CDATA[<p>Learn how you can use Power BI to forecase the future trends of data.</p>
  69. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/how-to-forecast-data-in-power-bi">How to Forecast Data in Power BI</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  70. ]]></description>
  71. <content:encoded><![CDATA[<h2>Introduction</h2>
  72. <p>In this article, we will learn how to Forecast data in Power BI. Forecasting in Power BI will help us to predict the future based on our current information. You can easily forecast in Power BI by following some simple steps.</p>
  73. <p><img width="795" height="494" class="wp-image-4148482" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart.png" data-lazy-load alt="forecast data in power bi - forecast chart" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart.png 795w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-300x186.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-768x477.png 768w" sizes="(max-width: 795px) 100vw, 795px" /></p>
  74. <h2>Requirements to forecast data in Power BI</h2>
  75. <p>First, we need to have the <a href="https://powerbi.microsoft.com/en-us/downloads/">Power BI Desktop</a> installed.</p>
  76. <p>Secondly, we need <a href="https://www.sqlservercentral.com/blogs/sql-server-2019-step-by-step-installation">SQL Server installed</a>.</p>
  77. <p>Finally, we will use the<a href="https://www.sqlservercentral.com/articles/sql-server-sample-databases-2"> Adventureworks database</a>.</p>
  78. <h2>Import data</h2>
  79. <p>First, in Power BI press the <strong>Get data</strong> option.</p>
  80. <p><img loading="lazy" width="1380" height="788" class="wp-image-4148483" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data.jpeg" data-lazy-load alt="forecast data in power bi - get data" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data.jpeg 1380w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data-300x171.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data-1024x585.jpeg 1024w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data-768x439.jpeg 768w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-get-data-128x72.jpeg 128w" sizes="(max-width: 1380px) 100vw, 1380px" /></p>
  81. <p>Secondly, select the <strong>SQL Server database option</strong> and press <strong>Connect.</strong></p>
  82. <p><img loading="lazy" width="600" height="660" class="wp-image-4148484" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-source.jpeg" data-lazy-load alt="forecast data in power bi - SQL Server source" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-source.jpeg 600w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-source-273x300.jpeg 273w" sizes="(max-width: 600px) 100vw, 600px" /></p>
  83. <p>Thirdly, write the SQL Server name. In my case, I am using the local server. Press <strong>OK.</strong></p>
  84. <p><img loading="lazy" width="701" height="335" class="wp-image-4148485" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-connection.jpeg" data-lazy-load alt="forecast data in power bi - sql server connection properties" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-connection.jpeg 701w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-sql-server-connection-300x143.jpeg 300w" sizes="(max-width: 701px) 100vw, 701px" /></p>
  85. <p>Also, in the Adventureworks database, check the <strong>Purchasing.PurchaseOrderDetail</strong> table. This is a table of the purchases made by the Adventureworks company. Next, press <strong>Load.</strong></p>
  86. <p><img loading="lazy" width="881" height="680" class="wp-image-4148486" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-preview-view-of-the-da.jpeg" data-lazy-load alt="forecast data in power bi - Preview view of the data" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-preview-view-of-the-da.jpeg 881w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-preview-view-of-the-da-300x232.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-preview-view-of-the-da-768x593.jpeg 768w" sizes="(max-width: 881px) 100vw, 881px" /></p>
  87. <h2>Visualize the data to forecast</h2>
  88. <p>First, check the <strong>Purchasing.PurchaseOrderDetail</strong> table and the <strong>Year</strong>. Also, check the <strong>OrderQty</strong> field.</p>
  89. <p><img loading="lazy" width="203" height="503" class="wp-image-4148487" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-fields.jpeg" data-lazy-load alt="Select fields" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-fields.jpeg 203w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-fields-121x300.jpeg 121w" sizes="(max-width: 203px) 100vw, 203px" /></p>
  90. <p>Secondly, select the Line chart. Make sure that the <strong>DueDate </strong>Year in the X-axis and the Y-axis have the <strong>Sum of OrderQty</strong>.</p>
  91. <p><img loading="lazy" width="189" height="653" class="wp-image-4148488" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-the-line-chart.png" data-lazy-load alt="Select the line chart" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-the-line-chart.png 189w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/select-the-line-chart-87x300.png 87w" sizes="(max-width: 189px) 100vw, 189px" /></p>
  92. <p>If everything is OK, the chart will look like this:</p>
  93. <p><img loading="lazy" width="821" height="508" class="wp-image-4148489" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-created.jpeg" data-lazy-load alt="forecast data in power bi - forecast chart created" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-created.jpeg 821w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-created-300x186.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-chart-created-768x475.jpeg 768w" sizes="(max-width: 821px) 100vw, 821px" /></p>
  94. <p>The company started in 2011 and until 2014 the sales increased to 1,5 million. In 2013, the sales were around 692K, and in 2012 140k.</p>
  95. <h3>Further analyses of your visual</h3>
  96. <p>To enable the forecast option, select <strong>add further analyses to your visual</strong> icon and then turn on the <strong>Forecast</strong> option. Note that the forecast option is not available if Power BI does not detect dates in the chart. If Power BI does not detect dates in the fields used, by the chart, you will not see the forecast option.</p>
  97. <p><img loading="lazy" width="195" height="575" class="wp-image-4148490" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-option.png" data-lazy-load alt="Forecast option" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-option.png 195w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-option-102x300.png 102w" sizes="(max-width: 195px) 100vw, 195px" /></p>
  98. <p>The Forecast includes the following options.</p>
  99. <ul>
  100. <li>First, we have units. <strong>Units</strong> are used to find if we want to forecast in Years, Quarters, Days or whatever option is available for the time.</li>
  101. <li>Secondly, we have<strong> Forecast Length. </strong>It is used to configure how many units ahead do we want to forecast. By default, the number is 10. In this example, we are using the Forecast length equal to 10, which means that we will forecast 10 years.</li>
  102. <li>Thirdly we have the<strong> Ignore the last option</strong>. It is useful when you think that some intervals of time should not be included in the forecast analysis because they are not representative. For example, if we had great sales before the COVID pandemic and during the pandemic, we have bad years. We think our forecast should not include the last 2 years because we are optimistic.</li>
  103. <li>Also, we have<strong> Seasonality.</strong> It is an option in Power BI. The option is used to determine some tendencies at a certain time. It will detect certain variations during the year. This option could be used to determine some patterns in the information and make some predictions about the future. For example, we can detect more sales during winter or during summer. Some valid values for seasonality are yearly, quarterly, monthly, weekly, and daily.</li>
  104. <li>Finally, we have the <strong>Confidence interval.</strong> This is a percentage used to determine how confident the value is. It is a range of values of the forecasted value. We consider this as the forecasting value with a margin of error. This margin is set by the user.</li>
  105. </ul>
  106. <p><img loading="lazy" width="184" height="394" class="wp-image-4148491" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-properties-in-power-bi.jpeg" data-lazy-load alt="forecast properties in Power BI" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-properties-in-power-bi.jpeg 184w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-properties-in-power-bi-140x300.jpeg 140w" sizes="(max-width: 184px) 100vw, 184px" /></p>
  107. <p>The current sales values from 2011-2014 are represented in a blue line. Also, the black line with a gray background represents the forecasting values. According to the forecast, our sales will reach 6 million in 2024.</p>
  108. <p><img loading="lazy" width="796" height="482" class="wp-image-4148492" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-values.jpeg" data-lazy-load alt="forecast data in power bi - forecast values" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-values.jpeg 796w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-values-300x182.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-forecast-values-768x465.jpeg 768w" sizes="(max-width: 796px) 100vw, 796px" /></p>
  109. <h2>Upper bound and Lower bound values</h2>
  110. <p>If you move the mouse over the lines in the chart, you will notice that the line has 3 values:</p>
  111. <ul>
  112. <li>First, you have the <strong>Forecast value </strong>which is the estimated value.</li>
  113. <li>Secondly, you have the <strong>Upper bound</strong> which is the maximum forecast value.</li>
  114. <li>Finally, you have <strong>Lower bound</strong> which is the minimum forecast value. Both, the upper bound and lower bound are determined by the Confidence interval explained previously.</li>
  115. </ul>
  116. <p><img loading="lazy" width="475" height="331" class="wp-image-4148493" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-upper-bound-lower-boun.jpeg" data-lazy-load alt="forecast data in power bi - upper bound lower bound" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-upper-bound-lower-boun.jpeg 475w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-data-in-power-bi-upper-bound-lower-boun-300x209.jpeg 300w" sizes="(max-width: 475px) 100vw, 475px" /></p>
  117. <h2>Change the forecast length data in Power BI</h2>
  118. <p>We will show how to change the number of years, and months forecasted.</p>
  119. <p>First, in order to change the prediction from 10 years to 2 years, change the Forecast length from 10 to 2</p>
  120. <p><img loading="lazy" width="181" height="348" class="wp-image-4148494" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-lenght.png" data-lazy-load alt="Forecast lenght" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-lenght.png 181w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-lenght-156x300.png 156w" sizes="(max-width: 181px) 100vw, 181px" /></p>
  121. <p>Secondly, verify the chart generated. Note that the forecasting line is shorter (it represents 2 years instead of 10).</p>
  122. <p><img loading="lazy" width="789" height="484" class="wp-image-4148495" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/chart-forecast.jpeg" data-lazy-load alt="chart forecast" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/chart-forecast.jpeg 789w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/chart-forecast-300x184.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/chart-forecast-768x471.jpeg 768w" sizes="(max-width: 789px) 100vw, 789px" /></p>
  123. <h2>Forecast data in Power BI – Changing the format</h2>
  124. <p>Follow these steps to change the forecast format in Power BI:</p>
  125. <p>First, press <strong>add further analyses to your visual</strong> icon.</p>
  126. <p>Secondly, look for the Forecast line.</p>
  127. <p><img loading="lazy" width="270" height="623" class="wp-image-4148496" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-line.png" data-lazy-load alt="Forecast line" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-line.png 270w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-line-130x300.png 130w" sizes="(max-width: 270px) 100vw, 270px" /></p>
  128. <p>You have 3 options here:</p>
  129. <ol>
  130. <li>First, you have the color which is used to change the color of the forecast line.</li>
  131. <li>Secondly, you have a style that can be dotted, solid, or dashed.</li>
  132. <li>Finally, you can configure the transparency of the forecast colors.</li>
  133. </ol>
  134. <p><img loading="lazy" width="795" height="494" class="wp-image-4148497" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/format-options.jpeg" data-lazy-load alt="format options" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/format-options.jpeg 795w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/format-options-300x186.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/format-options-768x477.jpeg 768w" sizes="(max-width: 795px) 100vw, 795px" /></p>
  135. <p>In addition, you can configure the <strong>Confidence band. </strong>By default, you fill in, but you can use lines or remove the confidence band using the none option. Finally, you have the option to modify the tooltip title of the forecast value.</p>
  136. <p><img loading="lazy" width="247" height="297" class="wp-image-4148498" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/confidence-band.png" data-lazy-load alt="Confidence band" /></p>
  137. <h2>Forecast data in Power BI</h2>
  138. <p>Sometimes you need to see the values instead of watching the chart only. To check a table instead of a chart, you can follow these steps:</p>
  139. <p>First, click on the <strong>More options </strong>icon.</p>
  140. <p>Secondly, select the <strong>Show as a table</strong> option.</p>
  141. <p><img loading="lazy" width="908" height="503" class="wp-image-4148499" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/show-as-a-table.png" data-lazy-load alt="Show as a table " data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/show-as-a-table.png 908w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/show-as-a-table-300x166.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/show-as-a-table-768x425.png 768w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/show-as-a-table-128x72.png 128w" sizes="(max-width: 908px) 100vw, 908px" /></p>
  142. <p>Finally, you will be able to see in a table the ForecastValue, the confidence Hight Bound and Low Bound values which were explained previously.</p>
  143. <p><img loading="lazy" width="821" height="254" class="wp-image-4148500" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-values.png" data-lazy-load alt="forecast values" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-values.png 821w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-values-300x93.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2023/02/forecast-values-768x238.png 768w" sizes="(max-width: 821px) 100vw, 821px" /></p>
  144. <h2>Conclusion</h2>
  145. <p>In this article, we saw how to forecast values. In our example, we predicted the order quantity values of the table purchasing.purchaseorderdetail table of the AdventureWorks database.</p>
  146. <p>Also, we understood how to configure the forecast properties including the format options. Finally, we learned how to show the forecast values instead of the table.</p>
  147. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/how-to-forecast-data-in-power-bi">How to Forecast Data in Power BI</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  148. ]]></content:encoded>
  152. </item>
  153. <item>
  154. <title>Meta AI by Facebook: A New Contender in the AI Battle</title>
  155. <link>https://www.sqlservercentral.com/articles/meta-ai-by-facebook-a-new-contender-in-the-ai-battle</link>
  157. <dc:creator><![CDATA[Daniel Calbimonte]]></dc:creator>
  158. <pubDate>Mon, 28 Apr 2025 00:00:47 +0000</pubDate>
  159. <category><![CDATA[Artificial Intelligence (AI)]]></category>
  160. <category><![CDATA[meta ai]]></category>
  161. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4559585</guid>
  162.  
  163. <description><![CDATA[<p>A short review of the Meta AI.</p>
  164. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/meta-ai-by-facebook-a-new-contender-in-the-ai-battle">Meta AI by Facebook: A New Contender in the AI Battle</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  165. ]]></description>
  166. <content:encoded><![CDATA[<h2>Introduction</h2>
  167. <p>Meta AI from <a href="https://www.facebook.com/">Facebook</a> is one of the last big technology giants’ contenders in the AI battle. In our first article, we created a <a href="https://www.sqlservercentral.com/articles/chatgpt-in-sql-server-practical-examples">demo about ChatGPT.</a> Secondly, we talked about <a href="https://www.sqlservercentral.com/articles/bing-chatbot-vs-chatgpt">Bing from Microsoft</a>, who is now <a href="https://www.sqlservercentral.com/articles/writing-sql-server-code-with-ai-using-github-copilot">Copilot</a>. Thirdly, we mentioned <a href="https://www.sqlservercentral.com/articles/bard-googles-chatgpt-competitor">Bard from Google,</a> which is <a href="https://www.sqlservercentral.com/articles/meet-the-new-google-ai-assistant-gemini">Gemini</a> now.</p>
  168. <p>However, Mark Zuckerberg and Facebook did not want to be out of the AI party. Now we have <a href="https://www.meta.ai/">Meta AI</a> incorporated into WhatsApp or in Facebook. We can also use Meta AI in the browser. In this article, we will talk about this new technology.</p>
  169. <h2>Meta AI history</h2>
  170. <p>Meta AI was created by the Facebook Artificial Intelligence Research (FAIR). This is the division of Meta Platforms (Facebook) in charge of the artificial intelligence research. In addition, Meta AI was founded on December 11, 2015 as Facebook Artificial Intelligence Research (FAIR). FAIR started with machine learning and artificial intelligence to understand intelligence and make machines significantly more intelligent. After that, they worked on face recognition, tagging in photographs, and personalized feed recommendations. In October 2021, Facebook rebranded itself as Meta Platforms, and focused on the "metaverse" and virtual reality environments.</p>
  171. <p>Currently, Meta is presenting the Meta AI to compete with ChatGPT, Gemini, Copilot, Deepseek, and other competitors.</p>
  172. <h2>Getting Started</h2>
  173. <p>Let’s start with a conversation with Meta AI.</p>
  174. <p><strong>Daniel: Hello. What are you?</strong></p>
  175. <p><strong>Meta AI:</strong> Hello! I'm Meta AI, a friendly AI assistant. I can help answer questions, provide information, generate text, and even create images. I'm constantly learning and improving, so please bear with me if I make any mistakes. How can I assist you today?</p>
  176. <p><strong>Daniel: Are you based in ChatGPT or are you a completely different technology?</strong></p>
  177. <p>We are both chatbots, but I was created by Meta, while ChatGPT was developed by OpenAI. Our training data, language understanding, and overall tone and purpose are unique, so we each have different strengths and capabilities.</p>
  178. <h2>Can Meta AI have access to the internet?</h2>
  179. <p><strong>Daniel: Can you navigate on the internet to get current data?</strong></p>
  180. <p><strong>Meta AI: </strong>I can't browse the internet or access real-time data. My responses are based on the data I was trained on, which is a massive dataset that was available to me when I was trained. However, my training data only goes up until 2022-2023, so I may not have the most up-to-date information on very recent events or developments.</p>
  181. <p><strong>Daniel: What are your strengths compared with Copilot and ChatGPT?</strong></p>
  182. <p><strong>Meta AI:</strong></p>
  183. <p>We all share some similarities, but I've been designed to be more conversational, concise, and helpful in a broader range of tasks and topics. Here are some of my strengths:</p>
  184. <ol>
  185. <li><strong>Conversational dialogue</strong>: I'm trained to engage in natural-sounding conversations, using a more casual tone and including relevant questions to clarify context.</li>
  186. <li><strong>Concise responses</strong>: I aim to provide clear and direct answers, focusing on the most important information and avoiding unnecessary elaboration.</li>
  187. <li><strong>Task-oriented</strong>: I can assist with a wide range of tasks, from generating creative content and providing definitions to solving math problems and translating languages.</li>
  188. <li><strong>Knowledge base</strong>: While not exhaustive, my training data covers a vast range of topics, including but not limited to history, science, entertainment, culture, geography, and more.</li>
  189. <li><strong>Multilingual support</strong>: I can understand and respond in multiple languages, making me more accessible to users worldwide.</li>
  190. <li><strong>Continuous learning</strong>: My training data is constantly updated and expanded to keep my knowledge up-to-date and accurate.</li>
  191. </ol>
  192. <p>Keep in mind that each AI model has its unique strengths and weaknesses. If you have specific needs or preferences, you may find that one model works better for you than others.</p>
  193. <h2>Meta AI opinion about SQL Server 2022</h2>
  194. <p><strong>Daniel: What is your opinion about SQL Server 2022?</strong></p>
  195. <p><strong>Meta AI:</strong> SQL Server 2022 is a powerful relational database management system (RDBMS) that offers numerous enhancements and improvements over its predecessors. Here are some key features and benefits:</p>
  196. <h2>Key Features</h2>
  197. <ol>
  198. <li><strong>Improved Performance</strong>: SQL Server 2022 introduces several performance enhancements, including improved query optimization, faster data processing, and better resource utilization.</li>
  199. <li><strong>Enhanced Security</strong>: The new version includes advanced security features, such as Azure Active Directory (AAD) authentication, encryption, and access control.</li>
  200. <li><strong>Data Virtualization</strong>: SQL Server 2022 allows you to integrate data from various sources, including Azure Blob Storage, Azure Data Lake Storage, and other databases.</li>
  201. <li><strong>Cloud-Native Capabilities</strong>: The database engine is designed to work seamlessly with cloud environments, providing features like elastic scaling, automated patching, and backup.</li>
  202. <li><strong>Improved Analytics</strong>: SQL Server 2022 includes advanced analytics capabilities, including machine learning, data warehousing, and business intelligence.</li>
  203. </ol>
  204. <h2>Benefits</h2>
  205. <ol>
  206. <li><strong>Faster Insights</strong>: With improved performance and analytics capabilities, you can gain faster insights from your data.</li>
  207. <li><strong>Enhanced Collaboration</strong>: SQL Server 2022 enables seamless collaboration between developers, data scientists, and business users.</li>
  208. <li><strong>Increased Agility</strong>: The database engine provides a flexible and scalable platform for developing and deploying applications.</li>
  209. <li><strong>Better Data Governance</strong>: With advanced security and access control features, you can ensure that your data is secure and compliant with regulatory requirements.</li>
  210. </ol>
  211. <p>Overall, SQL Server 2022 is a robust and feature-rich database management system that can help organizations unlock insights, improve collaboration, and drive business success.</p>
  212. <p>Do you believe that SQL Server on premises will stop and everything will migrate to the cloud?</p>
  213. <p>While cloud adoption is increasingly popular, I don't think SQL Server on-premises will completely disappear. Here's why:</p>
  214. <h2>Reasons for On-Premises Persistence</h2>
  215. <ol>
  216. <li><strong>Data Sovereignty</strong>: Some organizations, especially those in regulated industries, require strict control over their data and may not be comfortable hosting it in the cloud.</li>
  217. <li><strong>Security Concerns</strong>: On-premises environments can provide an additional layer of security, as data is stored within the organization's own infrastructure.</li>
  218. <li><strong>Legacy Systems</strong>: Many organizations have invested heavily in on-premises infrastructure and may not be able to migrate their legacy systems to the cloud easily.</li>
  219. <li><strong>Low Latency Requirements</strong>: Certain applications, such as real-time analytics or high-frequency trading, require ultra-low latency, which may be difficult to achieve in a cloud environment.</li>
  220. </ol>
  221. <h2>Hybrid Approach</h2>
  222. <ol>
  223. <li><strong>Cloud-First Strategy</strong>: Many organizations are adopting a cloud-first strategy, where new applications and workloads are deployed in the cloud, while existing on-premises workloads are maintained.</li>
  224. <li><strong>Hybrid Cloud</strong>: Some organizations are using a hybrid cloud approach, where they use a combination of on-premises and cloud infrastructure to meet their needs.</li>
  225. </ol>
  226. <h2>Microsoft's Strategy</h2>
  227. <p>Microsoft has stated that they will continue to support and invest in SQL Server on-premises, while also providing a clear path for customers to migrate to the cloud when they're ready.</p>
  228. <p>In summary, while the cloud will continue to grow in popularity, I don't think SQL Server on-premises will completely disappear. Instead, organizations will adopt a hybrid approach, using a combination of on-premises and cloud infrastructure to meet their needs.</p>
  229. <h2>Create some T-SQL Code with Meta AI</h2>
  230. <p><strong>Me:</strong> It is not bad, let’s take a look at the code created. Also, can you generate some T-SQL tables with data? Please apply best practices. Based on the tables, create a view and a stored procedure.</p>
  231. <p><strong>Meta AI: </strong>Here's an example of T-SQL tables with data, a view, and a stored procedure, following best practices:</p>
  232. <h2>Tables</h2>
  233. <p>SQL</p>
  234. <pre class="prettyprint  lang-mssql">-- Create schema
  235. CREATE SCHEMA Sales
  236. GO
  237. -- Create tables
  238. CREATE TABLE Sales.Customers (
  239. CustomerId INT PRIMARY KEY IDENTITY(1,1),
  240. FirstName NVARCHAR(50) NOT NULL,
  241. LastName NVARCHAR(50) NOT NULL,
  242. Email NVARCHAR(100) UNIQUE NOT NULL,
  243. CreatedDate DATETIME DEFAULT GETDATE()
  244. );
  245. CREATE TABLE Sales.Orders (
  246. OrderId INT PRIMARY KEY IDENTITY(1,1),
  247. CustomerId INT NOT NULL,
  248. OrderDate DATETIME DEFAULT GETDATE(),
  249. Total DECIMAL(10, 2) NOT NULL,
  250. CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES Sales.Customers(CustomerId)
  251. );
  252. CREATE TABLE Sales.OrderItems (
  253. OrderItemId INT PRIMARY KEY IDENTITY(1,1),
  254. OrderId INT NOT NULL,
  255. ProductName NVARCHAR(100) NOT NULL,
  256. Quantity INT NOT NULL,
  257. UnitPrice DECIMAL(10, 2) NOT NULL,
  258. CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (OrderId) REFERENCES Sales.Orders(OrderId)
  259. );</pre>
  260. <h2>Data</h2>
  261. <p>SQL</p>
  262. <pre class="prettyprint  lang-mssql">-- Insert data
  263. INSERT INTO Sales.Customers (FirstName, LastName, Email)
  264. VALUES
  265. ('John', 'Doe', 'john.doe@example.com'),
  266. ('Jane', 'Doe', 'jane.doe@example.com'),
  267. ('Bob', 'Smith', 'bob.smith@example.com');
  268. INSERT INTO Sales.Orders (CustomerId, Total)
  269. VALUES
  270. (1, 100.00),
  271. (1, 200.00),
  272. (2, 50.00),
  273. (3, 75.00);
  274. INSERT INTO Sales.OrderItems (OrderId, ProductName, Quantity, UnitPrice)
  275. VALUES
  276. (1, 'Product A', 2, 25.00),
  277. (1, 'Product B', 1, 50.00),
  278. (2, 'Product C', 3, 33.33),
  279. (3, 'Product D', 1, 50.00),
  280. (4, 'Product E', 2, 18.75);</pre>
  281. <h2>View:</h2>
  282. <p>SQL</p>
  283. <pre class="prettyprint  lang-mssql">-- Create view
  284. CREATE VIEW Sales.CustomerOrders
  285. AS
  286. SELECT
  287. C.CustomerId,
  288. C.FirstName,
  289. C.LastName,
  290. O.OrderId,
  291. O.OrderDate,
  292. O.Total
  293. FROM
  294. Sales.Customers C
  295. INNER JOIN
  296. Sales.Orders O ON C.CustomerId = O.CustomerId;</pre>
  297. <h2>Stored Procedure:</h2>
  298. <p>SQL</p>
  299. <pre class="prettyprint  lang-mssql">-- Create stored procedure
  300. CREATE PROCEDURE Sales.GetCustomerOrders
  301. @CustomerId INT
  302. AS
  303. BEGIN
  304. SELECT
  305. O.OrderId,
  306. O.OrderDate,
  307. O.Total
  308. FROM
  309. Sales.Orders O
  310. WHERE
  311. O.CustomerId = @CustomerId
  312. ORDER BY
  313. O.OrderDate DESC;
  314. END;
  315. GO</pre>
  316. <p>To execute the stored procedure:</p>
  317. <p>SQL</p>
  318. <pre class="prettyprint  lang-mssql">EXEC Sales.GetCustomerOrders @CustomerId = 1;</pre>
  319. <p><strong>Me: </strong>I tested the code. Also, I created the objects and tested the data. The code worked fine.</p>
  320. <h2>Meta AI in WhatsApp</h2>
  321. <p>Now, you can talk to Meta AI using your WhatsApp. Also, you can use the search option to ask Meta AI or press the Meta AI icon to use this service:</p>
  322. <p><img loading="lazy" width="757" height="1600" class="wp-image-4559586" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/03/meta-ai-options-1.png" data-lazy-load alt="Meta AI options" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/03/meta-ai-options-1.png 757w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/meta-ai-options-1-142x300.png 142w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/meta-ai-options-1-484x1024.png 484w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/meta-ai-options-1-727x1536.png 727w" sizes="(max-width: 757px) 100vw, 757px" /></p>
  323. <p>It works in the same way.</p>
  324. <p><strong>Daniel:</strong> Also, create a T-SQL query to detect the space used by a database.</p>
  325. <p><strong>Meta AI: </strong>Here is a T-SQL query that detects the space used by a database:</p>
  326. <pre class="prettyprint  lang-mssql">SELECT
  327. db.name AS DatabaseName,
  328. CAST((SUM(file_size) * 8192.0) / 1048576.0 AS DECIMAL(10, 2)) AS TotalSpaceUsedMB
  329. FROM
  330. sys.master_files file
  331. INNER JOIN
  332. sys.databases db ON file.database_id = db.database_id
  333. WHERE
  334. db.name = 'YourDatabaseName'
  335. GROUP BY
  336. db.name</pre>
  337. <p>Replace 'YourDatabaseName' with the name of the database you want to check.</p>
  338. <p>This query works by:</p>
  339. <p>1. Joining the sys.master_files and sys.databases system views to get the file size and database name.</p>
  340. <p>2. Filtering the results to the specified database.</p>
  341. <p>3. Grouping the results by database name.</p>
  342. <p>4. Calculating the total space used in megabytes (MB).</p>
  343. <p>Note: The file_size column in sys.master_files is in units of 8192 bytes (8KB), so we multiply by 8192.0 and then divide by 1048576.0 to convert to megabytes.Drawing with Meta AI</p>
  344. <p>Also, you can create images with Meta AI in your WhatsApp or browser. However, depending on your country, this feature may be restricted.</p>
  345. <h2>Drawing in Meta AI</h2>
  346. <p><strong>Me: Please draw a corrupted database.</strong></p>
  347. <p><strong>Meta AI:</strong></p>
  348. <p><img loading="lazy" width="1280" height="1280" class="wp-image-4559587" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1.jpeg" data-lazy-load alt="corrupt database" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1.jpeg 1280w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1-300x300.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1-1024x1024.jpeg 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1-150x150.jpeg 150w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/corrupt-database-1-768x768.jpeg 768w" sizes="(max-width: 1280px) 100vw, 1280px" /></p>
  349. <p><strong>Me:</strong> Mmmm…Can you generate a video of a database flying?</p>
  350. <p><strong>Meta ai:</strong></p>
  351. <p><img loading="lazy" width="1280" height="1280" class="wp-image-4559588" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1.jpeg" data-lazy-load alt="Database flying" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1.jpeg 1280w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1-300x300.jpeg 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1-1024x1024.jpeg 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1-150x150.jpeg 150w, https://www.sqlservercentral.com/wp-content/uploads/2025/03/database-flying-1-768x768.jpeg 768w" sizes="(max-width: 1280px) 100vw, 1280px" /></p>
  352. <p>Daniel: It just created an image. It does not look like a database.</p>
  353. <h2>Conclusion</h2>
  354. <p>To conclude we can say that meta AI is currently similar to the competitors. However, if it is fully integrated to Facebook, Instagram, and WhatsApp, it will be really useful for our daily tasks.</p>
  355. <p>Also, it will be a great assistant in the long run if it can understand our voice and do some tasks automatically.</p>
  356. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/meta-ai-by-facebook-a-new-contender-in-the-ai-battle">Meta AI by Facebook: A New Contender in the AI Battle</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  357. ]]></content:encoded>
  361. </item>
  362. <item>
  363. <title>Using try catch in SQL Server stored procedures</title>
  364. <link>https://www.sqlservercentral.com/articles/using-try-catch-in-sql-server-stored-procedures</link>
  366. <dc:creator><![CDATA[Additional Articles]]></dc:creator>
  367. <pubDate>Mon, 28 Apr 2025 00:00:39 +0000</pubDate>
  368. <category><![CDATA[Error Handling]]></category>
  369. <category><![CDATA[TRY..CATCH]]></category>
  370. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4583651</guid>
  371.  
  372. <description><![CDATA[<p>A great new option that has been around since SQL Server 2005 is the ability to use the Try..Catch paradigm that exists in other development languages.  Doing error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors.</p>
  373. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/using-try-catch-in-sql-server-stored-procedures">Using try catch in SQL Server stored procedures</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  374. ]]></description>
  375. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/using-try-catch-in-sql-server-stored-procedures">Using try catch in SQL Server stored procedures</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  376. ]]></content:encoded>
  380. </item>
  381. <item>
  382. <title>Retrieving Data Using the REST API in Data API Builder</title>
  383. <link>https://www.sqlservercentral.com/articles/retrieving-data-using-the-rest-api-in-data-api-builder</link>
  385. <dc:creator><![CDATA[Steve Jones - SSC Editor]]></dc:creator>
  386. <pubDate>Fri, 25 Apr 2025 00:00:11 +0000</pubDate>
  387. <category><![CDATA[Data API Builder (DAB)]]></category>
  388. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4583128</guid>
  389.  
  390. <description><![CDATA[<p>I wrote a piece on getting started with the Data API Builder (DAB), which looked at how this product works and how to start using it. That was a basic look, but it gives data professionals an idea of how this might help them work more closely with front-end developers who are more comfortable with [&#8230;]</p>
  391. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/retrieving-data-using-the-rest-api-in-data-api-builder">Retrieving Data Using the REST API in Data API Builder</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  392. ]]></description>
  393. <content:encoded><![CDATA[<p>I wrote a piece on getting started with the Data API Builder (DAB), which looked at how this product works and how to start using it. That was a basic look, but it gives data professionals an idea of how this might help them work more closely with front-end developers who are more comfortable with API access to data and dislike SQL. In this article, I want to continue to dive into DAB with a more detailed look at the REST API and how one might perform queries against the tables you have exposed from your database.</p>
  394. <p>Note that the REST API is lightly documented at MS Learn: <a href="https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http">https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http</a></p>
  395. <h2>Pre-requisites</h2>
  396. <p>I will assume you know how to</p>
  397. <ul>
  398. <li>create a DAB configuration file and add entities to the file.</li>
  399. <li>start the DAB process and find the REST API base URL</li>
  400. <li>configure this for SQL Server</li>
  401. <li>have <a href="https://www.postman.com/">Postman</a> installed (not required, but nice)</li>
  402. </ul>
  403. <p>It should be the same for other platforms, though I haven't tested those. I will also be showing examples in Postman as it makes rendering results easier. You can do this from a web browser, but it's harder to read results (and more annoying to me), or you can use any other method you want that supports accessing an http endpoint and rendering JSON results.</p>
  404. <h2>The REST API</h2>
  405. <p>If you are a data professional, you might not be familiar with REST APIs. These are a common way of accessing data resources, or changing them, in much of software development. These are often http URL addressed resources, with the API being a structured view over the underlying resources. You can read more about REST from <a href="https://www.redhat.com/en/topics/api/what-is-a-rest-api">Red Hat</a> or <a href="https://aws.amazon.com/what-is/restful-api/">AWS</a>, but it's not required. Essentially for us data professionals, we are exposing our database objects through an HTTP endpoint.</p>
  406. <p>As an example, I have DAB running against the Northwind database and the Customers table exposed. The base URL for my DAB endpoint is: http://localhost:5000/api. However, this doesn't provide my anything but an error:</p>
  407. <p><img loading="lazy" class="alignnone size-full wp-image-4583158" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0142.png" data-lazy-load alt="REST error" width="813" height="167" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0142.png 813w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0142-300x62.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0142-768x158.png 768w" sizes="(max-width: 813px) 100vw, 813px" /></p>
  408. <p>This is because there isn't any data to return. Instead, in my config file, I have added the dbo.Customers table, and it is accessible as http://localhost:5000/api/Customers. Note all the results below, which are hard to read.</p>
  409. <p><img loading="lazy" class="alignnone size-full wp-image-4583159" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0143.png" data-lazy-load alt="DAB returning customers" width="809" height="370" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0143.png 809w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0143-300x137.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0143-768x351.png 768w" sizes="(max-width: 809px) 100vw, 809px" /></p>
  410. <p>This is a JSON document, with lots of elements, arrays, and more. If you are familiar with Northwind, this data looks familiar, but it's hard to consume. In .NET, there are plenty of easy ways to de-serialize this data into a series of rows and columns that could populate a table or a grid or anything the developer wants to fill with data.</p>
  411. <p>That's a simple look at the REST API, now, let's look at what we can do with it.</p>
  412. <p>Note, in the rest of the article I'll be using Postman to submit the URL and get results back.</p>
  413. <h2>Selecting Certain Columns</h2>
  414. <p>In the example above, for the base URL for Customers, I get all customers from the table, both all rows and all columns. This is essentially a SELECT * with no WHERE clause. Not what we want most developers to do from their applications.</p>
  415. <p>Instead, we would like them to be selective about what is returned. Choose certain columns of data. I'll start to limit data in this section by only picking some columns. In the next section, we will add the filtering of a WHERE clause.</p>
  416. <p>The REST API in DAB includes a <a href="https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http#select">$select parameter</a>, which can be used as the column list to be returned. The basic syntax is:</p>
  417. <p>/api/entity?$select=column1,column2,column3</p>
  418. <p>Here we give the entity name, use a ? to indicate parameters are coming and then use $select as the parameter. For my Customers table, I construct a URL like this to get the CustomerID and Company Name: http://localhost:5000/api/Customers?$select=CustomerID,CompanyName</p>
  419. <p>When I run this, I see this data. Note that Postman gives me a table in which I can edit parameters below the URL. All customers are shown in the results, but only those two columns.</p>
  420. <p><img loading="lazy" class="alignnone size-full wp-image-4583162" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0144.png" data-lazy-load alt="Getting All Customers with jsut the ID and name" width="725" height="564" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0144.png 725w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0144-300x233.png 300w" sizes="(max-width: 725px) 100vw, 725px" /></p>
  421. <p>If I wanted another column, like ContactName, I add it to my URL. The call is now:</p>
  422. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Customers?$select=CustomerID,CompanyName,ContactName</pre>
  423. <p>Here are the results:</p>
  424. <p><img loading="lazy" class="alignnone size-full wp-image-4583163" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0145.png" data-lazy-load alt="resutlts with id, name, and contact" width="871" height="814" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0145.png 871w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0145-300x280.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0145-768x718.png 768w" sizes="(max-width: 871px) 100vw, 871px" /></p>
  425. <p>One caveat here is that DAB is case sensitive in the REST API. Your casing must match what's in the DB. Here is a query in SSMS that works. Note that I've lowercased the ContactName field:</p>
  426. <p><img loading="lazy" class="alignnone size-full wp-image-4583165" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0147.png" data-lazy-load alt="Query with mixed case in code" width="519" height="424" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0147.png 519w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0147-300x245.png 300w" sizes="(max-width: 519px) 100vw, 519px" /></p>
  427. <p>In the REST API, the equivalent query is:</p>
  428. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Customers?$select=CustomerID,CompanyName,contactname</pre>
  429. <p>This, however, returns an error:</p>
  430. <p><img loading="lazy" class="alignnone size-full wp-image-4583166" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0148.png" data-lazy-load alt="Error in REST API from wrong case" width="753" height="490" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0148.png 753w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0148-300x195.png 300w" sizes="(max-width: 753px) 100vw, 753px" /></p>
  431. <p>I haven't tested how many columns can be added, but I assume there isn't a limit in DAB. I've added many, and they seem to work.</p>
  432. <p><img loading="lazy" class="alignnone size-full wp-image-4583167" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0149.png" data-lazy-load alt="Getting Many Columns" width="1018" height="665" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0149.png 1018w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0149-300x196.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0149-768x502.png 768w" sizes="(max-width: 1018px) 100vw, 1018px" /></p>
  433. <p>I can also rearrange the columns. In this URL, I've changed the order of columns, and I've duplicated one column:</p>
  434. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Customers?$select=CompanyName,ContactTitle,ContactName,Country,CustomerID,ContactName,ContactTitle,Address,City,Region,Country</pre>
  435. <p>In the results, however, I don't see duplicate data. DAB has removed the duplicate.</p>
  436. <p><img loading="lazy" class="alignnone size-full wp-image-4583168" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0150.png" data-lazy-load alt="Query results with reordered columns and duplicate" width="1183" height="727" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0150.png 1183w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0150-300x184.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0150-1024x629.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0150-768x472.png 768w" sizes="(max-width: 1183px) 100vw, 1183px" /></p>
  437. <p>Behind the scenes, this is an sp_executesql query that looks like this (I've cleaned this up slightly to make it easier to read):</p>
  438. <pre class="prettyprint  lang-mssql">exec sp_executesql N'
  439. SELECT TOP 101
  440.  [dbo_customers].[CompanyName] AS [CompanyName]
  441. , [dbo_customers].[ContactTitle] AS [ContactTitle]
  442. , [dbo_customers].[ContactName] AS [ContactName]
  443. , [dbo_customers].[Country] AS [Country]
  444. , [dbo_customers].[CustomerID] AS [CustomerID]
  445. , [dbo_customers].[Address] AS [Address]
  446. , [dbo_customers].[City] AS [City]
  447. , [dbo_customers].[Region] AS [Region]
  448. FROM [dbo].[customers] AS [dbo_customers]
  449. WHERE 1 = 1
  450. ORDER BY [dbo_customers].[CustomerID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES
  451. ',N'@param0 nvarchar(11),@param1 nvarchar(12),@param2 nvarchar(11),@param3 nvarchar(7),@param4 nvarchar(10),@param5 nvarchar(7),@param6 nvarchar(4),@param7 nvarchar(6)',@param0=N'CompanyName',@param1=N'ContactTitle',@param2=N'ContactName',@param3=N'Country',@param4=N'CustomerID',@param5=N'Address',@param6=N'City',@param7=N'Region'
  452. </pre>
  453. <p>Note that the column list doesn't include the duplicate column.</p>
  454. <p>It's great we can reorder which columns appear, but often we don't want all rows. Let's look at filtering next.</p>
  455. <h2>Filtering API Results</h2>
  456. <p>The WHERE clause is a filter for our queries. In the REST API, we use the <a href="https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http#filter">$filter parameter</a>. Like $select, this will allow us to add values to limit results. However, unlike $select, this is an expression, and the format is this:</p>
  457. <p>/api/entity?$filter=column [operator] value</p>
  458. <p>That's the basic structure. The operators are:</p>
  459. <ul>
  460. <li>eq - equal</li>
  461. <li>ne - not equal</li>
  462. <li>gt - greater than</li>
  463. <li>lt - less than</li>
  464. <li>ge - greater than or equal</li>
  465. <li>le - less than or equal</li>
  466. </ul>
  467. <p>The value can be a number. If it is a string, enclose it in single quotes. For dates, I'll show that below. One thing to note, for a dev, they need to URLEncode their $filter parameters.</p>
  468. <p>Here are a couple examples to illustrate how this works. First, let's only get one customer. We can do this with a URL that uses the eq operator and the CustomerID column. The URL would be:</p>
  469. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Customers?$filter=CustomerID eq 'ALFKI'</pre>
  470. <p>This returns just this customer:</p>
  471. <p><img loading="lazy" class="alignnone size-full wp-image-4583170" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0151.png" data-lazy-load alt="Return one customer" width="1024" height="652" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0151.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0151-300x191.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0151-768x489.png 768w" sizes="(max-width: 1024px) 100vw, 1024px" /></p>
  472. <p>Every column in dbo.Customers is a string field, so let me switch to dbo.Orders for the next example. If I want just one particular order, I can use the order ID. My URL would be:</p>
  473. <pre class="prettyprint  lang-plain"> http://localhost:5000/api/Orders?$filter=OrderID eq 10253</pre>
  474. <p>Note I have spaces in my expression and URL and it works. Look at my results:</p>
  475. <p><img loading="lazy" class="alignnone size-full wp-image-4583175" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0152.png" data-lazy-load alt="Retrieve one order" width="858" height="821" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0152.png 858w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0152-300x287.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0152-768x735.png 768w" sizes="(max-width: 858px) 100vw, 858px" /></p>
  476. <p>For a date range, I have a slightly different format. I can't use quotes or the OData style (datetime'yyyy-mm-dd'). Instead, I need to use a datetime offset value. I can still use a gt or ge expression. For example, dates for orders in Northwind range from 1996-07-04 to 1998-05-06. Let's get just the 1998 orders with this URL:</p>
  477. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Orders?$filter=OrderDate ge 1998-01-01T00:00:00Z</pre>
  478. <p>The results are here:</p>
  479. <p><img loading="lazy" class="alignnone size-full wp-image-4583176" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0154.png" data-lazy-load alt="date filtering to 1998" width="667" height="889" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0154.png 667w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0154-225x300.png 225w" sizes="(max-width: 667px) 100vw, 667px" /></p>
  480. <p>I can also combine multiple expressions with AND/OR/NOT, as I would in a WHERE Clause. I can also use parenthesis to group my logical expressions. In the examples below, I'm going to limit what I return and combine a few logical filters. First, let's get a limited set of data from orders that were shipped to France. The URL for this is:</p>
  481. <pre class="prettyprint  lang-plain">http://localhost:5000/api/Orders?$filter=ShipCountry eq 'France'&amp;$select=CustomerID,OrderID,ShipCountry,OrderDate</pre>
  482. <p>I now see just the date, customer, order, and country.</p>
  483. <p><img loading="lazy" class="alignnone size-full wp-image-4583177" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0155.png" data-lazy-load alt="French Orders" width="1011" height="854" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0155.png 1011w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0155-300x253.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0155-768x649.png 768w" sizes="(max-width: 1011px) 100vw, 1011px" /></p>
  484. <p>That's still 77 orders. Let's further limit this to just those orders with a number &gt; 11000. I do that by adding values to the URL. Now I query with:</p>
  485. <pre class="prettyprint  lang-mssql">http://localhost:5000/api/Orders?$filter=ShipCountry eq 'France' and OrderID gt 11000&amp;$select=CustomerID,OrderID,ShipCountry,OrderDate</pre>
  486. <p>I get three results.</p>
  487. <p><img loading="lazy" class="alignnone size-full wp-image-4583178" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0156.png" data-lazy-load alt="Orders &gt; 11000" width="1084" height="870" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0156.png 1084w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0156-300x241.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0156-1024x822.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0156-768x616.png 768w" sizes="(max-width: 1084px) 100vw, 1084px" /></p>
  488. <p>Perhaps I also want to include a few orders from a different customer. Let's group our previous query and add an OR filter. This gives us a URL of:</p>
  489. <pre class="prettyprint  lang-mssql">http://localhost:5000/api/Orders?$filter=(ShipCountry eq 'France' and OrderID gt 11000) or CustomerID eq 'VINET'&amp;$select=CustomerID,OrderID,ShipCountry,OrderDate</pre>
  490. <p>Now I get five more rows. There are 5 VINET orders, and you can see the last one with the 3 orders from the previous query.</p>
  491. <p><img loading="lazy" class="alignnone size-full wp-image-4583181" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0158.png" data-lazy-load alt="Complex filtering" width="1149" height="905" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0158.png 1149w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0158-300x236.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0158-1024x807.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0158-768x605.png 768w" sizes="(max-width: 1149px) 100vw, 1149px" /></p>
  492. <p>Just as you can in SQL, you can build up some expressions here. I don't know the limits of DAB, but I would also expect these not to be too complex as this is designed to replace relatively basic CRUD operations from an application.</p>
  493. <p>We have looked at limiting results in a few ways, but what about ordering? Clients can order results, and if the set is small the should, but there is power in letting the server do some of this work. Let's look at adding an ORDER BY clause.</p>
  494. <h2>Ordering Results</h2>
  495. <p>My previous results were in whatever order the server wanted to return them to me. However, if I wanted to ensure a certain order, there is an <a href="https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http#orderby">$orderby parameter</a> in DAB. As you might expect, this equates to the ORDER BY keywords in T-SQL, and you can add a list of columns (case-sensitive). The structure is</p>
  496. <p>/api/entity?$orderby=column1,column2</p>
  497. <p>For my last query, I have a complex expression, but let's now order all the results by the employee that took the order. To do that, I add a new parameter and then the $orderby keyword. That give me this URL:</p>
  498. <pre class="prettyprint  lang-mssql">http://localhost:5000/api/Orders?$filter=(ShipCountry eq 'France' and OrderID gt 11000) or CustomerID eq 'VINET'&amp;$select=CustomerID,OrderID,ShipCountry,OrderDate&amp;$orderby=EmployeeID</pre>
  499. <p>My results show the VINET orders mixed in with the others that are limited by country and date. I've ordered by a column I'm not retrieving, just as I can in T-SQL.</p>
  500. <p><img loading="lazy" class="alignnone size-full wp-image-4583182" src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0159.png" data-lazy-load alt="Ordered Results" width="1279" height="862" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0159.png 1279w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0159-300x202.png 300w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0159-1024x690.png 1024w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/2025-04_0159-768x518.png 768w" sizes="(max-width: 1279px) 100vw, 1279px" /></p>
  501. <p>I can add multiple columns, but I'm sure you understand how to use this parameter.</p>
  502. <h2>Summary</h2>
  503. <p>There are a few more things you can do with DAB to retrieve data, but I'll cover those in another article. In this one, we've seen how to limit the columns returned, the rows, and to order them. These equate to a column list, a WHERE clause, and an ORDER BY clause in T-SQL. Most developers likely understand these concepts well, and the only strange thing might be encoding dates with the datetimeoffset value for the parameter when used in expressions.</p>
  504. <p>DAB is a way to speed up front-end development, and a way that helps DBAs control what they expose to developers. This can be a nice front end for the database, limiting what data certain applications can access without adding any complex RBAC rules with GRANT and REVOKE statements in the database. Of course, users do need to be granted permissions, but this is one further way to keep your security simple and help developers access data in a way that they are comfortable with working.</p>
  505. <p>Give it a try today and experiment with the Data API Builder. It can speed up a lot of front end drudgery with those simple CRUD applications.</p>
  506. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/retrieving-data-using-the-rest-api-in-data-api-builder">Retrieving Data Using the REST API in Data API Builder</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  507. ]]></content:encoded>
  511. </item>
  512. <item>
  513. <title>Managed Instance Gotchas – Error Conditions</title>
  514. <link>https://www.sqlservercentral.com/articles/managed-instance-gotchas-error-conditions</link>
  516. <dc:creator><![CDATA[Additional Articles]]></dc:creator>
  517. <pubDate>Fri, 25 Apr 2025 00:00:31 +0000</pubDate>
  518. <category><![CDATA[Azure SQL Managed Instance]]></category>
  519. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4575514</guid>
  520.  
  521. <description><![CDATA[<p>I was recently on a project to migrate a very transactional installation of SQL Server to Azure SQL Managed Instance (MI). SQL Managed Instance is a good stepping stone between a full, on-prem SQL instance / Azure VM and an Azure SQL Database. </p>
  522. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/managed-instance-gotchas-error-conditions">Managed Instance Gotchas – Error Conditions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  523. ]]></description>
  524. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/managed-instance-gotchas-error-conditions">Managed Instance Gotchas – Error Conditions</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  525. ]]></content:encoded>
  529. </item>
  530. <item>
  531. <title>Using the CASE expression instead of dynamic SQL in SQL Server</title>
  532. <link>https://www.sqlservercentral.com/articles/using-the-case-expression-instead-of-dynamic-sql-in-sql-server</link>
  534. <dc:creator><![CDATA[Additional Articles]]></dc:creator>
  535. <pubDate>Wed, 23 Apr 2025 00:00:31 +0000</pubDate>
  536. <category><![CDATA[T-SQL]]></category>
  537. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4568817</guid>
  538.  
  539. <description><![CDATA[<p>I have a lot of SQL update queries where I need to make IF/ELSE types of decisions. In some cases, I am using a cursor but looping through thousands of rows for update takes a long time. I also use some dynamic SQL to handle some query parameter decision making. Is there a better alternative?</p>
  540. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/using-the-case-expression-instead-of-dynamic-sql-in-sql-server">Using the CASE expression instead of dynamic SQL in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  541. ]]></description>
  542. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/using-the-case-expression-instead-of-dynamic-sql-in-sql-server">Using the CASE expression instead of dynamic SQL in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  543. ]]></content:encoded>
  547. </item>
  548. <item>
  549. <title>Understanding CROSS APPLY and OUTER APPLY in SQL Server</title>
  550. <link>https://www.sqlservercentral.com/articles/understanding-cross-apply-and-outer-apply-in-sql-server</link>
  552. <dc:creator><![CDATA[Noman072]]></dc:creator>
  553. <pubDate>Mon, 21 Apr 2025 00:00:41 +0000</pubDate>
  554. <category><![CDATA[cross apply]]></category>
  555. <category><![CDATA[T-SQL]]></category>
  556. <category><![CDATA[CROSS APPLY]]></category>
  557. <category><![CDATA[INNER JOIN]]></category>
  558. <category><![CDATA[left join]]></category>
  559. <category><![CDATA[OUTER APPLY]]></category>
  560. <category><![CDATA[SQL Basics]]></category>
  561. <category><![CDATA[SQL Function]]></category>
  562. <category><![CDATA[SQL Join]]></category>
  563. <category><![CDATA[SQL optimization]]></category>
  564. <category><![CDATA[SQL performance]]></category>
  565. <category><![CDATA[SQL programming]]></category>
  566. <category><![CDATA[SQL Queries]]></category>
  567. <category><![CDATA[SQL Query Examples]]></category>
  568. <category><![CDATA[SQL Server]]></category>
  569. <category><![CDATA[SQL Subquery]]></category>
  570. <category><![CDATA[SQL tutorial]]></category>
  571. <category><![CDATA[Table-Valued Functions]]></category>
  572. <category><![CDATA[TVF]]></category>
  573. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4544634</guid>
  574.  
  575. <description><![CDATA[<p>Learn the differences between CROSS APPLY and OUTER APPLY in SQL.</p>
  576. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/understanding-cross-apply-and-outer-apply-in-sql-server">Understanding CROSS APPLY and OUTER APPLY in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  577. ]]></description>
  578. <content:encoded><![CDATA[<h2 data-start="609" data-end="630"><strong data-start="614" data-end="630">Introduction</strong></h2>
  579. <p>When working with table-valued functions (TVFs) or subqueries, the CROSS APPLY and OUTER APPLY operators provide flexible ways to enhance SQL queries. While these operators might seem complex at first, understanding how they function can significantly improve both query performance and readability.</p>
  580. <p>In this article, we will break down how each operator works with practical examples and clearly explain their behavior when using TVFs, a common scenario. We will also address common points of confusion to help you gain a solid understanding of these operators.</p>
  581. <h2 data-start="1207" data-end="1255"><strong data-start="1212" data-end="1255">What are Table-Valued Functions (TVFs)?</strong></h2>
  582. <p>A table-valued function (TVF) is a special type of SQL function that returns a table as its result. TVFs are used to encapsulate complex logic and make queries more modular. You can use a TVF like a regular table in a query, which makes it a powerful tool when combined with CROSS APPLY or OUTER APPLY.</p>
  583. <p>Let’s clarify how the APPLY operators interact with TVFs:</p>
  584. <ul>
  585. <li>CROSS APPLY: Joins each row from the left table with the result set from the TVF. It will only return rows where there is a match between the left table and the right-side expression (the result of the TVF).</li>
  586. <li>OUTER APPLY: Similar to CROSS APPLY, but ensures that all rows from the left table are returned, even if there is no matching row in the right-side expression (the TVF).</li>
  587. </ul>
  588. <p data-start="2028" data-end="2081"><strong>Step 1: Create Sample Tables and Insert Data</strong></p>
  589. <p>Before diving into examples, we need to set up a database with some sample data. Let’s create two tables: Departments and Employees.</p>
  590. <pre class="prettyprint  lang-mssql">-- Create the Departments table
  591. DROP TABLE IF EXISTS #Departments;
  592. CREATE TABLE #Departments (DepartmentID INT, Name NVARCHAR(50));
  593. INSERT INTO #Departments (DepartmentID, Name)
  594. VALUES
  595. (1, 'Engineering'),
  596. (2, 'Sales'),
  597. (3, 'HR'),
  598. (4, 'Marketing'); -- Marketing department without employees
  599. -- Create the Employees table
  600. DROP TABLE IF EXISTS #Employees;
  601. CREATE TABLE #Employees (EmployeeID INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), DepartmentID INT, YearlySalary INT);
  602. INSERT INTO #Employees (EmployeeID, FirstName, LastName, DepartmentID, YearlySalary)
  603. VALUES
  604. (1, 'John', 'Doe', 1, 80000),
  605. (2, 'Jane', 'Smith', 1, 90000),
  606. (3, 'Paul', 'Johnson', 2, 75000),
  607. (4, 'Emma', 'Wilson', 3, 70000);
  608. </pre>
  609. <p>Explanation: This step sets up two tables:</p>
  610. <p>Departments: Holds department data.</p>
  611. <p>Employees: Holds employee data, including the department they belong to and their salary.</p>
  612. <p data-start="3125" data-end="3180"><strong>Step 2: Create the Table-Valued Function (TVF)</strong></p>
  613. <p>Next, we will create a TVF that returns the top 2 employees by salary for any given department.</p>
  614. <pre class="prettyprint  lang-mssql">-- Create or alter the function with the permanent Employees table
  615. CREATE OR ALTER FUNCTION dbo.fn_GetTopEmployeeSalary (@DeptID INT)
  616. RETURNS TABLE
  617. AS
  618. RETURN
  619.    (SELECT TOP 2
  620.             CONCAT(FirstName, ' ', LastName) AS EmployeeName,
  621.             YearlySalary
  622.     FROM dbo.Employees  
  623.     WHERE DepartmentID = @DeptID
  624.     ORDER BY YearlySalary DESC);
  625. GO
  626. </pre>
  627. <p>Explanation:</p>
  628. <ul>
  629. <li>The fn_GetTopEmployeeSalary TVF returns the top two highest-paid employees for a given department.</li>
  630. <li>It accepts a DepartmentID as an input parameter to filter employees based on the department.</li>
  631. <li>The employees are ordered by salary in descending order.</li>
  632. <li>The function returns a table with the employee's full name (concatenation of their first and last names) and their salary.</li>
  633. <li>Since SQL Server functions cannot access temporary tables, this function uses a permanent table (dbo.Employees), ensuring it can be accessed across sessions.</li>
  634. </ul>
  635. <p><strong>Step 3: Using CROSS APPLY with a TVF</strong></p>
  636. <p>Now, let’s use the CROSS APPLY operator with our table-valued function (TVF). This query will return the top 2 highest-paid employees for each department.</p>
  637. <pre class="prettyprint  lang-mssql">-- Using CROSS APPLY to get top 2 highest paid employees for each department
  638. SELECT D.Name AS DepartmentName,
  639.       E.EmployeeName,
  640.       E.YearlySalary
  641. FROM #Departments D
  642. CROSS APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
  643. </pre>
  644. <p>Explanation: Here’s what happens:</p>
  645. <ul>
  646. <li>CROSS APPLY works like an INNER JOIN. For each row in the Departments table (D), it applies the TVF dbo.fn_GetTopEmployeeSalary(D.DepartmentID) for that department.</li>
  647. <li>The query will return only those departments that have matching employees.</li>
  648. </ul>
  649. <p class="" data-start="4337" data-end="4374">Expected Result:</p>
  650. <p id="XQWjvPe"><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef764230899.png"><img loading="lazy" width="593" height="574" class="alignnone size-full wp-image-4573015 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef764230899.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef764230899.png 593w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef764230899-300x290.png 300w" sizes="(max-width: 593px) 100vw, 593px" /></a></p>
  651. <p>Key Point: CROSS APPLY ensures that only departments with employees are included. If a department has no employees (e.g., Marketing), it won’t show up in the result.</p>
  652. <p><strong>Step 4: Using OUTER APPLY with a TVF</strong></p>
  653. <p>Let’s now use OUTER APPLY instead of CROSS APPLY. This operator behaves like a LEFT JOIN, ensuring that all departments are included, even those without employees.</p>
  654. <pre class="prettyprint  lang-mssql">-- Using OUTER APPLY to get all departments, including those without employees
  655. SELECT D.Name AS DepartmentName,
  656.       E.EmployeeName,
  657.       E.YearlySalary
  658. FROM #Departments D
  659. OUTER APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
  660. </pre>
  661. <p>Explanation:</p>
  662. <ul>
  663. <li>OUTER APPLY returns all rows from the left table (Departments), even if there’s no matching data in the right table (the result of the TVF).</li>
  664. <li>If a department has no employees, NULL values will be returned for EmployeeName and YearlySalary.</li>
  665. </ul>
  666. <p class="" data-start="5571" data-end="5588">Expected Result:</p>
  667. <p id="kRUkoDf"><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76727ed95.png"><img loading="lazy" width="621" height="574" class="alignnone size-full wp-image-4573016 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76727ed95.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76727ed95.png 621w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76727ed95-300x277.png 300w" sizes="(max-width: 621px) 100vw, 621px" /></a></p>
  668. <p>Key Point: OUTER APPLY ensures that all departments are returned, even those without employees (like the Marketing department).</p>
  669. <p data-start="6318" data-end="6375"><strong>Step 5: Inserting a Department with No Employees</strong></p>
  670. <p>Let’s insert a new department, Finance, which has no employees, and observe how both CROSS APPLY and OUTER APPLY behave.</p>
  671. <pre class="prettyprint  lang-mssql">-- Insert an empty department (no employees)
  672. INSERT INTO #Departments (DepartmentID, Name)
  673. VALUES (5, 'Finance');
  674. </pre>
  675. <p>Let’s rerun both the CROSS APPLY and OUTER APPLY queries.</p>
  676. <p><strong>Using CROSS APPLY with the New Data:</strong></p>
  677. <pre class="prettyprint  lang-mssql">-- Using CROSS APPLY to get top 2 highest paid employees for each department
  678. SELECT D.Name AS DepartmentName,
  679.       E.EmployeeName,
  680.       E.YearlySalary
  681. FROM #Departments D
  682. CROSS APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
  683. </pre>
  684. <p>Explanation: The Finance department doesn’t appear because it has no employees, and CROSS APPLY works like an INNER JOIN.</p>
  685. <p>Expected Result:</p>
  686. <p><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef7694a1377.png"><img loading="lazy" width="630" height="575" class="alignnone size-full wp-image-4573017 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef7694a1377.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef7694a1377.png 630w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef7694a1377-300x274.png 300w" sizes="(max-width: 630px) 100vw, 630px" /></a></p>
  687. <p>Using OUTER APPLY with the New Data:</p>
  688. <pre class="prettyprint  lang-mssql">-- Using OUTER APPLY to get all departments, including those without employees
  689. SELECT D.Name AS DepartmentName,
  690.       E.EmployeeName,
  691.       E.YearlySalary
  692. FROM #Departments D
  693. OUTER APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
  694. </pre>
  695. <p>Explanation: The Finance department now appears in the results with NULL values for the employee fields, as OUTER APPLY ensures all departments are included, regardless of employee availability.</p>
  696. <p>Expected Result:</p>
  697. <p id="FoKiFbw"><a href="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76ac1fb20.png"><img loading="lazy" width="640" height="574" class="alignnone size-full wp-image-4573018 " src="data:image/gif;base64,R0lGODlhAQABAPAAAPLy8v///yH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76ac1fb20.png" data-lazy-load alt="" data-srcset="https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76ac1fb20.png 640w, https://www.sqlservercentral.com/wp-content/uploads/2025/04/img_67ef76ac1fb20-300x269.png 300w" sizes="(max-width: 640px) 100vw, 640px" /></a></p>
  698. <h2 data-start="8912" data-end="8930"><strong>Key Points</strong></h2>
  699. <ol>
  700. <li>CROSS APPLY returns matching rows only (like an INNER JOIN).</li>
  701. <li>OUTER APPLY returns all rows from the left table, even if no matches exist in the right-side table (like a LEFT JOIN).</li>
  702. <li>TVFs are often used with these operators to provide dynamic results based on a function’s return value.</li>
  703. </ol>
  704. <h2 data-start="8934" data-end="8998"><code data-start="8934" data-end="8947"></code><strong data-start="8301" data-end="8315">Conclusion</strong></h2>
  705. <p>The CROSS APPLY and OUTER APPLY operators are incredibly useful for working with subqueries and table-valued functions (TVFs) in SQL. Here's a quick recap:</p>
  706. <ul>
  707. <li>Use CROSS APPLY when you need to match rows from both the left table and the right table expression (similar to an INNER JOIN).</li>
  708. <li>Use OUTER APPLY when you need to include all rows from the left table, even if there’s no matching row in the right-side expression (similar to a LEFT JOIN).</li>
  709. </ul>
  710. <p>These operators are particularly useful in complex queries and enhance the performance and readability of your SQL code.</p>
  711. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/understanding-cross-apply-and-outer-apply-in-sql-server">Understanding CROSS APPLY and OUTER APPLY in SQL Server</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  712. ]]></content:encoded>
  716. </item>
  717. <item>
  718. <title>Load Data into Snowflake Using Python with Pandas</title>
  719. <link>https://www.sqlservercentral.com/articles/load-data-into-snowflake-using-python-with-pandas</link>
  721. <dc:creator><![CDATA[Additional Articles]]></dc:creator>
  722. <pubDate>Mon, 21 Apr 2025 00:00:37 +0000</pubDate>
  723. <category><![CDATA[Python]]></category>
  724. <category><![CDATA[Snowflake]]></category>
  725. <guid isPermaLink="false">https://www.sqlservercentral.com/?post_type=ssc_article&#038;p=4575512</guid>
  726.  
  727. <description><![CDATA[<p>Loading data into Snowflake is a common need. Using Python and pandas is a common go-to solution for data professionals</p>
  728. <p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/load-data-into-snowflake-using-python-with-pandas">Load Data into Snowflake Using Python with Pandas</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  729. ]]></description>
  730. <content:encoded><![CDATA[<p>The post <a rel="nofollow" href="https://www.sqlservercentral.com/articles/load-data-into-snowflake-using-python-with-pandas">Load Data into Snowflake Using Python with Pandas</a> appeared first on <a rel="nofollow" href="https://www.sqlservercentral.com">SQLServerCentral</a>.</p>
  731. ]]></content:encoded>
  735. </item>
  736. </channel>
  737. </rss>
  738.  
  739. <!--
  740. Performance optimized by W3 Total Cache. Learn more: https://www.boldgrid.com/w3-total-cache/
  741.  
  742. Page Caching using Redis (Page is feed)
  743.  
  744. Served from: sqlservercentral.com @ 2025-04-30 19:09:37 by W3 Total Cache
  745. -->

If you would like to create a banner that links to this page (i.e. this validation result), do the following:

  1. Download the "valid RSS" banner.

  2. Upload the image to your own server. (This step is important. Please do not link directly to the image on this server.)

  3. Add this HTML to your page (change the image src attribute if necessary):

If you would like to create a text link instead, here is the URL you can use:

http://www.feedvalidator.org/check.cgi?url=https%3A//www.sqlservercentral.com/feed

Home · About · News · Docs · Terms

 
Copyright © 2002-9 Sam Ruby, Mark Pilgrim, Joseph Walton, and Phil Ringnalda