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,779 other followers

Archive for the ‘SQL Server’ Category

sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow

Posted by jpluimers on 2019/11/28

[WayBack] sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow:

Medium slow:

SELECT * FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID

Very slow:

SELECT * FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity

It is based on the output of [WayBack] sql server – Query to list number of records in each table in a database – Stack Overflow, which originally ommited tables starting with dt (as those were be used for the “Database Diagram” in the SQL Server 7/2000 era using tables like dtProperties), but which I adopted using:

The query below does not support SQL Server 2000, where you would have to use things like objectproperty, but since by now even the [WayBack] documentation has been retired on the Microsoft site, you need to read [WayBack] Get list of tables but not include system tables (SQL Server 2K)? – Stack Overflow

In the end, it filters out tables like dbo.sysdiagrams that are generated by SQL Server Management Studio (SSMS), which are SSMS System Tables, but technically not SQL Server System Tables.

select
    s.name as SchemaName,
    t.name as TableName,
    i.name as indexName,
    p.rows,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from 
    sys.tables t
inner join
    sys.schemas s on t.schema_id = s.schema_id
inner join
    sys.indexes i on t.object_id = i.object_id
inner join
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
where
    t.is_ms_shipped = 0 -- not internal to SQL Server
    and
    (not exists (
        select ep.major_id
        from sys.extended_properties ep
        where 
            ep.major_id = t.object_id and
            ep.minor_id = 0 and
            ep.class = 1 and
            ep.name = N'microsoft_database_tools_support'
        )
    ) -- not internal to SQL Server Management Studio
    and
    i.index_id <= 1
group by
    s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
    s.name, t.name

The counts for the [WayBack] GitHub version of AdventureWorks is this:

Read the rest of this entry »

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

windows – How do I limit MS SQL Server memory usage? – Server Fault

Posted by jpluimers on 2019/11/26

Sometimes you need this so it easier to run other services on the same system: [WayBack] windows – How do I limit MS SQL Server memory usage? – Server Fault.

Thanks wfaulk.

From How to configure memory options using SQL Server Management Studio:

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Memory node.
  3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

You can also do it in T-SQL using the following commands (example):

exec sp_configure 'max server memory', 1024
reconfigure

Related:

–jeroen

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

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 »

 
%d bloggers like this: