The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 1,676 other followers

Archive for the ‘SQL Server’ Category

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, 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 »

 
%d bloggers like this: