How do you record locations in SQL? Most relational database systems support spatial and geographical data, generally using the round-earth system based on the SQL specification of the Open Geospatial Consortium (OGC). However, this is not the only approach, as Joe Celko explains. … Read more
Can you imagine it? You are in a group of smart database people, and they are debating the finer points about AWS DMS, and you don't even know what the letters stand for. You just feel too shy to ask those basic questions that seem ridiculous once you're up to speed. Laerte Junior answers all the questions you need answers for when facing the prospect of getting familiar with Amazon's useful Database Migration Servic… Read more
SQL Server works well, and Microsoft do everything they can to keep it relevant and competitive: As with everything in real life, they don't always get it completely right, and Rob Sheldon continues his quest through the jungle of past features to rediscover and explore the ones that time forgot. Here, he comes across Lightweight Pooling, XML Indexes, Stretch Databases, SQL Variants, Transaction Savepoints and In-Memory OLTP… Read more
SSMS has an interesting feature that turns easy the creation of policies to check our servers compliance. Right-clicking an item inside object explorer window, you will find ‘Facet’ item in the context menu. Clicking on it will open the ‘View Facets’ window. In the ‘View Facets’ window, you can see all the facets related to … Read more
By being able to run R from SQL Server, you have available to you not just a convenient way of performing analysis on data but also a wide range of more specialized graphical facilities. Rob Sheldon illustrates the point by demonstrating how you can create sparklines that can then be embedded in reports.… Read more
Query Store is becoming more and more important for server monitoring, specially in SQL Server 2017, which allows us to get historical information about wait stats. We can import query store information into power bi and create a very useful dashboard to monitor SQL Server. Query Store tables are system tables, due to that we … Read more
SQL Server produces some great features, but it would be impossible to get them spot-on target every time. We are now quietly advised to use caution about using some of them, such as AutoShrink or the Index Advisor. Others, like the database diagramming tool, almost seem to have been quietly abandoned. Robert Sheldon investigates.… Read more
A microservice architecture can provide great benefits to certain types of applications that have pronounced troughs and peaks in demand, such as web-based shopping apps. With containerization, there is the promise of deploying containerized services in a matter of seconds to deal with demand. Has the Docker ecosystem now reached the level of maturity to allow this type of application to become standard practice?… Read more
Although Azure Data Warehouse is part of the bright new jewellery of the Microsoft Data Platform, the old Data Warehouse rules still apply where data imports are concerned. When it comes to data import, it pays to choose the fastest import method first and prepare your data first to ensure that it is compatible with your choice. The subtlety is in the details, as Feodor explains.… Read more
There are a number of different ways that you can host SQL Server. RDS SQL Server, for example, uses SQL Server within AWS as a simple database service, much like a more versatile alternative to MySQL. Obviously, it is a compromise, in that you lose many of the extras beyond the database. Laerte Junior answers those questions about RDS that people seem to assume you know the answers to, but which you may be too shy to ask.… Read more
Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server, and wrote several articles on ways of overcoming these problems. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed the process up.… Read more
Several experts already wrote about CXPACKET before, there are plenty of articles about this subject on the internet, so, why one more? I think I can add my two cents to this subject with an example to emphasize an important sentence that several authors wrote, with small differences: “CXPACKET is not a problem by itself, … Read more
In the real world of business or scientific reporting and analysis, data can prove to be awkward. It can be plain wrong or it can be altogether missing. Sure, we have the NULL to signify unknown, but that doesn't play well with regular business reporting. There are a number of ways of dealing with missing information, and methods of estimating data from existing data has a long and respectable history. Joe Celko gets to grips with a data topic that is often treated with some trepidation. … Read more
The working life of the DBA can be punctuated by surprises, but they aren't generally nice surprises. This is especially true if the DBA is not checking and monitoring the databases for obvious things such as database corruption, and disk space. However, the really scary surprises are less obvious and provide fewer warning signs. Brent Ozar gives six scary surprises that can be avoided by the shrewd DBA.… Read more
Although you can get started with R in SQL Server without understanding data frames, they are a key structure of the R language that are the equivalent of SQL Server table variables. They give you many ways of manipulating and analyzing data and passing it between R and SQL Server. For a database professional, they provide a clear and familiar concept when getting to grips with integrating R into the database.… Read more
Some time ago, Phil Factor wrote his booklet 'SQL Code Smells', collecting together a whole range of SQL Coding practices that could be considered to indicate the need for a review of the code. It was published as 119 code smells, even though there were 120 of them at the time. Phil Factor has continued to collect them and the current state of the art is reflected in this article. There are now around 150 of these smells and SQL Code Guard is committed to cover as many as possible of them. … Read more
‘Quick Measures’ is a feature included in Power BI Desktop April update. It’s still in preview, so you need to enable this feature in order to use it. Inside Power BI Desktop, click ‘File’->‘Options and Settings’->‘Settings’->‘Preview Features’ and check the box beside ‘Quick Measures’, as illustrated in the image below. Microsoft created an online gallery … Read more
When your application is running slowly, the reflex action is to blame the database queries. It is certainly true that some of the more extravagant delays can be fairly blamed on a missing index or unnecessary locking, but there are other potential villains in the drama, including the network and the application itself. Dan Turner points out that you could save a lot of time and money by working out for sure where the problems lie before diving into the detail.… Read more
Every new release of SQL Server comes with new features that cause a ripple of excitement within the industry: well, amongst the marketing people anyway. What happens to all the exciting TLAs that are bandied about when a new version launches? It's mixed, it seems. Adam Machanic's classic post, The SQL Hall of Shame, has inspired Rob Sheldon to look back at some of the features that, though worthy, have may have failed to hit the mainstream.… Read more
Creating a feed for a data warehouse used to be a considerable task. Now, it just takes a few minutes to work through a series of screens that, in this example, create a pipeline that brings data from a remote FTP server, decompresses the data and imports the data in a structured format, ready for data analysis. The Copy Wizard for the Azure Data Factory is a great time-saver, as Feodor Georgiev explains.… Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
