Author: Database Journal

Feed: Databasejournal.com - Feature Database Articles. Author: . A long-standing discussion in Oracle forumns and newsgroups has been the efficiency of using coun(*) to returnn a rowcount from a given table. A new wrinkle in that discussion now introduces count(rowid) as a more efficient alternative; the argument states that count(*) expands the entire column list, much like "select * ...", and, as such, could be a resource sink when CLOB columns are present in the desired table. Let's look at that argument and see if it holds water. Let's start by creating and populating a table containing a CLOB column: ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . How many different database applications exist? Many people can name the most common ones such as MySQL, Microsoft SQL Server, and Oracle. Most good DBAs and programmers could likely rattle off a dozen different databases is you asked them to. This begs the question - how many can you name? Before looking at the list below or reading any further, I challenge you to consider a number. More so, I challenge you to then write your list to see if you can meet the number you selected! To be clear - SQL ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . In the processes of executing a query the database optimizer calculates an estimate at the cost to run the query. This cost is determined based on statistics. The statistics help the optimizer guess how much CPU and IO will be used when running the query. The database optimizer calls the calculated cost as the Estimated Cost of a query. This Estimated code is only an estimate of the execution cost, it is not the actual cost of the query. The database optimizer uses the Estimated Cost of a query to determine the ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . By Manoj Debnath Tables in a SQL database may contain enormous amount of data, but they are not always in a useful format to be readily used. The volume of data must be filtered based upon some specified criteria for efficient use. Due to security reason, we might want to make public only a certain amount of data while rest might be accessible to the privileged users. The SQL DML operations are extensible and used to filter through one or more tables using complex query expressions. Leveraging the idea, we can create ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . In one of the article, Considerations for Deploying Azure SQL Database Managed Instances, recently published on this site, basic characteristics of Azure SQL Database Managed Instance were described. Its biggest selling points include nearly full compatibility with on-premises SQL Server deployments, support for SQL Server Agent, and additional security associated with its Azure virtual network integration. However, Azure SQL Database Managed Instance does not provide the same level of flexibility and agility as other Platform-as-a-Service SQL Server-based offerings in Azure. To address these shortcomings, Microsoft introduced Azure SQL Database instance pools. Before ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . Oracle introduced a new feature, group by elimination, for queries where the group by column is also the table's unique key. As with many new features this one still has not had all the kinks resolved. The problem arises when key values are manipulated with function calls. The following example will illustrate the issue by using a table with a DATE as the primary key and by extracting the year is extracted using TO_CHAR or EXTRACT. A table is created as follows: create table bug_test_calendar( cal_name char(17), bus_dt date, updt_timestamp timestamp (6) ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . Do you get tired of having your CREATE PROCEDURE statement failing if the stored procedure already exists? If you are like me then you probably hate this as much as I do. I get tired of writing a drop then create statement like below: IF OBJECT_ID('dbo.YourObject','U') IS NOT NULLDROP PROCEDURE dbo.YourObject;CREATE PROCEDURE dbo.YourObject AS _ Or modifying my CREATE PROCEDURE statement to be an ALTER PROCEDURE after my initial creation.  Well now there is a better way. With the introduction of SQL Server 2016 SP1 Microsoft introduced the CREATE OR ALTER statement ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . Microsoft provides a number of different deployment options for its Azure SQL Database offerings. We have reviewed a number of them on this forum, including the recently introduced Azure SQL Database Serverless. While most of them share the same characteristics, one in particular warrants special attention from the standpoint of its infrastructure dependencies. That exception is Azure SQL Database Managed Instance, which will be the topic of this article. The majority of the caveats regarding Azure SQL Database Managed Instance results from its unique architecture. Its primary tenets, which differentiate it for ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . In SQL cursors serve as a pointer that enables application programming language to deal with query results one row at a time. This article quickly explores the concept behind and show how to declare cursors, open, retrieve data from them, and then close them. SQL Cursors The data in relational database are managed in the form of sets. As a result, query results return by SQL SELECT statements are referred to as result sets. The result sets are nothing but combinations of one or more rows and columns extracted from one or ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . .NET is great and provides a ton of tools for working with databases and data sources. There are times though that a data source may not be inherently supported. In a case such as MySQL you may not see the MySQL connection options when creating binding sources or data sources for your project. What do you do then? Well, all is not lost! It is just a little extra work. First, download a few tools which include the MySQL Connectors: After you have downloaded the installation files, install them one by one ... Read More