How to get next value of Sequence dynamically in Oracle I used to a SQL guy and trying my hands on Oracle front.

I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to handle this in Oracle.

I wrote something same for SQL like this:

SET @v_SQLString = 'SELECT @v_new_seq = next value for '+@v_table_name
SET @v_ParmDefinition = '@v_new_seq NUMERIC(19) OUTPUT'

EXECUTE sp_executesql
    @v_SQLString,
    @v_ParmDefinition,
    @v_new_seq = @v_new_seq OUTPUT

Looking for something same in Oracle.

My_Sequence_Name.NextVal - check here and here. Psoug is a great site for Oracle.


MySql/MariaDB cannot change default datadir on Debian 9.1 server I have a problem to move the default datadir of Maria DB to another partition, it appears to be very common but i tried everything I can without luck.

Mysql is installed as Mariadb 10.1.26 with the default debian package (apt-get install mysql-server) on a Debian 9.1 (stretch) server, mysqld -v returns mysqld 10.1.26-MariaDB-0+deb9u1

Default_mysql_datadir : /var/lib/mysql

New_mysql_datadir : /home/mysql

/var/lib/mysql is mounted to "/" (/dev/md3)

/home/mysql is mounted to "/home" (/dev/md4)

What I've tried

# systemctl stop mysql
# mv /var/lib/mysql /home

Change datadir in /etc/mysql/my.cnf

datadir = /home/mysql

Check if the rights/permissions are ok

# chown -R mysql.mysql /home/mysql

apparmor is NOT installed nor running on the system though the /etc/apparmor.d/usr.sbin.mysqld file is existing with the following rules :

