How to migrate wide Excel worksheet into SQL Server table

Question: What is the limit on SQL Server Maximum Columns per table? I have a very wide Excel worksheet and want to migrate the data to a SQL Server table - where we can then manage the data? Answer: The standard SQL Server table maximum columns limit is 1024. Tables that include sparse column sets include up to 30,000 column A Sparse column set combines the table's sparse columns and translates into a structured representation. The column set is not stored physically in a table. Read more on SQL Server tables Table sizes and percentages (SQL Server DBA) SQL Server... Read more →

Database compatibility changing to 100 when restoring SQL 2005 to SQL 2017

Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and there is some database compatibility behaviour I was not expecting. When the database is attached to SQL Server 2005 - the database compatibility is 90. But when restored to SQL Server 2017 - the database compatibility is set at 100. Why is this happening? Answer: This is expected behaviour. i.e when changing from a lower version to a higher version - if the database compatibility of the source db is lower than the lowest compatibility of the target SQL Server version... Read more →

Separating the Application and Database server

“I have a new product we need to install. The vendor documentation supports both co-located application and database server , and also remote access to the database server” This sort of comment is standard amongst product documentation. As a DBA I need to review and ensure the product implementation is in line with the organisation standards and the standardisation standards required in managing large amounts of servers. Consolidate.Data Center Migration.Cloud Computing.VM Disaster Recovery , these are some of the background themes when establishing an approach to managing a database server environment. We’re in the age of Server consolidation and services... Read more →

Data Migration Assistant for SQL Server 2016 Upgrade

The Data Migration Assistant (DMA) has replaced the SQL Server Upgrade Wizard. The big difference is the Data Maigration Assistant also offers an option to Migrate the databases. It's a key step in documenting how to update SQL Server. The details from the DMA give database owners detailed information about any changes required The purpose of this post is to focus on Assessment only not on the Migration Aspect. The Assessment type of usage is very similar to the Upgrade Wizard. There is also a command line option for the Data Migration Assistant , but I'll discuss the command line... Read more →

SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN. I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ... Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve. You... Read more →