The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers

Archive for the ‘SQL Server’ Category

« Previous Entries
Next Entries »

ALTER DATABASE Compatibility Level (Transact-SQL) | Microsoft Docs

Posted by jpluimers on 2019/11/21

Since I keep forgetting which numeric value can correspond to what kind of server: [WayBack] ALTER DATABASE Compatibility Level (Transact-SQL) | Microsoft Docs

Product Database Engine Version Compatibility Level Designation Supported Compatibility Level Values
SQL Server 2019 15 150 150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x) 14 140 140, 130, 120, 110, 100
Azure SQL Database logical server 12 130 150, 140, 130, 120, 110, 100
Azure SQL Database Managed Instance 12 130 150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x) 13 130 130, 120, 110, 100
SQL Server 2014 (12.x) 12 120 120, 110, 100
SQL Server 2012 (11.x) 11 110 110, 100, 90
SQL Server 2008 R2 10.5 100 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 (9.x) 9 90 90, 80
SQL Server 2000 8 80 80

–jeroen

Posted in Database Development, Development, SQL Server | Leave a Comment »

SQL Server Downloads | Microsoft

Posted by jpluimers on 2019/11/14

[WayBack] SQL Server Downloads | Microsoft

Get started with Microsoft SQL Server downloads. Choose a SQL Server trial, edition, tool, or connector that best meets your data and workload needs.

Downloading offline installers is the same for both: run the .EXE, then choose to download off-line media.

This is much easier than subscribing to Visual Studio Dev Essentials below.

Then depending on the edition, make the choice:

Read the rest of this entry »

Posted in Database Development, Development, Software Development, SQL Server | Leave a Comment »

Public database servers

Posted by jpluimers on 2019/10/30

I could not find any vendors/architectures have public database servers.

So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2017

I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.

If you run locally, there are plenty of example/demo database, like:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »

ApexSQL, a free tool (SSMS add-in) for analyzing the execution plan of a SQL server query…

Posted by jpluimers on 2019/10/01

On my research list: ApexSQL PLAN analysis tool released in 2017. It requires SSMS which you can get at [WayBack] Download SQL Server Management Studio (SSMS) | Microsoft Docs.

More info:

Via:

–jeroen

Posted in Database Development, Development, Software Development, SQL, SQL Server | Leave a Comment »

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Posted by jpluimers on 2019/08/29

Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:

Application for testing and sharing SQL queries.

Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

It is a cool site, currently supporting these SQL back-ends:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)

Other resources for learning and playing around with SQL:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »

SQL Server 8060 row size query limit

Posted by jpluimers on 2019/06/19

Every now and then you bump into a limit you did not know it existed before:

Cannot create a row of size 8209 which is greater than the allowable maximum row size of 8060.

This is while querying a few tables having a combined column size of more than 8060. Which means it is not just about single tables being too wide.

Related:

–jeroen

Posted in Database Development, Development, SQL, SQL Server | Leave a Comment »

Generating a million sequential numbers on the fly in a SQL Server query

Posted by jpluimers on 2019/01/16

A while back I wrote on Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements.

SQL Server has different features and performance characteristics so here are some links on doing similar things in SQL Server:

As always: if performance is important, measure before starting to optimise!

Via: [WayBack] sql – All hour of day – Stack Overflow

–jeroen

Posted in Database Development, Development, Firebird, SQL, SQL Server | Leave a Comment »

Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements

Posted by jpluimers on 2018/07/19

The testing was done with Firebird 2.5.x x86 on Windows 7 x64.

Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBack] PkLab – Firebird: Tree data mangement with recursive CTE).

However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:

with 
  recursive 
  sequence(n) as (        
    -- When you select more than 1024 values, this error occurs:
    -- Error while fetching data:  Too many concurrent executions of the same request    
    select 0 -- start
    from rdb$database
    union all
    select sequence.n + 1
    from sequence
    where sequence.n < 1023 -- finish
  )
select sequence.n 
from sequence
--where sequence.n in (24, 38) 
order by sequence.n

It however is a pretty quick and CU bound solution: on my system ([WayBack] AMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.

In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?

Read the rest of this entry »

Posted in Database Development, Development, Firebird, IKEA hacks, OracleDB, SQL, SQL Server | Leave a Comment »

“The login already has an account under a different user name.” – MSSQL

Posted by jpluimers on 2018/07/11

You get “The login already has an account under a different user name.” when you try to create a user that already exists.

In this case it was a statement somewhere in a database creation script having a statement like this:

exec sp_addlogin N'<<UserName>>', N'<<Password>>', @logonDatabase, @logonLanguage

What happened was that the <<UserName>> in the script already existed and was used to create the new user using sp_addlogin.

I know that this is deprecated, but the search above also shows it happen with other ways of adding users.

It had been a while since doing SQL Server, so this was a good time for me to find back the relation between [WayBack] SQL Server Logins and Users

These queries will help you identify which ones are currently in your database:

use CRM124TestJWP
go

select sl.name as LoginName
     , su.name as UserName
     , sl.dbname as DatabaseName
from sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid

select sp.name as LoginName
     , sp.type_desc as LoginType
     , dp.name as UserName
     , dp.type_desc as UserType
     , sp.default_database_name as DefaultDatabase
     , dp.default_schema_name as DefaultSchema
from sys.server_principals sp
join sys.database_principals dp on dp.sid = sp.sid

Via: [WayBack] sql server – The login already has an account under a different user name – Database Administrators Stack Exchange

Read the rest of this entry »

Posted in Database Development, Development, SQL Server | Leave a Comment »

MSSQL: finding column names

Posted by jpluimers on 2018/07/10

This small query gives you the tables, views and columns having characters likely not translating directly to ORM identifiers because they contain other characters than a-zA-Z0-9:

select *
from INFORMATION_SCHEMA.COLUMNS c
where 1=0
or c.TABLE_NAME LIKE '%[^a-zA-Z0-9_]%'
or c.COLUMN_NAME LIKE '%[^a-zA-Z0-9_]%'

The view [WayBack] COLUMNS (Transact-SQL) | Microsoft Docs in the … has been around since at least SQL Server 2000, so this is a pretty safe method for finding those columns.

As a bonus, I learned that SQL Server supports a subset of regular expression matches in like also since at least SQL Server 2000: LIKE.

Via [WayBack] SQL Server 2008 query to find rows containing non-alphanumeric characters in a column – Stack Overflow.

Related:

–jeroen

Posted in Database Development, Development, SQL Server | Leave a Comment »

« Previous Entries
Next Entries »