/home/mysql/ r,
/home/mysql/** rwk,

I even tried to create and empty /var/lib/mysq folder refering to this bug

But when I start I always get the same error :

# systemctl start mysql
[Warning] Can't create test file /home/mysql/<user>.lower-test
#007/usr/sbin/mysqld: Can't change dir to '/home/mysql/' (Errcode: 13 "Permission denied") 2017-09-07  0:16:59 140119808397888 [ERROR] Aborting
mariadb.service: Main process exited, code=exited, status=1/FAILURE
Failed to start MariaDB database server.
mariadb.service: Unit entered failed state.
mariadb.service: Failed with result 'exit-code'.

Any suggestion ?

Thanks

You must set ProtectHome=false in the systemd config file in order to move your datadir to the /home directory.

According to Debian policy rules, you should set this option value in a custom file like /etc/systemd/system/mariadb.service.d/YOUR_CUSTOM_FILE.conf containing:

[Service]

# Prevent accessing /home, /root and /run/user
ProtectHome=false

Then reload systemctl daemon:

systemctl daemon-reload

After that you should be able to change MariaDB datadir from /var/lib/mysql to /home/mysql.


SSIS Packages get stuck in SQL Agent jobs We run about a dozen scheduled SQL Agent jobs which run SSIS packages.

Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's a bunch of jobs still running. Sometimes we don't actually notice it for days. Further investigation seems to indicate that the package never starts:

DTEXEC process has 0 or very little CPU time
No logging or data activity occurs in the database

There is no loss of functionality in the actual database server, we don't notice anything (except stale data) until we check SQL Agent status, or start a job and it doesn't finish

We can stop and restart the jobs and they simply get stuck again
SQL Commands and CMDEXEC steps still work fine.

One job's first step copies a file with XCOPY then runs a package. The job completes the file copy then gets stuck on the package

There are a few customisations

Most packages are run from a master package, but the issue occurs for standalone packages
We have a custom built tool which queries and starts jobs via T-SQL but this is not in use
We use a framework called BIPA for our logging

Looking in the SQL Server logs, there seem to be a lot of AD type windows authentication errors. However these happen all the time regardless of the SQL Agent issue

We don't use the SSIS service. Packages are run from the file system using the SSIS step.

I can't think of anything further to troubleshoot at this stage. Does anyone have any ideas?

EDIT:

If you're interested I narrowed this down to our SSIS framework which is called 'BIPA'. I deleted the BIPA logging addin and we no longer have this problem. Based on the .net wait's happening in SSIS I believe it just couldn't instantiate the provider somehow. Why it only does this at night I don't know.

Strange thing is it does run fine when run interactively from DTEXEC (i.e. in a batch file) but won't run in SQL Agent (as a different user of course)

I think you should debug SSIS package in visual studio, if you have solution file.

I got this situation couple of month before, when I execute the package they stuck on the data flow.
I had resolved my problem by removing the transaction script from the package. Because I importing bulk of data and transaction create kind of deadlock situation....

Hope this will work for you


No priviligies to create table in custom database I'm using locally installed express edition of SQL server. CURRENT_USER = dbo, and I used win authentication. The database was successfully created this way:

CREATE DATABASE [online_banking]
 ON  PRIMARY
( NAME = N'online_banking', FILENAME = N'C:\...\online_banking.mdf' ,
  SIZE = 512000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30%)
 LOG ON
( NAME = N'online_banking_log', FILENAME = N'C:\...\online_banking_log.ldf' ,
  SIZE = 1024KB , MAXSIZE = 20GB , FILEGROWTH = 10%)
GO

However creating table cause the error, that I don't have permission.

CREATE TABLE [online_banking].Customers
     (
      customer_id INTEGER NOT NULL,
      customer_name VARCHAR(100) NOT NULL,
      CONSTRAINT PK_cust_id
               PRIMARY KEY CLUSTERED (customer_id)
               WITH (IGNORE_DUP_KEY = OFF)
      )
;

What is wrong here?

P.S. Table can be created this way:  CREATE TABLE Customers ...

It looks like you are trying to create a table in a schema called online_banking. In some platforms a schema and a database are synonymous, but in SQL Server, a schema is a container inside a database.

  Instance > Database > Schema > Object

In order to create a table in the online_banking database, you need to use a three-part name or be in the context of that database. And you should always use the schema prefix.

USE online_banking;
GO
CREATE TABLE dbo.test1(i INT);
GO

Or:

CREATE TABLE online_banking.dbo.test1(i INT);


sp_estimate_data_compression_savings for column store compressions Is there a way to get compressions saving for column store and column store column store archival compression?

In order to check these compressions saving I have to drop all clustered and non clustered indexes and then to create a clustered column stored index - most of the time this a time consuming operation and I am wondering is there an other way to do this.

There is no equivalent proc so what I have been doing recently is creating a copy of the table in tempdb using SELECT...INTO, eg

SELECT TOP 10 PERCENT *
INTO tempdb.dbo.yourTable
FROM dbo.yourTable

then apply the clustered columnstore.  If your table is too large to fit in your tempdb (as I might expect for a datawarehouse table), transfer a percentage and infer from that.  You could also create the empty table in tempdb, add the columnstore then populate, although this will probably take longer - compression has quite an effect on INSERT time.  The SELECT...INTO technique has the added benefit of removing the indexes for you, can be minimally logged and can even run in parallel on SQL 2014.

Example:
I have a 1TB table, I copy 100GB ( 10% ) into a table in tempdb.  I apply the clustered columnstore index ( normal compression ) and check the table size.  It has packed down to 10GB, or 10x compression.  I rebuild the clustered columnstore with COLUMNSTORE_ARCHIVE mode, the table packs down to 5GB or 20x compression.  I can infer that my 1TB table will pack down to approximately 100GB or 50GB with archival mode.  Allow some tolerance as it's an estimate.

The documentation suggests up to 7x compression but I have been seeing larger factors in my testing, particularly with the COLUMNSTORE_ARCHIVE mode, clearly with some CPU overhead when reading back.  YMMV.


Difference between Relational database and time series database? I have a bit stupid question maybe. I recently came across a term "time-series database". I would like to ask what is time series database and is it same as relational database?

Thanks,

Time series describes the type of data you will be storing.  The use case that springs to mind is experimental or research data being collected at different time points.  You may have seen the term "Data Warehouse". That too is another type of data.  As it happens, you can store these data "types" in a relational database.  For time series data this may not be optimal.  There are a number of vendors in the space that cater directly to time series data.  See the wiki page referred to above.  Good luck.


Get notified when SQL Server 2008 Audit Server logs something? I'm setting up SQL Server 2008 Audit Server to log any activities made by certain group of users of certain database. All I need to do is get notified when the Audit server logs something, so that I can send out emails to the admins notifying them of the logged operation.

Is there a way -an event that get raised or something- to tell SQL Server to run a certain job once an audit entry has been recorded?

One consideration would be to query the sys.dm_audit_actions view at regular intervals to see what new information has been logged to the audit log.  For example, create a stored procedure to mail the results of a query from this view to a set of people on a daily basis.  The only drawback would be that it wouldn't be delivered as it happens (asynchronously) unless you wanted to script it in .Net.


Connecting to MySQL database from Analysis Services I need to know whether it is possible or not to get data from a MySQL database and create a Cube in Analysis Services 2008. If so, tell me how to connect to MySQL Database from Analysis Services.

You can do this using MyODBC. Install the connector, then just set up a DSN and use it as normal.


Oracle 11g database structural change doesn't autobackup controlfile/spfile I have configured controlfile autobackup:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/%F';

Then added a test tablesapce into the database:

create tablespace test datafile '+DATA01' size 10m;

And there is no controlfile autobackup, and also no entry in alert log file about backing it up.

Why?

I found the answer as I think:

"Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database."

After about(not sure how much) 10-15 minutes I run the following command from the RMAN:

list backup;

and now it shows me the result. So it waited for a time and then backed up the controlfile.


MobiLink: Where is Sybase Central storing the actual content of an remote task for mlagent When creating a remote task inside an MobiLink project with Sybase Central where it is storing the actual content of task (e.g. executing SQL -> Query which shall be executed)

It's storing them inside project folder of MobiLink project as seperate files with mlt-extension. Format is XML.


Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers? I would like to grant below to permissions to Developers group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)

This is done to make them able to query some of dynamic management views and functions, view performance data, as well as see code (definitions) of all stored procedures and functions
Are there any drawbacks or any reasons why this is not good in certain scenarios ?
Any possible unwanted side effects of granting above permissions ?
Second question - if I grant VIEW DEFINITION in master database, that makes it server-level and it doesn't have to be granted in any user database ? Same with SHOWPLAN ?

VIEW DEFINITION is a database permission.  There is a corresponding server permission VIEW ANY DEFINITION you can add.  If you want grant some logins access to view server state and any all object metadata, you can do it like this:
create server role developers
grant view any definition to developers
grant connect any database to developers
grant view server state to developers

You can grant SHOWPLAN for each database, or grant ALTER TRACE at the server level.  ALTER TRACE also allows the user to create SQL Traces which you might not want to allow developers to do on a production server.  This is all documented under Permissions.


What is using all my space? I'm using SQL Server Express 2008 R2 and a third part application has its database on this database server.  The database seems way bigger than I would expect considering what it is actually doing.  When I do a rough add up of the space used by the tables and indexes I get to about half of the total size of the database file.

I am wondering what is using up the rest of the space.  I understand SQL Server Express is limited to 10GB which is why I'm concerned as the database is about 2/3 full. I also understand that there is free space within the database itself which is no problem.  I'm not short of disk space.

Here is how I have found the size of the tables and indexes.  I don't understand these queries very well as I googled them but they seem to report the numbers I'm interested in:

-- Size of database and how much space is used
SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id;

Result:

DbSize   SpaceUsed  LogSize
-------  ---------  -------
7829.25    6130.09   611.13

OK so what is using that 6.1 GB?

The following query lists the sizes of all the tables and indexes (source):

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName,
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
    cast(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If I add up all the tables and indexes with a size over 1mb, it comes to under 3GB in total.  (I tried sum in the above query but couldn't figure that out)

If I post the full output here it is very long and messy.

So it seems about half of the expected free space is being used up by something else.

Any ideas what I'm missing here or, do I need a better way to add up used space?

The Comment by  "Henrik Staun Poulsen" above pointed me in the right direction.
Half the total space used by my database is a single XML index.

I found a great article on XML Indexes here

https://www.red-gate.com/simple-talk/sql/database-administration/getting-started-with-xml-indexes/

I tried to rebuild the index with

Alter Index <IndexName> on <TableName> Rebuild

It ran for 5 minutes but that didn't reduce the size so I'm probably stuck with it.

I'll post the full query Henrik pointed me to here incase that link disappears in the future. (I hate that)

    ;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       CASE
         WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
         ELSE N'<Heap>'
       END AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
ORDER BY UsedGB Desc

11Jun19 Posted a question to Stack Exchange.  Didn’t use the exact queries below but something close.

16May19 This script returns information but it doesn’t add up to anything like the size of the db.

USE [MyArchive] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
--ORDER BY s.Name, t.Name
Order by Total_MB
GO


Fulltext searching on shared hosting - 2000 Entires with around 500 words each I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be sorted by relevancy.

I'm trying to avoid going for google custom search, amazon aws search, google app search or any hosted service like sphinx, solr etc. The only reason I'm trying to avoid this is it means my costs and complexity is kept right down.

With this fairly low amount of entires is it possible to still keep high performance using MySQL Fulltext search? It seems possible to still get reasonable performance, especially when the amount is documents is small. Normally comparisons are done against millions of entries, not thousands.

I guess my question is, it is possible to get reasonable full text searching using MySQL on simple shared hosting on that amount of documents or am I still asking too much without bringing in proper search engine software.

I often query a 170K-row table using InnoDB FULLTEXT.  (Avg: 300 words.)  I typically get the answer of a few dozen rows in under 200ms.  That 200ms drops to below 20ms if the cache is warm.

I don't think the number of documents in the table matters as much as the number of documents returned.  If you need to fetch the text for 100 documents (after searching the index), that could take a full second to randomly fetch from a cheap disk.

Beware of benchmarks; they may be delivering only document ids, not the text; and they may be assuming everything is cached in RAM.  Since you have only a few MB, the caching is a valid assumption for you.


Compare sum of columns between tables I'm trying to create a query for this problem.

Suppose I have these two tables:

Acceptor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          1
2   O-           5
3   O+           1
4   O-           5
5   O-           5
6   AB-          1
7   AB+          1

Donor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          2
2   O-           5
3   O+           3
4   O-           5
5   O-           3
6   AB-          1
7   AB+          3

I need a query to select the types of blood, and the difference between the quantities in cases where the SUM of Acceptor.QTY is greater than the SUM of Donor.QTY.

So in this case, Donor's O- => 5 + 3 + 5 = 13 and the Acceptor's O- => 5 + 5 + 5 = 15.

The query would have to return

O-   2

You can use a group by on each table to get the result you're looking for, then wrap the whole thing in a view to filter it. See this fiddle: https://www.db-fiddle.com/f/d313vqnsTkiXDXfiEPTwLt/4

Select shortages.Type, shortages.Shortage from (
    Select a.Type, a.QTY-d.QTY Shortage
    from
        (Select Type, sum(QTY) QTY from Acceptors group by Type) a
         inner join
        (Select Type, sum(QTY) QTY from Donors group by Type) d on a.type = d.type

) shortages
where shortages.shortage > 0

If you need to cover the situation where a value doesn't exist in the donors table at all you should replace the inner join with a left join and use a case statement or an ISNULL to replace the null values with zeros.


Post data deploy in SSDT I need to create post deployment script which will upload data from .csv file to my database. But I can't understand how can I do this.

When I tried to deploy my project on my server I receive this error messaege:

  (2354,1): SQL72014: .Net SqlClient Data Provider: Msg 4861, Level 16, State 1, Line 9 Cannot bulk load because the file ".\Data\DataFile.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).
  (2346,0): SQL72045: Script execution error.  The executed script:

Could someone help me please?

P.S. Below you could find my examples of files.

Structure of my SSDT project:

Source
    Data
        DataFile.csv
    Script
        PostDeployment
            DataFile.sql
    PostDeployment.sql

DataFile.sql:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t
    (
        Column NVARCHAR(100),
        Column2 NVARCHAR(100)
    )
;
BULK INSERT #t
FROM '$(DataFile)'
WITH (CODEPAGE = '1251', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', DATAFILETYPE = 'widechar')

SELECT * INTO table1 FROM #t AS tcbm

PostDeployment.sql

:setvar DataFile .\Data\DataFile.csv
:r .\Script\PostDeployment\DataFile.sql

If I change :setvar DataFile to ..\..\Data\CarBrandModel - deployment also will be failed with the same error.

CSV files are not included in the dacpac that you deploy so you will need to make sure that you copy it with the dacpac and that if you use relative paths the working directory is what you think it is.

What I do for extra files is set the "Build Action" of the file to "Copy if Newer", when the dacpac is built in the bin folder you will also have a "Data" subfolder - just make sure you copy that with your dacpac always.

ed


Migration to SQL Database using Transaction Replication - down time? I am looking to migrate two on-prem SQL Servers into Azure Hyperscale. Both 28 cores, 1.5 TB of RAM with 10TB data disks.

I can NOT afford any downtime during the migration and have heard transaction replication method might do the trick.

Has anyone had a scenario like this where they used this process?

There really isn't a quiet time. It's a cell phone sub carrier with roughly 2,000,000 users. There is roughly 30GB of data updated twice per minute. Currently on SQL Server 2014 Enterprise Edition.

If you configure replication, replicate to the external site, keep the replication going until a quiet time then stop it and reconfigure the destination as not replicated. There are some limits replicating to Azure, see https://docs.microsoft.com/en-us/azure/sql-database/replication-to-sql-database to see if they apply to you. There are also some differences between on-prem SQL Server and Azure SQL, they are minimal-to-nothing for most simple databases these days but do check the documentation (and test the application thoroughly before switching over) to make sure they don't affect you.

There will be a small amount of downtime as you stop replication and reconfigure your application to point to the new database, but that should be minimal.

Obviously you'll want to test this out: point a second instance of your application at the target DB and test thoroughly. Once you are convinced all is well wipe it down (as it will now be out of date) and build a new replica (or have the new replica building while you perform the testing). You may need a couple of iterations of this do->test->fix->do loop if there are problems with the replication or your app reconfiguration, or you need to make changes to account for differences in Azure SQL.


Primary key within JSON data in Postgresql I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key:

> SELECT json->'id' AS id FROM t LIMIT 1;
             id
-----------------------------
 " 63631ff3809de7a17398602f"

I can create a UNIQUE INDEX on id, thus:

> CREATE UNIQUE INDEX t_id ON t((json->>'id'));

CREATE INDEX

I'd like to add this as a table_constraint_using_index, but it fails for both PRIMARY KEY:

> ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

and UNIQUE:

> ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Should I be able to add such a constraint?

As explained in Craig's answer neither PRIMARY KEY nor UNIQUE can be used.

However,here is a two part solution to work around this:

To enforce not null, use a check constraint, thus:

ALTER TABLE t ADD CONSTRAINT id_not_null CHECK ((json->>'id') IS NOT NULL);

To enforce uniqueness, create a unique index*, thus:

CREATE UNIQUE INDEX t_id ON t ((json->>'id'))

After this, a \d t on in psql will give you:

Indexes:
    "t_id" UNIQUE, btree ((json ->> 'id'::text))
Check constraints:
    "id_not_null" CHECK ((json ->> 'id'::text) IS NOT NULL)

And you can see both not null and uniqueness are enforced:

test=# INSERT INTO t VALUES ('{"no id" : "field here"}');
ERROR:  new row for relation "t" violates check constraint "id_not_null"
DETAIL:  Failing row contains ({"no id" : "field here"}).

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
INSERT 0 1

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
ERROR:  duplicate key value violates unique constraint "t_id"
DETAIL:  Key ((json ->> 'id'::text))=(abc) already exists

* note that the mere existence of the t_id index is sufficient to enforce uniqueness, even though it isn't explicitly given as a constraint.


Assign result of regexp_split_to_array() to Pl/pgSQL array variable How do I create an array and then put a result of a function into it?
I'm doing it like this:

array text[];
select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+')
    into array;

But obviously it doesn't work, when I try to

raise notice '%', array[1];

It's just not how it's done.

array is a reserved word. You cannot use it as variable name in plpgsql.
This works:

DO
$do$
DECLARE
   _arr text[];
BEGIN
SELECT regexp_split_to_array('thiiis splits into array with spaces', '\s+')
INTO _arr;

RAISE NOTICE '%', _arr[1];
END
$do$;

SQL Fiddle

Also simplified  E'\\s+' -> '\s+'.
standard_conforming_strings = ON is the default since Postgres 9.1 and should be the universal setting by now.


Bulk loading to partitioned table I am designing a database application which will be using PostgreSQL 9.3.  My question is regarding bulk loading a CSV to a partitioned table.  I have done a lot of research, but I'm still unsure of the best way to proceed.

In our application, we download data in CSV format every day from web-enabled devices and upload these to a single database table.  Each CSV contains a record for every minute in the day and we have a few hundred devices, so we end up loading about a million records each day.  This will increase though, as we are adding more devices every day.

We have found that we have performance issues as this table gets too large, so I would like to redesign it as a partitioned master table which uses range partitioning on the date field, so that each child table contains the records for a single month.  This seems like the best balance for us between table size and number of partitions.

My understanding is that the most efficient way for us to load data would be to insert the records to a staging table, then alter the table so it is a child of our master table.  In our case, though, this won't be possible because we need to update the table every day with new data; we cannot wait until we have a full partition after each month, then make that month's table a child of the master table.

So far, I've come up with three possible solutions:

Use bulk loading to send the CSV to the master table.  Triggering would be used to send the data to the correct child partition, as described here. I'm very concerned about the performance of this, as my understanding is that the triggers would be applied to every record that gets inserted.
According to that link, using rules instead of triggers would give better performance, as they would apply to the entire query instead of every record. Unfortunately, it also says rules are not supported with bulk inserts that use the COPY command.
Use bulk loading to send the CSV to the correct child table.  Since we generally upload one CSV at a time which contains one day's data for one device, we can add some logic to our ETL code that determines the correct child table for that day and sends it there. This is not ideal because I would like the partitioned table design to abstract this logic away and take care of it in the background.
Create another table which contains only the current month's data.  Update this table each day, and when the month is over, alter this table so it becomes a child of the master table with all the other past months' data.  Then create a new current-month table and start updating it instead.

I like this approach because it minimizes updates to the master table.  We do have a use case where we have to re-load data from previous months, so we will probably still have to use one of the previous methods for these cases.
The disadvantage with this approach is that our master table will not contain the data for the current month, as that will be located in a different table.

I'd appreciate anyone's input on this problem and the best solution, as I'm still somewhat new at this.  Please let me know if I've made any incorrect assumptions or I'm just flat-out wrong about something.

Option 2 would be the fastest. You are loading it directly, append-only. Option 3 would be similar except that you are updating data.

Option #4: Create a staging table (UNLOGGED) and COPY the CSV into that. After loading, use straight SQL or PLPGSQL to INSERT-INTO-CHILD--SELECT--FROM-. That allows fast bulk loading, with optional transformation steps after the load. If you have few indexes on the staging table (which you ideally add after the bulk load), you can have the whole ETL process work quite rapidly. Using plpgsql to load your child partition from the staging table allows gives you error-handling capabilities.


Why does SQL Server require a private key to encrypt a backup? I am setting up an experiment where I only have the public key portion of a certificate on the server. The certificate was generated on another server and I did not restore the private key.

When I try to backup a database with encryption using that certificate I get this error:

Msg 15556, Level 16, State 1, Line 15
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
Msg 3013, Level 16, State 1, Line 15
BACKUP DATABASE is terminating abnormally.

That's SQL Server clearly telling me it needs the private key for encryption.

But why is that?

My understanding is that BACKUP generates a symmetric key on the fly,  protects the symmetric key using the public key in the certificate and stores it alongside the backup contents. The symmetric key is then used to encrypt the actual backup contents, since symmetric encryption is usually (always?) faster than asymmetric encryption.

In order to restore the backup I would then need the private key that corresponds to the public key used in encryption. That's expected.

I just don't get why the private key is needed during the backup operation. Didn't find an answer on docs or anywhere else. Can someone explain that or point me to some documentation on why that is the case?

I just don't get why the private key is needed during the backup operation.

Short Answer

That's how it's currently implemented.

Longer Answer

I didn't look at all the dependencies to see if it would be required (logically it wouldn't be needed to encrypt the data, just decrypt as you've already stated) for all of the other calls, but it is checked and hence the error given how it is currently written and implemented.

If you're worried about having the keys on the same server or multiple servers in order to do backup operations, you may want to look into using a HSM which does integrate with backup encryption.

  You can integrate encryption keys with Extended Key Management (EKM)
  providers.


Designing a database for a business context involving orders, payment methods and shipping addresses Our group has been developing a webapp to take it live in the future. We got stuck at normalizing a few tables, as it could involve redundancy.

We have some tables that I describe as follows:

shipping table: the address attributes and ID being the primary key.
order table: few attributes along with address attributes and oID being the primary key.
payment/card table: few attributes along with address attributes and pID being the primary key.

Is this in normalized state or do we have redundancy here?

So you have several tables each of which holds an address. You're wondering if these common address-related columns should be in their own table.

The typical answer is "yes" - a separate would be a good idea. There are any number of posts on this subject on this site.

If you do make a common address table will you compare user input to existing rows so you can re-use an existing address_id in one of your tables? If the answer is "no", or you don't have a plan to handle small variations in how the user spells or formats the address, then why bother?


Using Ola Hallengren’s Maintenance in SQL Server 2016 with TDE Enabled databases and compression I’m using Ola Hallengren’s maintenance and backup solution and looking into leveraging TDE compression as I have all the databases in instance using TDE  impression. Is there a way of getting benefit of TDE compression with Ola’s solution?

According to Ola's SQL Server Maintenance Solution Version History
(highlighting mine)

  25 Sep 2016

  Improved handling of databases with Transparent Data Encryption (TDE)
  together with backup compression. If you have databases with TDE in
  SQL Server 2014 and earlier, backup compression is not used. If you
  have databases with TDE in SQL Server 2016, backup compression is used
  if MAXTRANSFERSIZE is greater than 65536.

You should make sure that you can restore these compressed backups.  There were some problems as referenced by this Stack Exchange post (Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used)


How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0:

SELECT t1.contact_id,
       t1.contact_group_id,
       t1.contact_email,
       COUNT(t2.contact_id) - 1 row_number
FROM contacts t1
JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id
                AND t1.contact_id >= t2.contact_id
GROUP BY t1.contact_id, t1.contact_group_id, t1.contact_email

Use this enumeration in DATEADD().


Return all rows from table1 and corresponding rows from table2 even if null I'm new into SQL so please bear with me. The question itself is confusing as I don't really know how to ask it. Thing is, I have two tables, table1 lists all printers. Table2 lists their page counters, with year/month parameter. What I want to show is ALL printers with their respective page counters filtered by year/month, and NULL if there is no page counter for that specific printer on that specific time frame. To try and clarify a little, this is what I have:

Table1:
id
model
location

Table2:
id
printerid
counter
year
month

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2 ON tb1.id = tb2.printerid
WHERE
   tb2.year = 2017 AND tb2.month = 1

I know the WHERE clause is messing things up, and this query only returns printers that have counters on that year/month parameter. If I remove the WHERE from the query I get duplicates (if a printer has (and will have) counters on different year/months).What I want is something like this:

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null

Instead I'm getting this (without WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null
printer1     150        2017      2

Or this (with WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1

Any help would be much appreciated

You only need to move the WHERE conditions (that involve table2) to the joining, ON condition:

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2
   ON (  tb1.id = tb2.printerid
     AND tb2.year = 2017
     AND tb2.month = 1
      ) ;

The ON condition can be as complex as required, it doesn't necessarily be a simple equality, although that's the most common (the parentheses around the condition are not required either, just to make the code a bit more clear).

It's unclear how you get Printer, Pages, Year, Month columns in the result when you have id, model, location, counter in the SELECT list.


What's the biggest bottleneck (time) in writing data to a SQL database? I'm running an ETL process that is writing about 2 million rows to a SQL Server database.

I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story).

I'm wondering what the biggest bottleneck is, or best way to reduce time, for basic inserts into a SQL database.

I mean, the first thing, probably, is the size of the data, right? Both the number of rows, the number of columns, and the data size in each column. Some of these may not be able to be minimized, the KB/ footprint of each row is one thing that can be potentially optimized, right?

What else can be optimized or is the largest factor? Is it the transmission medium? I mean, how much magnitude of difference is there between writing to a database that's on the same computer, vs writing across a web connection (that is robust, fast, and has a ping of 1 ms?).

Finally --- why is it that multiple parallel connections to the database seem to speed up the process to a point? I mean, when I have 20 connections making inserts round-robin style, it's about 6-7x faster than one connection writing ALL the data. I'm curious why this is.

Right now I have 2.2 million rows totaling 2.7 GB. That's 1.23 kb per row.

Right now inserting 1000 rows at at time (1.23 MB) using 14 connections takes 6.7 seconds. That's a snail-paced 10.66 rows per second. Even assuming 1 connection would be just as fast (it isn't) that's 150 rows/ second at best, which is not exactly "fast" either.  I'm writing over a super-fast, robust web connection b/c we can't have the ETL process on the same space as the data warehouse.

So .. how can I optimize for speed here?

The reason for 1000 rows at a time is because the data comes from pages of 1000 - but optimizing the parsing is a separate issue for now.

I do have one primary index I believe, but nothing too write-expensive. Right now I've simply been doing Monte Carlo like testing (try it and see what works) but I need something more focused.

You need to go an read:

The Data Loading Performance Guide
How to analyse SQL Server performance
Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)
bcp.exe
Transferring Data Efficiently by Using Partition Switching
Operations That Can Be Minimally Logged
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
SqlBulkCopy Class
SSIS OleDB destination
Performing Bulk Copy Operations (for OleDB and ODBC examples)

You have to read each link. Really. The short story is that efficient loading must use bulk insert and achieve minimal logging. SSIS is by far the better tool for that, but you can achieve this also programatically. OleDB is the best, but SqlClient (C#) will also do, if needed. Things like row size, network speed and the like are unlikely to be your concern here, but you should always measure (and the articles linked will teach you how). Load performance should be completely orthogonal to existing DB size, if load speed decreases as the DB size increases you must be doing something wrong.

  Finally --- why is it that multiple parallel connections to the database seem to speed up the process to a point? I mean, when I have 20 connections making inserts round-robin style, it's about 6-7x faster than one connection writing ALL the data. I'm curious why this is.

I'll be speculating, due to lack of any investigation and measurement. But most likely you are now blocked by commit flush rate: every time you COMMIT SQL has to stop and wait for the log to be written to disk. If you do not explicitly begin transactions then every statement must stop and wait. Adding more loaders causes causes better log utilization, the log gets committed for 20 writers at once. Read What is an LSN: Log Sequence Number.


Restrict access to new database by default I am administering a Postgres server for students on a very basic database course. Students get there own accounts, and are allowed to create databases. The problem: when a student creates a database, the default is that another student can connect to it, and create their own tables in it. (They can't select or insert on each other's tables, however.)

They can prevent this themselves with:

revoke all privileges on database the_database_i_just_created from public;

But what I'd like is for that behavior to be default, so that they don't have to remember to issue the revoke command. There doesn't seem to be anything in alter default privileges that does this. Is there any way to accomplish what I want? It doesn't have to be exactly this, just something that prevents users to mess with each other's databases by default.

I have complete control over the server, so changing configuration files or whatever is fine. It's also fine if I have to issue one command for each user – I can do that in the script that creates the students' accounts.

I think you cannot do this using only the tools PostgreSQL offers.

On one hand, there are no default privileges, as you noticed. The REVOKE command issued on a template DB (template1 in most cases) has no effect on the created DB. Last, the event trigger mechanism does not support shared objects (including databases).

I think the best way would be to create one DB for them each, and confine them to that one from wherever you create the users currently.


Documenting a collection of databases and their relationships with each other As part of a project I'm currently involved with we need to identify where various data points are held within existing systems, with a view to integrating them into our central integration engine. We're part of an NHS Trust, so there are many databases, reporting databases, etc.

There seem to be many tools to design & document a single database, but what I'm after is something a level higher, which can record the relationships between data elements in different databases. I can't find anything on Google, but I wonder whether I'm just using the wrong terms.

At the moment we're solving the problem by recording the database, table, attribute & a sample query for each data item. A system which could at least group the different data elements by table, database, etc. would be useful.

It seems this would be useful to any organisation with many disparate databases, so want to check it doesn't already exist (or that there's not a better solution for the problem).

Firstly, let me say from the outset that you have my deepest sympathies! :-) Maintaining a true-to-life up-to-date schema of even a single database is bad enough, but having to maintain a single point of truth (SPOT) for a multi-database disparate data-source environment is going to be nothing short of hell on earth!

You _must_ establish a SPOT for your systems - one path in, one path out. If you don't have serious senior management buy-in for this project, it is doomed from the start!

Take a look here and follow the links to K. Scott Allen's posts. Check out Agiledata.org and Allen's site ambysoft.com. Establish the relationships - there are schema generation tools for every database under the sun, but given that I imagine you have a large hetergeneous environment, you probably have everything from Oracle Enterprise servers down to spreadsheets on individual's machines.

Bear in mind that there's only one thing worse than no documentation and that's inaccurate documentation (think prominent, but defective, fire extinguisher).
Furthermore, if your central system is seen to sub-optimal, all that will happen is that workers on the ground it will be bypassed and become a shelf-ware irrelevancy.

The approach I would take in this situation is to establish a framework whereby there is [1 person | a small group] responsible for a given system and it is up to them to maintain their part of the schema documentation and ensure that they have the tools to do so. Yours should be more of an organisational/oversight role. Make it SOP that your group be informed of any schema changes (immediately) so that their (and by extension the central) meta-data repository is kept up to date. IMHO, you face an uphill battle and I wish you the best.

As for tools for documenting a given system, leave that up to the experts in those systems. As for a tool which consolidates "meta-meta"-data, I'm not aware one - apart from graft that is.


Simplifying Function with Recursive CTE and/or Window Function I'm trying to come up with a Recursive CTE and/or Window Function to create a function.

After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:

  En = Bn * (1 - SUM(E1, E2, ... En-1))

Examples:

╔═══╦═════════════╦═════════════╗
║ N ║ B           ║ E           ║
╠═══╬═════════════╬═════════════╣
║ 0 ║ 0.142857143 ║ 0.142857143 ║
║ 1 ║ 0.285714286 ║ 0.244897959 ║
║ 2 ║ 0.285714286 ║ 0.174927114 ║
║ 3 ║ 0.285714286 ║ 0.124947938 ║
║ 4 ║ 0.285714286 ║ 0.089248527 ║
║ 5 ║ 0.4         ║ 0.089248527 ║
║ 6 ║ 0.666666667 ║ 0.089248527 ║
║ 7 ║ 1           ║ 0.044624264 ║
╚═══╩═════════════╩═════════════╝

  E0 = 0.143 * (1 - 0) = 0.143
  E1 = 0.286 * (1 - 0.143) = 0.245
  E2 = 0.286 * (1 - (0.143 + 0.245)) = 0.175
  E3 = 0.286 * (1 - (0.143 + 0.245 + 0.175)) = 0.125
  E4 = 0.286 * (1 - (0.143 + 0.245 + 0.175 + 0.125)) = 0.089
  E5 = 0.400 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089
  E6 = 0.667 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089
  E7 = 1.000 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089 + 0.089)) = 0.044

If the table above was in Excel, C2 = B2 * (1 - 0) (base) and C3 = B3 * (1 - SUM(C$2:C2)) (recursive)

What I've tried:

Windowed Functions

Tried SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), but can't reference the column recursively.

Recursive CTE

Tried several iterations of:

WITH B AS ([Num], [Best], [Effective Rate]) AS (
    SELECT *
        , [Best]
    FROM A
    WHERE [Num] = 0
    UNION ALL
    SELECT A.*
        , (1 - [Effective Rate]) * A.[Best]
    FROM B
    JOIN A ON A.[Num] = B.[Num] + 1
)

and some with an extra column in the CTE, but it only covers 1 previous row and results after 2nd row are wrong.

Recursive CTE with Windowed Function

From all that I've tried, it seems that the recursive segment of the CTE is calculated independently of the other results, and SUM(...) OVER(...) only works on the current row. (With regard to the above table, all values of E would be 0.142857143).

I assume this is because the UNION ALL happens all at once, and not incrementally.

Alternative Solutions

What I would really like to happen is to simplify the above equation, and/or transform it into an iterative function.

Bonus: If anyone cares to know the source of this information, it's used to calculate MACRS depreciation for tax purposes.

You need an extra column to carry along the running total (fiddle).

In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is your SUM(E1, E2, ... En-1).

WITH R
     AS (SELECT N,
                B,
                E = B,
                RunningTotalE = B
         FROM   A
         WHERE  N = 0
         UNION ALL
         SELECT A.N,
                A.B,
                E = A.B * ( 1 - R.RunningTotalE ),
                RunningTotalE = A.B * ( 1 - R.RunningTotalE ) + R.RunningTotalE
         FROM   R
                JOIN A
                  ON A.N = R.N + 1)
SELECT N,
       B,
       E = CAST(E AS DECIMAL(10,9))
FROM   R


Determining maximum table size, type of engine and type of indexes supported How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server?

I also want to determine the maximum table size supported and consumed and the type of storage engine SQL Server is using.

Please explain both, 2005 and 2008 professional and express editions.

SQL Server has B-Tree indexes only. Other types are hardly used day to day.
All maximum capacity specs for 2008 are here: there is a link to SQL Server 2005. Note SQL Server does not have "limits" apart of disk space for size of the DB or a table.
Express differs in other ways: see editions
Like Sybase, Oracle, Postgres, SQL Server is a single engine. As an approximation, InnoDB is MySQL's closest in concept

Note: if you come from MySQL, do not make assumptions about SQL Server. It (like other RDBMS) are more complex and mature then MySQL in many respects


Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint? Using temporal tables one is able to use the FOR_SYSTEM TIME clause with combinations with other options to get valid rows for specific time intervals.

For example, we have:

FOR SYSTEM_TIME ALL
FOR SYSTEM_TIME AS OF @dt
FOR SYSTEM_TIME clause is FROM @start TO @end
FOR SYSTEM_TIME clause is BETWEEN @start AND @end
FOR SYSTEM_TIME clause is CONTAINED IN (@start, @end)

What is said in the documentation and I believe is not true is:

  If you search for non-current row versions only, we recommend you to
  use CONTAINED IN as it works only with the history table and will
  yield the best query performance.

For example, you can see below both table are queried:

Fortunately, Itzik Ben-Gan advises to create check constraint to the temporal table like this in order to help the SQL Engine:

ALTER TABLE [dbo].[SecurityUsersEncrypted]
ADD CONSTRAINT [CK_SecurityUsersEncrypted_SysEndTime]
CHECK ([SysEndTime] = '9999-12-31 23:59:59.9999999');

And the main table is not queried:

If I am using the maximum end period (forcing reads of the main table), the execution plan is changed, too (which is normal):

And if I use one of the other clauses, even my query do not return data from the main table (only history data is returned), the optimization is not applied (as pointed in the documentation):

So, my questions are:

Why in the documentation is said a optimization is working for CONTAINED IN clause, but actually you need to create such check constraint to use it? Should I always create such constraint or this is going to change?
All clauses can return rows from both tables and one of them. Why this optimization is working only for the CONTAINED IN clause?

Why in the documentation is said a optimization is working for CONTAINED IN clause, but actually you need to create such check constraint to use it?

The optimization is really occurring - even though the main table index scan appears in the execution plan, it is never executed because it's behind a filter with a startup expression predicate.

See below for a more detailed explanation.

  All clauses can return rows from both tables and one of them. Why this optimization is working only for the CONTAINED IN clause?

I don't think anyone but the SQL Server product team could answer that one, unfortunately.

The execution plan is a bit misleading here.  If I create a temporal table:

CREATE TABLE dbo.ThisIsJustATest
(
  [Id] int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
  , [Junk] nvarchar(2048) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ThisIsJustATestHistory));

And then load it with data:

INSERT INTO dbo.ThisIsJustATest (Junk)
SELECT m.[text] FROM sys.messages m
OPTION (MAXDOP 1);

And then update some rows (so that I have history rows to query):

UPDATE dbo.ThisIsJustATest
SET Junk = 'Busted'
WHERE Id % 100 = 0;

And run this query (that only returns history rows) with stats I/O on:

SET STATISTICS IO ON;

SELECT *
FROM dbo.ThisIsJustATest FOR SYSTEM_TIME CONTAINED IN ('2018-09-11 14:17:00.96', '2018-09-11 14:18:51.35');

Here's what I get:

Table 'ThisIsJustATest'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So according to stats I/O, it really did zero reads from the base table - it only read from the history table.  Here's the execution plan.

Notice that all the predicates on the base table's index scan operator are residual predicates.  So in theory, the whole table should get read (even though there are no matching rows).  So there is some trickery going on on.

It doesn't read anything from the base table because the predicate in the filter operator must always evaluate to false:

'2018-09-11 14:18:51.35'=sysmaxdatetime((2))

Note that the predicate is a Startup Expression, so the Clustered Index Scan below it does not get started when the expression evaluates to false. sysmaxdatetime is an intrinsic (internal) function that presumably returns the maximum date/time value at the given precision.

The "Number of Executions" on the Clustered Index Scan is 4, but this is simply an internal quirk related to the way parallel plans work - a thread does call Open() on the subtree to ensure all necessary threads get created and initialized, but no real work is done. In a serial version of the same plan, the number of executions would indeed be zero.

What about the CHECK constraint?

For what's worth, it appears that adding the check constraint is still useful as a performance tuning option, as it reduces logical reads on the history table by quite a bit.

Before the constraint:

Table 'ThisIsJustATest'. Scan count 0, logical reads 0
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 160

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.

After the constraint:

Table 'ThisIsJustATestHistory'. Scan count 1, logical reads 53

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.


MySQL Help - Combining 2 MySQL Selects - Result in 1 I have two MySQL statements (see below), I would like to combine them together so if they both result in a 1 then the end result will be 1. I'm not sure how to construct this and was hoping for some help.

select count(*)
from monitor
where name='job_starttime' and value < ( UNIX_TIMESTAMP( ) -600)

select count(*)
from monitor
where name='job_active' and value = 0

So for example I would like when both statements are true to result in a value of 1, if 1 or none are true it results in a 0.

I nearly achieved it with the below SQL (except it's giving a result for each entry in the table) :(

select
case
when (select count(*) from monitor
where exists (select * from monitor where name = 'job1_active' AND value = 1)
and exists (select * from monitor where name = 'job2_active' AND value = 1)) > 1
then 1
else 0
end
from monitor

(I am assuming you want the result in a WHERE or some other place for testing?)

This is faster, but is equivalent to COUNT(*) >= 1.

SELECT ...
    WHERE EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_starttime' AND value < ... )
      AND EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_active' AND value = 0 )

Otherwise:

SELECT ...
    WHERE ( SELECT COUNT(*) ... ) = 1
      AND ( SELECT COUNT(*) ... ) = 1

Another tip:  false == 0; true == 1.


Can one database affect the performance of another in MongoDB? We have a fairly beefy server (15 GB RAM, 2 cores) running a reasonably large MongoDB instance. The Mongo instance hosts two separate databases that communicate via application code. The larger database (let's call it DB1) contains on the order of 100 million records with more being added all the time.

The second DB (let's call it DB2) contains about 10 million records and grows much more slowly (there are a lot of writes, but most are updates).

Recently, we noticed that reads on DB2 have become ridiculously slow, taking up to 2-3 minutes to simply loop through about a 1000 records. On profiling the query, it looks like the right indexes are set and are being used, but that the performance for some as-yet unknown reason has completely fallen off a cliff.

Considering that DB2 doesn't grow much, could this be because DB1 is affecting the performance (maybe by competing for RAM)? Is there a more direct mechanism for two databases to interfere with each other?

I'm trying to identify the bottleneck here, so any pointers in the right direction would be much appreciated.

I should also note that while DB1's performance has also degraded a bit, it hasn't completely fallen off a cliff like DB2.

I can add, that WiredTiger and mongo 3.2.11 will not help you. Theoretically it hasn't global locks, but in practice I have a bit more beefier server (12 core, 96GB RAM) and two databases. DB1 is in production (it is only 300GB in size, so mostly in RAM) and DB2 which I prepare for next release.

Surprisingly, found that mass updates and deletes on DB2 (also 300GB), even in one thread (one console or java application) make regular indexed queries on DB1 run for 5-10 SECONDS.

When I stop all activity on DB2 it returns to 3-400ms.

In other topics I've read that deleting by unindexed field might lead to such behaviour, but unfortunately, not in my case. I update/delete by "_id" and still performance is terrible.


Performance Implications of encrypted data in a Clustered Columnstore Index I am using SQL Server 2016 and expecting some details on below points regarding performance point of view:

Is there any performance gain or additional benefit on creating a primary key - unique constraint on same table on which a clustered columnstore index is already created?
If my table contains encrypted columns (out of 10 columns, 9 columns are encrypted), is there any benefit in case of creating clustered columnstore index? I am using SYMMETRIC KEY for encryption.

It's best to avoid asking multiple, unrelated questions in a single question when possible. Your first question is very broad. In general, creating an index is always a trade-off and some queries will benefit from that index. The same is true for NCIs on columnstore tables. I'll focus on the second question instead.

As far as I can tell, encryption by SYMMETRIC KEY can be a very poor match for columnstore. Inserting encrypted data into a CCI means that you're inserting long, relatively unique strings into varbinary columns. CCIs don't do well with those data types and data distributions. You're likely to hit dictionary pressure which will limit the size of your rowgroups and you'll miss out on most of the features of columnstore, except for column elimination.

I found the code at How to use SQL Server Encryption with Symmetric Keys helpful in developing a simple example. I don't know what your data looks like, so I'll just insert unique integers into a table and insert the same unique integers with encryption into a different table. Definition of the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'you will never guess this';

CREATE CERTIFICATE MyFirstCertificate
WITH SUBJECT = 'no u';

CREATE SYMMETRIC KEY key_to_answering_217242 WITH
IDENTITY_VALUE = 'yeah nah',
ALGORITHM = AES_256,
KEY_SOURCE = 'closet full of jandals'
ENCRYPTION BY CERTIFICATE MyFirstCertificate;

T-SQL to create two tables and insert data into both of them:

DROP TABLE IF EXISTS dbo.CCI_NOT_ENCRYPTED;

CREATE TABLE dbo.CCI_NOT_ENCRYPTED (
ID BIGINT,
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NOT_ENCRYPTED WITH (TABLOCK)
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

DROP TABLE IF EXISTS [TEST].dbo.CCI_ENCRYPTED;

CREATE TABLE [TEST].dbo.CCI_ENCRYPTED (
ID VARBINARY(256),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO [TEST].dbo.CCI_ENCRYPTED WITH (TABLOCK)
SELECT EncryptByKey(Key_GUID('key_to_answering_217242'), CAST(RN AS VARCHAR(7)))
FROM (
    SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

There's a clear difference in total table size. The table with encrypted data is over 20X bigger:

Looking at sys.dm_db_column_store_row_group_physical_stats, we can see that the table with encrypted data had its rowgroup size limited by dictionary pressure:

Query performance will of course be greatly impacted as well. The following query takes approximately 0 ms of CPU time:

SELECT COUNT(*)
FROM dbo.CCI_NOT_ENCRYPTED
WHERE ID = 1;

If I run that same query on the encrypted table:

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

SELECT COUNT(*)
FROM [TEST].dbo.CCI_ENCRYPTED
WHERE CAST(CAST(DecryptByKey(ID) AS VARCHAR(7)) AS BIGINT) = 1;

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

It takes over 600 ms of CPU time. With all of that said, there appears to be a large overhead in general in working with encrypted data. I cannot say if a columnstore will offer any benefit for your exact data and scenario, only that you definitely won't see the usual benefits of columnstore.


Getting replication status for snapshot replication Is there a way we can get stats on how the snapshot replication is working in an easier report format to check on actually how many objects like tables or sps have been replicates followed by health to check if snapshot replication is actually working as expected

Publisher is Sql 2008r2
Subscriber is sql 2012

Replication Monitor does this for me. You can see the current status, the status of past sessions and the details of which articles got delivered/copied.


Formatting SQL Source Code in SSMS There are several source code formatters for SQL in SSMS.

I have tried a couple and found that are not configurable in terms of what formatting they do (e.g. indenting).

Does anyone know of one that is configurable in terms of how it formats the code?

Thanks.

Red Gate's SQL Prompt is what I use and has several dozen different settings. It also has a better auto suggestion and code snippet engine than many others I've tried. Well worth the money in terms of time saved.


How expensive are SELECT FROM LIKE statements from a design perspective? I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files drastically very very soon. I am investigating for quick solutions to release the pressure.

One option is to move the file's content (UTF8 text) into a database and perform SELECTs to replace the file search (by name). The select statements would be the following:

SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE 'criteria%';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%othercriteria';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%andanothercriteria%';

We are talking about a total of 200K requests per day on a 800K rows table (which I could easily split in two if it helps). MyContentName is part of the key and will be indexed. There is either one entry matching the criteria in the table or none.

I am no DB admin expert. Is this something a MySQL instance on a shared server can support or are my expectations too high?

I know the typical answer is: I should test. Unfortunately, because of the emergency, I don't have time to perform tests. I need to find a quick solution, even if temporary, even if it degrades service response delays a little.

I am looking for experienced DB admin's opinion on this strategy. Tips and suggestions are welcome too.

If you are not able to fine tune the filesystem (for instance, by using a smaller block size), and you really must use a database, I'd suggest the following readings:

http://en.wikipedia.org/wiki/B-tree
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

The first will explain the data structure most commonly used by indexes, the B-Tree. The second explains how MySQL uses the B-Tree. The third will tell you about the command EXPLAIN SELECT ..., which is how MySQL describes the query plan (it will tell you which (if any) index it is using, if it is doing table scans -- which you must avoid at all costs).

To create an optimized index, you should first think about the structure of the query (or queries) you will need. For example, it might be something like: select content from files where firstParameter = XXX and secondParameter like 'xxx%'.

You should analyze the cardinality of each column (ie, how many different values can the column have).

You chose the columns with highest cardinality to be the first in an index, leaving those with lower cardinality to the end. Example: suppose you have 2M rows and firstParameter is a number between 1 and 1M, randomly distributed, and secondParameter is the full name of the owner of the file. In such a situation, you want the index (firstParameter, secondParameter), in this very order, since the clause firstParameter = XXX will leave you, in average, with only 2 rows. The cardinality of secondParameter, on the other hand, is much lower: there are much less then 1M possibilities for people's names. Therefore, if your index is (secondParameter, firstParameter), the query where firstParameter = 1 and secondParameter like 'bruno%' will first look for every row in which secondParameter begins with bruno (which will be likely tens or hundreds of thousands), and only then it will look for the other condition.

Also, note that the index is used from left to right. That is, if you have 3 columns, A, B and C and you index (A, B, C), the index will be mostly useless in a query such as where A = 1 and C = 2. It will likely be used to find rows matching A = 1, but after that every row will be checked for C = 2. If most of your queries are like that one (and some might specify B as well), than your index should be (A, C, B).

Finally, note that like 'xxx%' can use an index, while like '%xxx' (or like '%xxx%') cannot. This is, again, because the index is read from left to right. To match xxx%, it knows where to start looking; to match %xxx it must check every single row.

All that said about indexes, I'd strongly suggest you to rework your criteria so that you have something more structured. As you said, you could try to precompute something.

There are other considerations, such as the size of the content. If you can make it fit in under 8KB (which is like 3000 characters if you use UTF-8), then InnoDB will store the data in the same page as the primary key; otherwise, it will store the data elsewhere. If you query by primary key, in the first case you have a single read operation; if you query by another index, in the second case you have 3 read operations: one to find the primary key of matching row, one to find the row by primary key (to read the address of the data) and one to read the data.

Oh, check the amount of RAM of your server. Ideally your data (or at the very least your indexes) should fit in the RAM.

By considering all these points, you should have no problem at all: I don't know the hardware of your server, or its load (since you said it is shared), but 800k rows is close to nothing if you fine tune your indexes; I'm very far from expert and, by doing all the above things, I work daily with (very optimized) tables with 10M, some 100M rows, and the queries are ultra-fast.

I hope that helps. Once you have your table(s), you could ask another question showing the create table statement and describing a bit about your data (sizes, cardinalities, etc) and the select queries you will use, so someone could help you to create an optimized index.


What does it mean there is multiple sources in a SELECT's "FROM" clause? I have a table with a jsonb column, and I wanted to select from this table with the contents of that column extracted into columns:

CREATE TEMPORARY TABLE stuff (
    name text,
    contents jsonb
);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello", "y": "postgres", "z": "rdbms"}'::jsonb);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello2", "y": "postgres2", "z": "rdbms2"}'::jsonb);

CREATE OR REPLACE FUNCTION pg_temp.extract_stuff (s jsonb, out x text, out y text, out z text)
    RETURNS record
AS $$
BEGIN
    x = s ->> 'x';
    y = s ->> 'y';
    z = s ->> 'z';
END;
$$
LANGUAGE plpgsql;

I was screwing around and somehow landed on this statement, which works perfectly and looks very intuitive:

SELECT
    name,
    foobar.x,
    foobar.y,
    foobar.z
FROM
    stuff,
    pg_temp.extract_stuff (contents) foobar;

 name  |   x    |     y     |   z
-------+--------+-----------+--------
 hello | hello  | postgres  | rdbms
 hello | hello2 | postgres2 | rdbms2

However, I'm not sure I understand how this works. Is it shorthand for an implicit inner join of some sort? What is the limitation of this type of syntax (can there be multiple physical tables, etc)?

Implicit vs Explicit JOIN Syntax

It's called a SQL-89 Implicit (Cross) Join. You should prefer the more modern Explicit syntax. In PostgreSQL the Implicit syntax has a different precedent, and it currently serves as an optimization fence. What you actually want is a CROSS JOIN and because you're using the variables from the left table in a function call what you want is a CROSS JOIN LATERAL.

SELECT
    name,
    foobar.x,
    foobar.y,
    foobar.z
FROM stuff
CROSS JOIN LATERAL pg_temp.extract_stuff (contents) AS foobar;

The reason for the preference is style: the newer syntax allows for syntactical binding between the JOIN-clause and the condition that provides the JOIN's selectivity. While technically this is no different, it makes it easier to maintain and read. The SQL-89 method is to stuff the JOIN-selectivity in the  WHERE-condition. This makes maintaining the query a ton easier.

SELECT f.g
FROM f AS f1
JOIN f2 USING (fid);
JOIN f3 ON f3.fkey_id = f.id
WHERE f.x = 15;

Versus,

SELECT f.g
FROM f, f2, f3
-- Potentially hundreds of lines.
WHERE f.x = 15          -- no
AND f.id = f2.id        -- specific
AND f3.fkey_id = f.id;  -- order

Expanding the scope of the question to [INNER] JOIN, you may want to stare at this example for a bit,

SQL-92: Explicit JOIN with Explicit INNER

SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;

SQL-92: Explicit JOIN with Implicit INNER

SELECT *
FROM a
JOIN b ON a.id = b.id
JOIN c ON b.ref = c.id;

SQL-89: Never use without reason

SELECT *
FROM a, b, c
WHERE a.id = b.id AND b.ref = c.id;

SQL-92: CROSS JOIN with JOIN's selectivity-clause written as a WHERE-clause. Some would say this is "flattened".

SELECT *
FROM a
CROSS JOIN b
CROSS JOIN c
WHERE a.id = b.id AND b.ref = c.id

SQL-92: Explicit JOIN with goofy order (may be useful for optimizing, depending on RDBMS)

SELECT *
FROM a
JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Unless you have a reason, you should always prefer the first or second syntax.

USING clause

USING is shorter (less to type), and more terse. This can also save you some work and seems less problematic from my perspective.

Column Listing

USING adds the nicety of not reproducing the column when * is expanded. Let's again look at some examples,

This will return one row with one column: (a=1). It can do this because its t.a and g.a are marked as the same with USING and as the equijoin condition they must be equal.

-- Returns one column with a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  USING (a);

This will return one row with two columns: (a=1,a=1).

-- Returns two columns with a=1,a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  ON t.a = g.a;

That's nice, but even if you never use a there is something to be had with USING.

This will gladly work

SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  USING (a);

This dies: requires explicitly picking an 'a' to disambiguate

SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  ON t.a = g.a;

Mixed Use

Some detractors think it's nasty to mix USING with ON. I don't think so, but take a look for yourself.

SQL-92: With USING sugar, Explicit JOIN with implicit INNER token

SELECT *
FROM a
JOIN b USING (id)
JOIN c ON b.ref = c.id;

SQL-92: Without USING sugar, Explicit INNER JOIN

SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;

Convention

USING establishes a convention from the spec of making your equijoins on id-columns that are globally unique: by this I mean it kills off the all too common convention of naming every table's surrogate key id. Generally, I think that's a admirable goal (or side effect) when possible.

Anti- JOINs

As a final note on SQL-92. SQL-92 provides the EXISTS predicate. This gives you the option to write an anti-join like this,

SELECT *
FROM t1
LEFT OUTER JOIN t2 USING (id)
-- could
-- be
-- lots of stuff
WHERE t2.id IS NULL;

Or, like this

SELECT *
FROM t1
WHERE NOT EXISTS (
  SELECT 1
  FROM t2
  WHERE t1.id = t2.id
);

For the same reasons presented earlier, the option with NOT EXISTS is better then WHERE t2.id IS NULL: it keeps the joining-conditionals semantically linked. And it clears up two otherwise potential areas of confusion. For instance

WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
LEFT OUTER JOIN t AS t2
  USING (n)
WHERE t2.n IS NULL;

Versus,

WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
WHERE NOT EXISTS (
  SELECT 1
  FROM t AS t2
  WHERE t1.n = t2.n
);

Without the NOT EXISTS syntax, it's not immediately clear whether the intent is to achieve an equijoin on NULL, or to select where there is no match. Though they get planned the same the NOT EXISTS predicate is more descriptive than an IS NULL predicate for the task.

Summary

My preference is for Explicit JOIN syntax with USING whenever possible. And to always write antijoins using NOT EXISTS ( SELECT 1... ).


Cant unlock file (Errcode: 11) on my new tables I get the following error on my MySQL database

Cant unlock file (Errcode: 11)

But this error only happens when I try to access my new table I created below has two tables 1st I created recently and the second was already created when I got the database to update it
How I'm remove the errors?

CREATE TABLE `tbl_produtos` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nome` varchar(140) DEFAULT NULL,
  `Descricao` text,
  `FotoDestaque` varchar(190) DEFAULT NULL,
  `FK_Categoria` int(11) DEFAULT NULL,
  `FK_SubCategoria` int(11) DEFAULT NULL,
  `Count` int(11) DEFAULT '0',
  `Valor` float DEFAULT NULL,
  `Propriedades` varchar(5) NOT NULL DEFAULT '0,1',
  `FotoCapa` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_admin_logs` (
  `RegistroID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UsuarioID` int(10) unsigned NOT NULL,
  `UsuarioNome` varchar(45) COLLATE latin1_general_ci NOT NULL,
  `Modulo` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `Registro` text COLLATE latin1_general_ci NOT NULL,
  `IProtocol` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`RegistroID`)
) ENGINE=InnoDB AUTO_INCREMENT=383 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

EDIT:

SELECT @@VERSION = 5.1.39-community
SHOW GLOBAL VARIABLES LIKE '%locking%'=
Variable_name: skip_external_locking Value: OFF

My suspicion would be that you are either using external_locking or you have character set issues.

It might center around the following bugs

Error 11 - can't unlock file when using external_locking
Inconsistent use of column alias In ORDER BY clause that specified collation

I would suggest upgrading to MySQL 5.6 as this situation is consistent with MySQL 5.1.


Can I use a foreign key index as a shortcut to getting a row count in an INNODB table? I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;
+----------+
| COUNT(1) |
+----------+
| 25453476 |
+----------+
1 row in set (2 min 36.20 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
|  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

You cannot trust the EXPLAIN plan's row count. Why?

Each time Query Optimization takes place where it involves an InnoDB table, the InnoDB Storage Engine does an approximation of the BTREE entries for the keys. They approximations change from query to query.

Simply run SHOW INDEXES FROM guage_data; multiple times and you will get a different set of row counts each time. You could set innodb_stats_on_metadata to 0:

SET GLOBAL innodb_stats_on_metadata = 0;

This will cause SHOW INDEXES FROM guage_data; to give the same number over and over again. This is still self-defeating because the row count would still be approximated anyway. You would have to disable the metadata stats and run ANALYZE TABLE. Again, this is self-defeating.

With innodb_stats_on_metadata on, approximations are always done. I wrote about this back on June 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

In light of all this, you are better off doing SELECT id FROM guage_data if id is the PRIMARY KEY. However, notice in your EXPLAIN plan that MySQL choose to traverse the gauge_data_FI_1 index. This makes all the sense in the world if this table is InnoDB. Why? Every non-unique index stores the rowid of the PRIMARY KEY anyway.


Storing unicode text into varchar column in SQL Server I have mistakenly stored unicode text into a varchar column in a table and now it is shown as question marks (???).

Is it possible to get our main information back from the column instead of question marks???

The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value.

Sorry to say but the original value is lost.


How to cat PostgreSQL's Help Menu In PostgeSQL's psql interactive terminal, you can type \?+<enter> to produce a overview that shows a list of common commands. In Ubuntu Linux, is there a way that I can pipe that content to a file that I can cat or open up in a GUI text editor?

$ echo '\?' | psql > psql_backslash_help.txt
$ head psql_backslash_help.txt
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode


How to format a number with optional fraction Is it possible to format a number in Postgresql with optional fraction?  Like:

1.0 -> '1'
1.3 -> '1.3'

I looked into to_char, but I don't get much further than '1.' with to_char(1.0, 'FM9D9').

Is it possible, and how? I'm using Postgresql 9.5


Are views harmful for performance in PostgreSQL? The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):

  The danger with using views is filtering a query against a view,
  expecting to read a very small portion of a very large table. Any filtering should be done within
  the view because any filtering against the view itself is applied after the query in the view has
  completed execution. Views are typically useful for speeding up the development process but
  in the long run can completely kill database performance.

The following is an excerpt from PostgreSQL 9.5 Documentation:

  Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate
  the details of the structure of your tables, which might change as your application evolves,
  behind consistent interfaces.

The two sources seem to contradict each other ("do not design with views" vs. "do design with views").

However, in PG views are implemented using the rule system.  So, possibly (and this is my question) any filtering against the view is rewritten as a filter within the view, resulting in a single query execution against the underlying tables.

Is my interpretation correct and PG combines WHERE clauses into and out of the view?  Or does it run them separately, one after another?  Any short, self contained, correct (compilable), examples?

The book is wrong.

Selecting from a view is exactly as fast or slow as running the underlying SQL statement – you can easily check that using explain analyze.

The Postgres optimizer (and the optimizer for many other modern DBMSes) will be able to push down predicates on the view into the actual view statement – provided this is a simple statement (again, this can be verified using explain analyze).

The "bad reputation" regarding performance stems – I think – from when you overuse views and start building views that use views that use views. Very often that results in statements that do too much compared to a statement that was hand-tailored without the views e.g. because some intermediate tables wouldn't be needed. In almost all cases the optimizer is not smart enough to remove those unneeded tables/joins or to push down predicates over multiple levels of views (this is true for other DBMSes as well).


Why are these queries slow in some environments and not others? Can we fix their performance? The following stored procedures in a MySQL 5.x environment:

DELIMITER //

CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
        SELECT TABLE_NAME as keyTable, GROUP_CONCAT(COLUMN_NAME) AS keyColumns, REFERENCED_TABLE_NAME AS refTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
        FROM INFORMATION_SCHEMA.key_column_usage
        WHERE TABLE_SCHEMA = schemaName  COLLATE utf8_general_ci
        AND TABLE_NAME = tableName  COLLATE utf8_general_ci
        AND REFERENCED_TABLE_NAME IS NOT NULL
        GROUP BY constraintName;
    END //

CREATE PROCEDURE sp_has_many (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
      SELECT REFERENCED_TABLE_NAME AS keyTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS keyColumns, TABLE_NAME AS refTable, GROUP_CONCAT(COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
      FROM INFORMATION_SCHEMA.key_column_usage
      WHERE TABLE_SCHEMA = schemaName COLLATE utf8_general_ci
      AND REFERENCED_TABLE_NAME = tableName COLLATE utf8_general_ci
      GROUP BY constraintName;
    END //

DELIMITER ;

In one environment they run in a .03 seconds and in another closer to 1 or even 2 seconds.

Our current best guess is that it is primarily a problem with the number of tables and amount of data in one environment versus another.

Can anyone provide additional insights? And is there a way to accomplish the same thing as these procedures while getting more consistent performance?

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

  When this variable is enabled (which is the default, as before the
  variable was created), InnoDB updates statistics during metadata
  statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing
  the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are
  similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does
  not update statistics during these operations. Disabling this variable
  can improve access speed for schemas that have a large number of
  tables or indexes. It can also improve the stability of execution
  plans for queries that involve InnoDB tables.


MySQL vs SQLite on Amazon EC2 I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database.

On AWS you pay for the amount of IOPS (I/O requests Per Second) to the volume. Which database has the least IOPS? Also, can SQLite handle queries from both the program and website simultaneously?

If you are concerned about the cost of iops, which are incredibly cheap in Amazon, then you are talking about a scale which sqlite will not be able to handle. There is no direct way to compare iops between two server types without benchmarking under your exact work load. Even then, i/o is going to be a function of how well your server is tuned.  Generally speaking, a true rdbms has several levels of in memory buffering to reduce i/o as much as possible.  Additionally, adding caching layers in front of the DB will help.

If you expect any concurrency at all, sqlite will not be sufficient.  It is really only good for development or embedded environments.


Best way to arrange a hairdresser database based on their skills I need some help in a smart way to organize a database for a hairdresser shop.
So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc.
I also think I need a table for the procedures the hairdressers can perform. This table would contain the procedure id, description, time required, cost, price and so on.
But I don't see a good way to link those two tables. For example: Professional A can do procedures #1 and #3, but not #2.
How to include this info in the database?
I thought of two possible ways:

Make the procedures be columns in the Professionals table, and assign true in the column in case a professional can perform that procedure;

Make the professionals be columns in the Procedures table, assigning true if the procedure can be performed by that professional.
Either possibilities don't see quite the best way to achieve what I want, specially if the number of professionals/procedures get large.

Since I have very little experience in this, I count on your help giving me some insights in this matter.
Thanks in advance.

You can make a junction table that uses the ID from the professionals table and the ID from the procedure table. That way it solves your issue of Professional A only being linked to procedures 1 and 3.
For example:


Not all things can be easily achieved in SQL.  I think the best approach is to do the test in the application.  In pseudo code:

START TRANSACTION;
$ct = SELECT COUNT(*) FROM booking WHERE ...
if ($ct >= 5)
    error to user
else
    INSERT ...
COMMIT;

OK, I guess this could be encapsulated in a Stored Procedure, plus error handling in your application.


MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement? I am trying to restore multiple mysql dump (.sql) files. Data in each dump file are from the same table, but with different ID (primary key) range.

For example,

> 1.sql (0 < ID && ID <= 100)
> 2.sql (100 < ID && ID <= 200)
> ...

I have 100 files like the above, each containing 'DROP TABLE IF EXISTS' and 'CREATE TABLE' statements. Because of the two statements, I can't simply automate the mysql restore command.

> root$> mysql < dump.sql

Since every time I execute the sql, it will drop the table and delete all data restored. So I need something that I can ignore the DROP and CREATE statements while doing ...

> root$> mysql < dump.sql

Has anyone done this before?

FYI, my OS is Amazon Linux AMI.

cat 1.sql | grep -v "DROP TABLE" | mysql --force

Assuming no other string contains drop commands this should work..

Updated: use --force to ignore errors by CREATE TABLE.


How do you restore to a point in time between the last log backup before a full backup and the full backup? Imagine the following timeline:

T0 = full backup
T1 = log backup
T2 = log backup
T3 = log backup
T4 = full backup
T5 = log backup

After all the above have transpired, if you still have all the backup files, how would you restore to a point in time between T3 and T4?

My understanding is that the full backup at T4 should lead the log file to mark transactions between T0 and T4 as inactive, so in theory, the transaction log may no longer contain transactions from the time before T4. In particular, the log backup at T5 will only contain transaction information from the time after T4. Is this correct?

how would you restore to a point in time between T3 and T4?

You should restore this way:

T0 = full backup
T1 = log backup
T2 = log backup
T3 = log backup
T5 = log backup

I did not understand what you said here:

  My understanding is that the full backup at T4 should lead the log
  file to mark transactions between T0 and T4 as inactive, so in theory,
  the transaction log may no longer contain transactions from the time
  before T4.

But this one:

  the log backup at T5 will only contain transaction information from
  the time after T4.

is INCORRECT.

Every log backup starts from LSN where previous log backup stops, so it doesn't matter if there was or was not any full backup between T3 and T5.

You could take a "shortcut" by restoring only T4 and T5 in case the time you are interested in was after T4, but it's not your case, so you should restore all the log backups until the last one that covers the time you are interested in, starting from full backup that precedes the time you are interested.

P.S. May be it can be useful to you to know that the only thing that can "truncate" the log in full recovery model is log backup. Full backup has nothing to do with "marking transactions inactive".

This article A SQL Server DBA myth a day: (30/30) backup myths may also help.


Does "Table Valued Function" force the plan to get into cache? sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?

does this behaviour is shared with table value function or not?

the short answer is:
It is not, because if you check what execution plans you have in the cache these are the objects you can find there:

cacheobjtype    nvarchar(34)    Type of object in the cache.
The value can be one of the following:

Compiled Plan
Compiled Plan Stub
Parse Tree
Extended Proc
CLR Compiled Func
CLR Compiled Proc

You may be looking to consider:

Plan cache and optimizing for adhoc workloads

check what your plan cache is made up of:

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO

Minimize plan cache bloat with the optimize for ad hoc workloads configuration option

  SQL Server 2008 introduced the ‘optimize for ad-hoc workloads’
  configuration option which, when enabled, causes SQL Server to create
  a plan stub instead of a full plan the first time a statement is
  executed. If the plan is used a second time, then SQL Server will
  store the full plan. This option was introduced to help minimize the
  performance impact of plan cache bloat.

and another thing I would like to mention is..

High compiles (not recompiles):

High Compiles and Multi-Statement Table Valued Functions