What happens when a SQL query becomes too long or hard to follow?> It gets confusing> Difficult to debug> Hard to maintain or extend Use Subqueries or Common Table Expressions (CTEs) to break down the logic and improve readability. What is a Subquery? A subquery is a query inside another query.Below is a query which shows customers whose remaining amount is above the average. What is a CTE (Common Table Expression)? A CTE is a temporary result set you can reference in a main query.It starts with the WITH keyword and improves readability, especially with multi-step logic. To Conclude: Subqueries Advantages Disadvantages Quick and easy for simple filtering. Harder to read when nested. Good for one-off checks. Redundancy if used multiple times (no reuse). CTEs (Common Table Expressions) Advantages Disadvantages Clean, readable SQL for complex queries. May be slightly slower in some databases. Can be recursive. Not supported in old SQL engines. Both subqueries and CTEs help you write better SQL but choosing the right one depends on your needs. We hope you found this blog useful, and if you would like to discuss anything, you can reach out to us at transform@cloudfonts.com

Introduction: This guide will walk you through creating and accessing Azure Blob Storage and integrating it with Azure Data Factory to automate data pipelines. From setting up a storage account and managing containers to configuring pipelines and transferring data to an Azure SQL Database, this step-by-step tutorial ensures you gain a comprehensive understanding of the process. Steps: 3. Click on + Create to initiate the creation of a new storage account. 4. Fill in the required fields like subscription, resource group, and region. Review all the settings before proceeding. 5. Create a Storage Account 6. Once the storage account is created, go to the resource by clicking on Go to Resource. 7. In the storage account, navigate to the Containers section and click + Container to create a new container for storing your files. 8. Click on the container you just created to access its contents. 9. Upload the desired JSON file into the container by clicking on Upload and selecting the file from your local system. 10. Ensure that the uploaded file is now listed in the container. 11. Go back to the Azure Portal and search for Azure Data Factory to open the ADF service. 12. From the ADF home screen, go to Author > Datasets. Click + New Dataset to create a new dataset for your Blob Storage. 13. Select the Azure Blob Storage dataset type, as you are working with data stored in Blob Storage. 14. Choose the data format that matches the file you uploaded, such as JSON, and click Continue. 15. Enter the necessary details for your dataset, including the file path and format settings. Select the appropriate Authentication type and specify the Storage account where the Blob Storage resides. Click Create to finalize the dataset creation. 16. Verify the settings and click OK to confirm the dataset configuration. 17. Navigate to the Pipelines section and click + New Pipeline to create a pipeline that will define your data flow. 18. Pipeline gets created successfully as shown below. 19. In the pipeline, select the dataset type as Azure SQL Database and click Continue to set up the SQL Database dataset. 20. Provide the necessary Linked Service details for your SQL database and click Create. 21. After configuring both the source and target datasets, and the pipeline, publish all the elements to save your work. 22. Once the pipeline is running successfully, you can verify its functionality by querying the destination database to ensure data is being transferred properly. a. Go to the SQL Database and select the relevant database. b. Select the database on which we have perform a query. c. Log in with your credentials. d. Write a simple test query to verify data has been transferred from Blob Storage to the SQL Database. Execute the query and confirm that the expected output is returned. Conclusion: Integrating Azure Blob Storage with Azure Data Factory is a powerful way to manage and automate data workflows in the cloud. This guide walks you through creating a storage account, configuring containers, uploading data, and designing a pipeline to process and transfer data to Azure SQL Database. By following these steps, you can efficiently handle large-scale data integration and ensure seamless communication between your data sources and destinations. Azure Data Factory not only simplifies the process of orchestrating data pipelines but also provides robust options for monitoring and optimizing workflows. Whether you are managing JSON files, processing transactional data, or setting up complex ETL processes, Azure’s ecosystem offers a reliable and scalable solution. Start exploring these tools today to unlock new possibilities in data-driven operations! We hope you found this blog useful, and if you would like to discuss anything, you can reach out to us at transform@cloudfonts.com.

SQL Server Authentication is used primarily used in Azure Services for accessing data, integration, etc. At start during Installation it is disabled by default. So, in this blog we will see how to enable SQL Server Authentication for your Local Server in SSMS. First open SQL Server Management Studio and Connect to Windows Authentication method which is default. In the Object Explorer, right-click the server and click Properties. On the Security page under Server authentication, select SQL Server and Windows Authentication mode and then click OK. In the Object Explorer, right click your Server and click Restart. Now, Expand the Security folder, right-click on the Logins folder and choose New Login. Enter the login name as “demo” or whatever you wish it to be, then select SQL Server authentication, and enter the Password and untick the Enforce password policy. Click the Server Roles page and enable the sysadmin role. Again Connect to the Data Base Engine, and Select Authentication method as SQL Server Authentication and enter Login details and Click Connect. Now the SQL Server is connected with SQL server authentication.

In this blog we will see how to import/restore SQL Database (.bak) file in SQL Server. If you have a new downloaded .bak file or the Backup of any other database in .bak file format then you can import/restore it in your SQL Server. Open SSMS and Connect to your SQL Server Expand the SQL Server, Right Click on the Database and choose Restore Database In the General settings, Select Device and then the three ellipsis. Click on Add to add the .bak file. Select the file location and then select the .bak file and click OK. Click OK and your Database will be restored.

In Microsoft SQL Studio Management Server (SSMS) there is an interesting feature that helps users quickly determine which connection tab they are using currently this can be done by setting custom connection colors in SSMS for different SQL Server nodes. In this blog we are going to see how we can use this feature Open SSMS, and on the Connection to Server window, click the Options button to show advanced options In the Connection Properties Tab select the database from the dropdown on which you intend to apply custom colors on After selecting the database, click on use custom color checkbox and select the desired color. You can select any colors usually I prefer Red color for production databases and green for test databases. Click on Connect button. Restart SSMS As you can see the color green has been applied which means that green color is associated with that database. Thank you for reading my blog, hope it helped !!

Sometimes while working on large SQL Queries we may face errors. SSMS tells us the line where the error has occurred after the faulty query gets executed but there is no line no. in the Query editor so it is tedious sometimes to fish out these errors. The solution to this issue would be adding line numbers in the query editor, by default this option is disabled. In this blog we will see how to enable line numbers in SSMS. From the toolbar click on tools, then select options. From the window that appears select the Text editor. Now from the text editor click on all languages and enable the line number option. Click on OK button. As you can see the corresponding line numbers are visible. Hope you found this article helpful! thanks for reading.

Sometimes while writing SQL Queries we would feel the need to refer to the internet for logic, syntax etc. But constant switching between our web browser and SSMS would interrupt our flow while writing queries. This problem can be solved by using SSMS’s inbuilt web browser. It can be accessed pressing CTRL+ALT+R As you click or press the keys a tab with browser window opens as: You can go to any website or search for anything you want to. To boost productivity, using browser along side with queries helps greatly this can be done by tab groups, Horizontal tab group will arrange both query and browser tabs one above the other as shown. And vertical tab group will arrange them side by side. Either can be used as per convenience. Thank you for reading my blog hope it helped.

Introduction: Sometimes a user wants to analyze their SQL query results in Microsoft Excel or import existing Excel data sheets as tables in SQL in order to do various data transformations. In both scenarios we can achieve this goal as: Converting Data from SQL table to Excel sheet: Go to tools option on the toolbar on SSMS and select options. In the window that opens select Query Results. Select Results to Grid from the drop down. Check Include column headers when copying results from the checkbox and click on the ok button. Click on the intersection of first row and first column of the SQL Table to select the entire table and then copy it by pressing ctrl + C or by right clicking and selecting copy . Open a new excel sheet and just paste the contents after selecting the first cell. If some columns show values as ##### , just increase the column width. Converting Data from Excel sheet to SQL Table: Right click on the database where you want to import the excel sheet as table and go to tasks and select import data. Select Microsoft Excel as source from the dialog box Select the version of excel you want to convert into If the system gives error that microsoft.ace.oledb drivers are not found download it from Microsoft website based on what  driver version is required. Choose destination as SQL server Native client and enter the server name and credentials Select the sheet which you want to be converted into SQL as table. Click next Wait for the process to complete The new sheet is created as a table in SQL Conclusion: Thus we saw how we can convert SQL table into Excel sheet and vice versa Thank you very much for reading, hope you enjoyed the article!