How to get the current row in postgres function? I have a table where I have a column called event varchar(255) and user. An event column is a set of strings which tells me in what state was the user when the event was received.

I want to count how many events of each type I have per user.

So I'd like to create a function which would receive as a parameter an event value and then go over each row in the table and if the current row event equals the parameter it will return 1 else 0. The problem is don't how to get the reference to the current row in a postgres function. This is my code so far:

CREATE OR REPLACE FUNCTION decode_event (eventVar varchar(255))
RETURNS integer AS $result$
  BEGIN
    if event like eventVar
      then return 1;
    else
      return 0;
    END IF;
  END;
$result$ LANGUAGE plpgsql;

Then I would run a query like the this:

  SELECT
  user,
  SUM(decode_event('start')) AS start,
  SUM(decode_event('end')) AS end
  FROM stats
  GROUP BY user;

You don't really need a function for that, you can use conditional aggregation:

SELECT "user",
        count(*) filter (where event = 'start') AS start,
        count(*) filter (where event = 'end') AS end
FROM stats
GROUP BY "user";

You can pass a complete row to a function when you declare a parameter with the type of the table:

CREATE OR REPLACE FUNCTION decode_event (p_row stats, p_name varchar(255))
RETURNS integer AS $result$
  BEGIN
    if p_row.event like eventVar
      then return 1;
    else
      return 0;
    END IF;
  END;
$result$ LANGUAGE plpgsql;

Then you can use it like this:

SELECT "user",
        sum(decode_event(stats, 'start')) AS start,
        sum(decode_event(stats, 'end')) AS end
FROM stats
GROUP BY "user";

Note how the table alias is passed to the function. If you give the table a different alias, e.g. from stats as s, you need to pass that alias: decode_event(s, ...)


MariaDB LOAD DATA INFO can't import single column file to single column table? I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.

The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.

Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?

Here's what I am trying to do:

LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;

And here's the table:

CREATE TABLE `common_passwords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;

It seems like we should be able to do this rather easily in SQL...

To quote the manual:

  If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

and

  Interpret characters preceded by the escape character \ as escape sequences. For example, \t, \n, and \ signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.

and

  If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.

and

  ... empty ('')

-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html

That is, if you have tabs or backslashes, the data will be mangled as it is read.  This may explain the problem you are having.

I guess this may be the fix:

FIELDS ESCAPED BY ''


Display equal ranking if count is the same I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For example, if the the first 4 have the same article count then i want them to all rank as 1 and then for the 5th person to rank as 5. The following query outputs as shown below:

SELECT @rownum:=@rownum + 1 as Rank,
   subq1.*
FROM ( SELECT Uni.University_Name, COUNT( * ) AS ArticleCount
FROM Author_University AS AU, University AS Uni, Articles AS Art
WHERE Art.Year_Published >= '2010'
AND Uni.University_ID = AU.University_ID
AND Art.Article_ID = AU.Article_ID
GROUP BY University_Name
ORDER BY ArticleCount DESC
LIMIT 0 , 25
  ) subq1,
(SELECT @rownum := 0) r

Here is one way to achieve equal ranking with variables:

SELECT
  @lastrank := (subq1.ArticleCount = @lastcount) * @lastrank
            + (subq1.ArticleCount <> @lastcount) * (@rank := @rank + 1) AS Rank,
  subq1.University_Name,
  @lastcount := subq1.ArticleCount AS ArticleCount
FROM
  (
    SELECT
      Uni.University_Name,
      COUNT(*) AS ArticleCount
    FROM
      Author_University AS AU,
      University AS Uni,
      Articles AS Art
    WHERE
      Art.Year_Published >= '2010'
      AND Uni.University_ID = AU.University_ID
      AND Art.Article_ID = AU.Article_ID
    GROUP BY
      University_Name
    ORDER BY
      ArticleCount DESC
    LIMIT
      0, 25
  ) AS subq1,
  (
    SELECT
      @lastcount := 0,
      @lastrank := (@rank := 0)
  ) AS r
;

The purpose of the three variables in this method is:

@rank – to calculate and store sequential, unique ranking;
@lastrank – to store the rank of the previous row;
@lastcount – to store the count value of the previous row.

Here is how the Rank expression works. If the current row's ArticleCount is equal to @lastcount, the ArticleCount = @lastcount condition evaluates to true and the other, ArticleCount <> @lastcount, of course, to false. In the context of an arithmetical operation (*), true is implicitly converted to 1 and false to 0. So, the expression becomes equivalent to this:

1 * @lastrank + 0 * (@rank := @rank + 1)

which, in turn, simplifies to just @lastrank. So, when ArticleCount is equal to @lastcount, @lastrank retains its value from the previous row. The current count is then stored in @lastcount to be compared against on the next row.

That way @lastrank stays the same until ArticleCount is no longer equal to @lastcount. At that point true and false – and, consequently, 1 and 0 – trade places and the expression becomes equivalent to this:

0 * @lastrank + 1 * (@rank := @rank + 1)

and so in the end @lastrank gets the result of evaluation of (@rank := @rank + 1), which will simply be a new ranking. Note, however, that (@rank := @rank + 1) actually evaluates on every row but assigned to @lastrank only when ArticleCount changes to a new value.

And it is the fact @rank increases on every row that allows us to get this kind of ranking, with gaps. For comparison, consider this CASE expression:

CASE WHEN ArticleCount = @lastcount THEN @lastrank ELSE (@rank := @rank + 1) END

or its equivalent using the IF() function:

IF(ArticleCount = @lastcount, @lastrank, (@rank := @rank + 1))

Each of these variations would give us equal ranking as well, but the ranking would be without gaps – that is, instead of e.g. 1, 1, 3, 4, 4, 4, 7 we would be getting 1, 1, 2, 3, 3, 3, 4. The reason is, the (@rank := @rank + 1) assignment in each case would take place only if/when Article differed from @lastcount – in other words, @rank would both increase and be assigned to @lastrank only when ArticleCount changed, unlike the first method, where @rank increases constantly.


Solaris 10 - Why shell limits needs to be increased to run oracle database? Shell limits affect shell and scripts running shell like bash.
Oracle's requirements (https://docs.oracle.com/cd/E11882_01/install.112/e48357/pre_install.htm#SSDBI1182 - section 2.10) is to modify these values to a given minimum and to set them to unlimited in production environment.

If the oracle database run a set of binaries, how would changing ulimits affect the database?

Oracle might be binary, but it will still initiate system commands which in term could involve shells. On UNIX systems, the ulimit command controls the limits on system resource, such as process data size, process virtual memory, and process file size. It is also mostly the users processes affect parent processes. So the parent processes manage the users. Normally ulimit is inherited from the main user, so allowing you to set a value for Oracle might be in the interest of your system administrator. In Solaris it is ulimited. ulimit -Ha and ulimit -Sa will show the hard and soft limits. Good rule of thumb is to ensure the soft limits do not exceed the hard. But unlimited for a dedicated Oracle server would be better.


Can one part of a multi-column primary key be used across tables? I want to use SQLite as file format for my app, a reporting program.
Below is an idea of the kind of schema I'll use (just the first few tables).
There will be one report per file and all tables are children of that. So far so good.

But I want to be able to merge files, eg. to store 100 reports in one file. I've read that with a little effort, I can use GUIDs in SQLite to avoid dealing with key conflicts when merging databases. Sounds good, but I need a GUID for every record in every table right? That's a lot of GUIDs for my statistics table which might be large!
I then read about multi-column primary keys.
Is there any way I can create just one GUID for a report, and have every child of the report incorporate that GUID into its key to make every record's key unique?
Is there a better overall approach?

It is not possible to have 'hidden' columns.

When you are using GUIDs to avoid merge conflicts, you must use GUID keys in every table that contains data to be merged.
And when you already have a GUID, it does not make sense to add another column to the primary key.

If you do not want to use GUIDs, just deal with the conflicts, i.e., instead of blindly copying the records, map all old IDs to new ID values.


Why would a query work slower when a where clause is added? I've got two databases and both have got the same view over the same table which has the same indexes.

The view selects the top location for a given IMEI from a locations table.

CREATE VIEW [dbo].[LatestDeviceLocation]
AS
SELECT DISTINCT t.Imei, t.Accuracy, t.UserId, t.Lat, t.Lng, t.Timestamp
FROM (SELECT Imei, MAX(Timestamp) AS latest
    FROM      dbo.DeviceLocation
    GROUP BY Imei) AS m INNER JOIN
    dbo.DeviceLocation AS t ON t.Imei = m.Imei AND t.Timestamp = m.latest
GO

I'm querying the view with a very simple select with what seems like a very simple where clause.

SELECT TOP 1000 [Imei]
      ,[Accuracy]
      ,[UserId]
      ,[Lat]
      ,[Lng]
      ,[Timestamp]
  FROM [dbo].[LatestDeviceLocation]
  Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z'

On my live server when I query my view I get data back in < 1 second. When I add a where clause Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z' that jumps up to approximately 1 minute.

On my test server which has 10x more data (350k+ locations shared by approximately same number if Imei numbers as the live site, 25) the query returns data in < 1 second with or without the where clause.

I've looked for locks and can't see any.

I've re-created the index incase it was corrupted and no difference.

I've completely removed the index, performance didn't change.

This is the index that I've used on both servers.

/****** Object:  Index [GangHeatMapIndex]    Script Date: 02/26/2015 22:38:38 ******/
CREATE NONCLUSTERED INDEX [GangHeatMapIndex] ON [dbo].[DeviceLocation]
(
    [UserId] ASC,
    [Timestamp] ASC,
    [Imei] ASC
)
INCLUDE ( [DeviceLocationId],
[Accuracy],
[Lat],
[Lng]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Edit: I've just realised that I wasn't looking in the right place for locks. It is taking out object locks when querying. I'm trying to work out how to write my view with "no lock" built into the view.

Edit 2: I've attached the execution plans, top on is with the index, bottom is without.

Edit 3: More executions plans, this time all on the live server, with the index re-added, with and without where clauses.

Edit 4:

I've changed the view to use a common table expression as follows and the performance is much better.

WITH cte
       AS (SELECT Rank()
                    OVER (
                      partition BY dloc.[Imei]
                      ORDER BY dloc.[Timestamp], devicelocationid DESC) AS arank,
                 dloc.*
           FROM   [dbo].[DeviceLocation] AS dloc)
  SELECT [Imei], [Accuracy], [UserId], [Lat], [Lng], [Timestamp]
  FROM   cte
  WHERE  arank = 1

Including the device DeviceLocationId in the order by prevented any duplicates occurring in the final result.

Edit - The below is assuming that the number of rows that would be returned by the query (if the limit wasn't present) exceeds the limit. (as in ... it regularly would return 5000 rows, but the limit forces it to return 1000)

Any time you have a limit on the number of rows returned by a query, you should not expect the TIMING on that query to have any sort of relevance to performance.

For example, if you take a simple query as such:

SELECT * FROM table_with_1m_rows;

It will take a while to process because it has to fetch all the rows via a sequential scan..

If I adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows;

It will return relatively quickly because, while it still does a sequential scan, it can STOP after it gets past 1000 rows.

If I then adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows WHERE col1 > 100;

It will take LONGER than the previous query because, while it still does a sequential scan, it will most likely have to scan more than 1000 rows before it has 1000 rows to return with.

All of the above holds true whether the DB needs to use a sequential scan or an index scan..

If you truly want to troubleshoot performance of the query, you need to remove the TOP 1000 and then view your query plan and see where the performance hit is...  (in this case, most likely an index that would be useful is missing)


How mature is the Firebird database when compared with PostgreSQL and MySQL? I have used PostgreSQL with small projects and MySQL is running in production environment. No issues with them. I have been hearing of Firebird which is again a solid RDBMS in the open-source area.

How reliable, ANSI-SQL compliant and stable it is when compared to PostgreSQL? How

FirebirdSQL is quite mature and reliable.

Its biggest weakness however (in my opinion) is the lack of a proper manual that covers all features of the current release. Currently if you want to know if a specific feature is available you first need to go through the Interbase manuals, than through the Firebird 1.0 release notes than the Firebird 1.5 release notes, than the Firebird 2.0 release notes and finally the Firebird 2.5 release notes (and 3.0 is more or less on the doorstep)

Btw: using a different DBMS in development than in production is usually a very bad idea. Especially if the development DB - as in your case - offers a lot more SQL features than the production DBMS.


Azure server names vs Azure database names Let's say I have one Azure subscription and 2 Azure instances:  Dev-instance.database.windows.net for developer testing, and Prod-instance.windows.net for production work.  I want to create a new database named 'Employee'on both instances.  Can I name the database the same on both instances, or do the database names have to be unique since I only have one subscription?

Your database name can be same on both instances with in the same subscription. See the picture below. I have same database in 2 different server but in the same subscription.


Oracle audit: Only 'SESSION REC' in ACTION_NAME column I was asked to generate a report of operations performed on certain Oracle 9i tables in a given period of time.

When I query the DBA_AUDIT_TRAIL table I only get SESSION REC in the ACTION_NAME columen.

I was expecting SELECT, INSERT, DELETE etc.

What am I doing wrong?
What does SESSION REC mean?

This means you set AUDIT .. BY SESSION instead of AUDIT .. BY ACCESS. When you use BY SESSION, the ACTION_NAME contains SESSION REC, and the actual actions performed by that session are stored in the SES_ACTIONS column as characters. For example the value ---------S------ means there was a select on the object, as each character represents the following actions in order:

ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE

http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch2205.htm#1307791


Find fulltext indexes in SQL Server 2008 How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database?

For example, you can enable and disable a FULLTEXT INDEX like this:

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] ENABLE

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] DISABLE

To find a list of tables that have a FULLTEXT INDEX:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS FTCatalogName ,
    f.name AS FileGroupName,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName
FROM
    sys.tables t
INNER JOIN
    sys.fulltext_indexes fi
ON
    t.[object_id] = fi.[object_id]
INNER JOIN
    sys.fulltext_index_columns ic
ON
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON
    ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN
    sys.fulltext_catalogs c
ON
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
    sys.filegroups f
ON
    fi.data_space_id = f.data_space_id
INNER JOIN
    sys.indexes i
ON
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id];

To enable and disable a FULLTEXT INDEX refer msdn

sp_fulltext_table
   [ @tabname= ] 'qualified_table_name'
   , [ @action= ] 'action'
   [
   , [ @ftcat= ] 'fulltext_catalog_name'
   , [ @keyname= ] 'unique_index_name'
   ]

The preferred method is to use ALTER FULLTEXT INDEX [myFTindex] {ENABLE/DISABLE} instead of the deprecated sp_fulltext_table.


Are VM snapshots enough to backup SQL? I have a Microsoft SQL DB running on Azure VM

I have daily snapshots of the VM and this is currently my only backup mechanism.

Is this considered best practice? Am I risking something here?

Before I tell you if you following best practice or no, I suggest you understand few terms which dictates what kind of backup plan is suitable for you. This will also enable you to decide if you are risking something or no.
Recovery models

There are three recovery models:
Full recovery model (the default and the most commonly used) All modifications in the database a fully logged The log will not clear
until a transaction log backup is performed
Bulk-logged recovery model Some modifications (like an index rebuild or a bulk load) can be minimally logged, which reduces the
amount of log records generated The log will not clear until a
transaction log backup is performed, just like the full recovery model
Simple recovery model Some modifications can be minimally logged, just like the bulk-logged recovery model The log will not clear until
a checkpoint occurs (usually automatically), and transaction log
backups are not possible

Backup types

There are three commonly used backup types in SQL Server:
Full database backup
Transaction log backup
Differential database backup

RTO stands for Recovery Time Objective.

In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how
quickly must the data be made accessible again. RTO is often talked
about in terms of the number of nines of desirable up time or
accessibility for the data/database/system. For example, 5-nines means
99.999% up-time, and if that’s being measured 24 hours a day, 365 days a year, then that translates into just over 5 minutes of allowable
downtime per year. That’s really hard to achieve. It’s easier to meet
4-nines (52.5 minutes per year of downtime) or 3-nines (8.75 hours per
year of downtime).
When discussing the desired up time, you have to decide whether it’s
being measured 24×365, or 9am-5pm during weekdays, or some other time
period. You also need to decide whether the measured downtime includes
scheduled downtime for maintenance/patching or not, as it’s much
easier to meet a high number of nines if scheduled maintenance is
permitted.

RPO stands for Recovery Point Objective.

In simple terms, you can think of RPO as a measure of how much data or work it’s
acceptable to lose. It’s relatively easy to achieve very minimal or
even zero data/work loss using backups, but depending on the amount of
damage the database suffered when the disaster hit, recovering might
take a lot of time. For instance, if an entire database is destroyed,
depending on the architecture of the database and the backups that
exist, it may take a significant amount of time to recover the
database up to the point of the disaster. Most RPOs are defined as the
the amount of time for which work may be lost. For example, an RPO
might be that the database should be recoverable to a point within
thirty minutes of the disaster occurring, which means that up to 30
minutes of work may be lost.

Once you understand the above concept then you need to make a backup strategy. Make sure your business counter-part is in agreement with your strategy, especially about how much data you can lose. Read the article I listed in reference about planning a recovery strategy.
Reference:

The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types by Paul Randal
Database Maintenance Best Practices Part III – Transaction Log Maintenance by Kimberly Tripp
The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy by Paul Randal
The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO by Paul Randal


Proper permissions for ETL user I'm working on changing the permissions on our ETL to incorporate best practices.

This is our ETL in a nutshell:

Load 100+ tables from 5 different SQL Servers into stage in DW
Call procedures on stage and ODS databases to transform the data
Log the executions to a logging database

This is scheduled via a job.

What would be the appropriate permissions for this ETLUser account? Here are my thoughts:

Change to non-admin domain account
assign db_datareader on source databases
db_datareader, db_datawriter on Stage, ODS and Logging databases.
db_executor role (custom) on Stage, ODS and Logging
db_ddladmin on Stage (for truncating)
Is a proxy account on the job required?

SQL Server 2012

Your thoughts are to the point.

The only thing I want to suggest is not to use the db_ddladmin account if you only want to truncate tables.
The minimum permission for truncating a table is ALTER, see Truncate Table. So you could make a custom database role with ALTER permissions on all tables you need to truncate. Assign that role to the ETLUser account.

A proxy account is in my opinion indeed the best way to run the package under the ETLUser account in an SQL Server agent job.


"dateformat" independent form of datetime string in query for SQL Server? I am unfamiliar with SQL Server, and would like to do some ad-hoc searches in a dockerized legacy SQL Server database using SQL Server Management Studio where one of the fields is a datetime and I would like to use the BETWEEN operator to extract for a given period.
So something like
/****** Script for SelectTopNRows command from SSMS  ******/
select card, sum(amount) as sum, count(*) as count
  FROM ... where cardused between '01-03-2021' and '03-03-2021' group by card

Now my problem is that this is interpreted as MDY dates and not DMY dates (which is what we use here).  Server is TZ'ed to Europe/Copenhagen.  My Windows 10 speaks Danish.  If I run set dateformat dmy; first it is interpreted as expected.
Is there a documented format that always interprets correctly when used in a query string regardless of server settings?
Based on earlier work with Excel this might be something like
'2021-03-01 15:36'

to get this in my local timezone.  Server and client will both run in the same timezone eventually - would this be an issue?

Use datetime strings in ISO 8601 format:
YYYY-MM-DDThh:mm:ss[.mmm]
YYYYMMDD[ hh:mm:ss[.mmm]]

To use the ISO 8601 format, you must specify each element in the
format, including the T, the colons (:), and the period (.) that are
shown in the format.
The brackets indicate that the fraction of second component is
optional. The time component is specified in the 24-hour format.
The T indicates the start of the time part of the datetime value.
The advantage in using the ISO 8601 format is that it is an
international standard with unambiguous specification. Also, this
format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.

More info


Out of memory issues on SQL Server 2012 SP3 I've been having periodic SQL Server OOM errors, one time to the point that SQL Server shut down itself and always happened during night time, when no one's using it, and no SQL Agent job running at that time:

Here is the typical error:

  08/17/2017 19:31:17,spid100,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

  08/17/2017 19:31:17,spid100,Unknown,Error: 701 Severity: 17 State: 123.

  08/17/2017 19:31:17,spid112,Unknown,Error: 18056 Severity: 20 State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing ETW notifications etc are skipped.

Here is the server info:

10GB MIN SQL server memory
21GB MAX SQL server memory
only 4 DBs on the server
their sizes are only 1 to 2 GB each
Tempdb size never grew to more than 1GB (set to auto grow to 10GB)
Indexes are all low frag, stats updated
Version:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I checked multiple items:

DBCC memorystatus
Quick stats:

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

Output:
20 GB
Page Life Expectancy                     155932

No resource governor enabled:

select pool_id, cache_memory_kb, used_memory_kb,
       out_of_memory_count,used_memgrant_kb
  from sys.dm_resource_governor_resource_pools

Output:

pool_id | cache_memory_kb | used_memory_kb | out_of_memory_count | used_memgrant_kb
--------+-----------------+----------------+---------------------+-----------------
      1 |          295368 |         641416 |                   0 |                0

select (physical_memory_in_use_kb/1024) Memory_usedby_Sqlserver_MB,
       (locked_page_allocations_kb/1024) Locked_pages_used_Sqlserver_MB,
       (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
       process_physical_memory_low,
       process_virtual_memory_low
  from sys. dm_os_process_memory

Output:

Memory_usedby_Sqlserver_MB | Locked_pages_used_Sqlserver_MB | Total_VAS_in_MB | process_physical_memory_low | process_virtual_memory_low
----------------------------------------------------------------------------------------------------------------------------------------
                     20553 |                          20393 |       134217727 |                           0 |                          0

Server is on SP3, I know there was a memory leak issue in SP1, so ruling that out.

Anyone notice anything in DBCC result that I should focus on?

Since the SQL Server is running in a VM, as indicated by the (hypervisor) indicator in the version statement, you should ensure it has a memory reservation in VMWare (or Hyper-V, etc) on the host server.

The memory reservation should typically be 100% of the memory allocated to the VM for SQL Server virtual machines that are used in a production environment.  Without a memory reservation, the host server may "steal" memory from the virtual machine via use of a "balloon driver" for use by some other VM, which is likely the cause of your out-of-memory condition.

In VMWare vCenter, to set the Memory Reservation on a Virtual Machine:

Power off the virtual machine before configuring the memory settings.
In the vSphere Client, right-click a virtual machine from the inventory and select Edit Settings.
In the Virtual Machine Properties window, select the Resources tab and select Memory.
In the Resource Allocation panel, select the Reserve all guest memory (All locked) check box.
Click OK.

If you're using Microsoft Hyper-V, disable dynamic memory for the VM, using this process in Hyper-V Manager:

Ensure the VM is turned off.
Right-Click the VM, choose "Settings"
Select "Memory" from the left-hand pane.
Ensure "Enable Dynamic Memory" is not checked.
Start the VM.


Restoring a tablespace to a different database Any ideas as to how we can re-create a tablespace on a different server?

We have a cold backup, so we can copy the datafiles to the target server.
Can we just create a new instance on the target server with the same tablespace name and the same path structure and plug-in the original datafiles?

Just a bit more background, we just want to get a table back from cold backup without restoring the whole database, so we were looking to restore the tablespace's files into a dummy database, then exporting the table were after.

Unfortunately it is an old version of Oracle - 9.2.0.8 and we don't use RMAN.
Any info would be appreciated.

Yes,

if you have all files placed in the same locations, you can create a pfile, make it point to the controlfile[s] and start the instance. If the backup was a clean cold backup, it should open without any problems. Don't forget to also enter the name parameters and the dump destination parameters for the database.

You should restore at least the system, undo and your_desired_tablespace_files in order to succeed. Create a new temporary tablespace.


How can I skip rows that I've already imported? I've previously used the SQL Server 2008 R2 "import data" wizard to import an Excel file into my database.

Now I'm trying to import more Excel sheets that may contain the same data that was previously imported.

How can I import these Excel sheets to the same database, while skipping the rows from Excel that already exist in the database?

2008 supports except.  Import the excel data into a different table--mytemptable--then

INSERT INTO mymaintable
SELECT * from mytemptable
EXCEPT
SELECT * from mymaintable;

Then you can get rid of mytemptable (or whatever you want to call it).


Consistency in ACID and CAP theorem, are they the same? From my understanding, the consistency in ACID is ensuring the data integrity.

However, the consistency in CAP means the data can appear quickly in distributed system.

Does that mean: they are not the same concept?

They are not really the same, because of the scope of the data
ACID

Atomicity
Consistency : All Applied Data Changes Provide Consistent View of Data For All DB Connections
Isolation
Durability

CAP

Consistency (All Nodes Have Same Data via Eventual Consistency)
Availability
Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system

SYNOPSIS

ACID addresses an individual node's data consistency
CAP addresses cluster-wide data consistency


What does "copy of" mean when doing "delete archivelog"? Disclaimer: I'm not a DBA nor did I fully read the RMAN docs cover to cover, so this may be well covered, but I couldn't find it.

The question is very simple, what is the difference between these two commands:

delete copy of archivelog all completed before 'SYSDATE-7';
delete archivelog all completed before 'SYSDATE-7';

What is deleted from where under which circumstances when running these from RMAN?

Possibly relevant infos: (?)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5785
Next log sequence to archive   5787
Current log sequence           5787
SQL> select * from v$recovery_file_dest;

NAME             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------- ----------- ---------- ----------------- -----
e:\oracle\backup 2.1475E+11  4.7820E+10 4.6097E+10        1246

-

RMAN> show retention policy;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ****** are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

Based on the documentation:

  An image copy is an exact copy of a single data file, archived redo
  log file, or control file. Image copies are not stored in an
  RMAN-specific format. They are identical to the results of copying a
  file with operating system commands. RMAN can use image copies during
  RMAN restore and recover operations, and you can also use image copies
  with non-RMAN restore and recovery techniques.

Even the original archivelogs count as copies, as they are a copy of an archived redo log. The 2 statements are equivalent even if you have the same archivelogs 1 or 10 times on the disk. The COMPLETED BEFORE 'SYSDATE - 7' clause will delete archivelogs that are older than 7 days.

This is not true for datafiles however. The original, actual datafiles don't count as copies. If you create a copy of a datafile with the BACKUP AS COPY command, that copy will have the data from the file the time it was copied, and the database will not use that copy, except for recovery procedures. If you switch a datafile to its copy with the SWITCH command, the copy becomes the original, and the former original becomes the copy.


Why does postgres choose to sort instead of scanning the index? I have the following table:

# \d service
       Table "public.service"
   Column    |   Type   | Modifiers
-------------+----------+-----------
 customer_id | integer  | not null
 date        | date     | not null
 service     | smallint | not null
 has         | boolean  |
Indexes:
    "service_customer_id_idx" btree (customer_id)

# select count(*) from service;
   count
-----------
 327535416
(1 row)

Time: 75047.508 ms

# select version();
                                                   version

-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.1.1 20160621 (Red Hat 6.1.1-3), 64-bit
(1 row)

I tried to come up with a query for which it is obviously beneficial to use the
index, since the results can be taken directly from the index in the correct
order:

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=64804990.92..65623829.48 rows=327535424 width=4) (actual time=487209.250..546557.064 rows=327535416 loops=1)
   Output: customer_id
   Sort Key: service.customer_id
   Sort Method: external merge  Disk: 4482448kB
   ->  Seq Scan on public.service  (cost=0.00..5045816.24 rows=327535424 width=4) (actual time=2.705..49659.173 rows=327535416 loops=1)
         Output: customer_id
 Planning time: 0.093 ms
 Execution time: 554914.731 ms
(8 rows)

Time: 554919.649 ms

As you see, Postgres prefers to do a sequential scan and then sort the results.

Interestingly, if I add a limit clause, it does decide to use the index:

# explain (analyze,verbose) select customer_id from service order by customer_id limit 10;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..32.97 rows=10 width=4) (actual time=2.509..2.773 rows=10 loops=1)
   Output: customer_id
   ->  Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=2.503..2.760 rows=10 loops=1)
         Output: customer_id
         Heap Fetches: 10
 Planning time: 4.285 ms
 Execution time: 2.906 ms
(7 rows)

Time: 28.178 ms

Why does Postgres behave this way, and how could I debug this? Is there a way to
ask it to show alternative plans and their cost calculations?

Here's the execution with seqscan turned off:

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=2.753..346400.896 rows=327535416 loops=1)
   Output: customer_id
   Heap Fetches: 327535416
 Planning time: 1.921 ms
 Execution time: 355637.985 ms
(5 rows)

Time: 355647.367 ms

Generally speaking, reading a tuple from an index is more expensive then reading directly  from the relation.
This is due to the overhead of traversing the tree as well as occasionally reading from the table (due to changes for example)

Thus, if you scan the entire table Postgres will come to the conclusion that it should scan the table.
Limiting the scan immediately changes the above as the number of tuples that need scanning is much more limited.


Questions about handling NULLs and empty strings in nvarchar and numeric fields I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I basically have to treat NULLs, empty strings and (pure) whitespace as 'blanks' and count the number of non-blank cells. The table contains a mix of numeric, bit and nvarchar columns.

Q1 In the table TABLE1, I have a column COLUMN1 nvarchar(32) with the following data distribution:

Value    RowCount
N/A      80             -- string 'N/A'
NULL     20             -- actual nulls

Why does the last of the following queries return unexpected results?

SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, but I expected 100.

Q2 I have another column COLUMN2 numeric(18, 0) filled with values with no NULLs or empty strings (but it could contain either/both). But the second of the queries below fails due to a reason I don't understand.

SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END)
FROM TABLE1             -- returns full rowcount (100), as expected.

SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             --query FAILS! (Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.)

Q3 What's an all-encompassing expression for my requirement of checking a column for NULLs, emptystrings and pure whitespace regardless of a column's datatype? If my columnname comes from a (cursorized) variable @column, what should I enclose it in and compare it to? I tried working with cast to nvarchar and using LTRIM/RTRIM, but frankly I am a bit lost at this point.

I am using SQL Server 2008. Thank you for reading this and for your assistance.

Q1

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.

Why would you expect 100 rows? You have 20 rows where the column is NULL. Your expression evaluates to:

SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)

Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'.

Q2

The error is due to implicit conversion and the order of the expressions. In the first query, you are comparing to a string first, then to NULL. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. Try this to see why it doesn't work:

SELECT CONVERT(DECIMAL(10,2), '');

Q3

In order to use the same expression on all data types, you must be able to convert them all to the same data type. So let's say I have a table:

CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2));

INSERT #foo SELECT '1', NULL;
INSERT #foo SELECT NULL, 4.5;
INSERT #foo SELECT '', 5.5;

Now compare the results of these four expressions:

SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') <> '';
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = '';

SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') <> '';
SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';


When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode? I am using this great example https://dba.stackexchange.com/a/25818/113298 from Bluefeet, to create a pivot and transform it to xml data.

Declaring the param

DECLARE @cols AS NVARCHAR(MAX),  @query  AS NVARCHAR(MAX);

Next there is a CTE with a lot of code, the endresult of the CTE is put in a temp DB (same as in the example)

SELECT
B.[StayDate] -- this is a date dd-mm-yyyy
, B.[Guid]
INTO #tempDates
FROM BaseSelection B

Generating the cols (same as the example)

SELECT @cols = STUFF((SELECT distinct ',' +QUOTENAME(convert(char(10), [StayDate] , 120))
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');

The result set is what i should expected

set @query =
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT
            [StayDate]
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')
    ) p
    '
EXEC sp_executesql  @query ;

When I try to transform it to XML my attributes are only partially converted

set @query =
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT
            [StayDate]
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')
    ) p
    for xml auto
    -- when using for XML path i will get a error
    -- FOR XML PATH(''''), ROOT(''root'')
    -- Msg 6850, Level 16, State 1, Line 3
    -- Column name '2016-12-17' contains an invalid XML identifier
    -- as required by FOR XML; '2'(0x0032) is the first character at fault.
    '
EXEC sp_executesql  @query ;

resultset

<p Guid="3C3359E3-CFE5-E511-80CA-005056A90901"
  _x0032_016-12-17="2" --> should be 2016-12-17="2"
  _x0032_016-12-18="2" --> should be 2016-12-18="2"
  _x0032_016-12-19="2" --> should be 2016-12-19="2"
/>

Have I missed something, why is only a portion of the date converted to unicode?

How can i fix this?

Attribute names in XML are not allowed to start with a number, see NameStartChar.

You have to come up with alternative names for your attributes and encode that in a separate @cols variable specifying column aliases for your dynamic pivot query.

SELECT @cols2 = STUFF((SELECT distinct ',' +
                       quotename(convert(char(10), [StayDate] , 120)) +
                       ' as '+ QUOTENAME('z'+convert(char(10), [StayDate] , 120))
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');

Result;

[2016-12-20] as [z2016-12-20],[2016-12-21] as [z2016-12-21]

When you are using for xml auto SQL Server does that for you.


What are the best practices for ETL of data from one OLTP system to another? First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

I also started a similar topic that I labelled "What are the best practices for ETL of data from one OLTP system to another Part 2?"

Use the tools that you are most comfortable with. Your situation seems simple and lasts only for a short time. If you have to keep this up and running for a long time, you might end up by using more advanced tooling, where you define rules for conversions and synchronizations to be performed for you. Doing so is easier to maintain and to document. The learning curve might be a little steep.
On the other hand, you could use this case to train yourself. There is no urgent deadline and when it gets more urgent, you probably build the home cooked version within a day.


Does using inequality () or NOT in where clause lead to SQL ignoring the index? I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.
https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-where-clause/
Is this absolutely true?
What is the best way to handle this? How to avoid using <> ?

A <> X predicate is sargable.
SQL Server can convert it to two range seeks (on < X or > X).
Unless the index is covering for the query however you may well not see this. The tipping point for a query using a non covering index and look ups is typically very low. (at most selectivity of a single digit percent).
So unless, say, 99% of the table does have value X the <> will match too many rows for that plan to be chosen.
Even in this case likely you should be considering a filtered index only containing the 1% of rows that don't match X rather than a full index including the highly unselective value.
Unless the domain of possible values is entirely fixed and quite small you don't really have a choice between using <> X or writing it as IN(all,values,that,are,not_x) - you need to choose the one with the correct semantics for all possible values.


Odd behavior with SSRS 2016 drop down parameter list I have a report that is "working" fine except for an issue.

I have four dropdown list parameters where I am getting the values from queries. When I select a value from a dropdown list, it selects the next value below what I wanted to select, and "removes" the first value as an option. For instance, one of the dropdowns holds a list of payrolls. When getting the list from a query, it returns payrolls 1 through 121(or so), which is perfect. However, if I try to select any of the payrolls, say payroll 10, it will show me that I selected payroll 11, and the option to select payroll 1 will no longer be available.

ALSO, possibly tied to the above, the report sometimes refreshes when I select "view report" and I have to try to choose the payroll again.

These are not cascading parameters. The values are all derived from two queries where I am selecting a distinct list of employees for two of the parameters and a distinct list of payrolls for the other two.

I am at a loss as to why this type of behavior is even possible.

After desperately trying everything from restarting and updating SSRS, to clicking my heels three times and throwing salt over my shoulder, the solution is to not use the Microsoft Edge browser. When I switch to using Chrome/Firefox to run my SSRS reports, the behavior is removed.

Microsoft Edge version: 38.14393.0.0


Duplicate primary key in bulk insert after truncate in SQL Server 2008? I'm using SSIS to dump data from my SQL Server 2008 prod database to flat files and then using bulk insert to load those flat files into the SQL Server 2008 dev database. The SSIS package is running a TRUNCATE TABLE statement before the bulk insert, but when I run it I still get this error: "The statement has been terminated. Violation of PRIMARY KEY constraint. Cannot insert duplicate key." Why would there be a duplicate key after a truncate? Is there something more I need to do to clear the primary keys before the bulk insert?

If you've truncated the table, then a primary key violation must be coming from duplicate data in the file. Try bulk inserting into a new table, without the PK constraint, and then check the table for duplicates (probably easier than writing some tool or script to parse the file directly). You can create a mimic table that won't have constraints this way:

SELECT * INTO dbo.new_bulk_source
  FROM dbo.old_source
  WHERE 1 = 0;

Then change your package to reference this table, do the insert, then run:

SELECT key FROM dbo.new_bulk_source
  GROUP BY key
  HAVING COUNT(*) > 1;

I bet a donut the call is coming from inside the house (or the truncate is not succeeding).


Restoring full back up taking forever I am new to DB Admin in SQL Server.
I am more of a SQL Developer.
I am trying to restore 95GB size of BAK file to SQL Server (in Azure VM).
The issue is that it is taking more than 24 hours now..
I realized that since I have multiple more BAK files to restore after this one, I have to restore the main BAK file (95GB one) with "NoRecovery".
So, I went into a folder where 95GB Bak file is located, and selected the Recovery state as "Restore with Norecovery".
Then, I just started the process since yesterday, and it is taking more than 24 hours.
What am I doing wrong?  Or is there anything else I need to do?

As far as % complete is concerned, it was completed (with 100%) after probably 2 hours from when the process was started. But it just shows as "Restoring..." after 24 hours, and I am not able to do any query or access.
Regards to other bak files, those should be like an hourly transaction log backup. Since the original data provider only provides in SQB files format, I have to convert them into BAK file using a third party app (RedGate's SQL Backup).

This is the expected behavior.
When you restore using "With no recovery", it tell SQL to leave the DB in the "recovery" state (allowing you to restore subsequent backup).
If you want to make the database available, you can run a
Restore database [dbname] with RECOVERY;

Note that running this command will bring the DB online and you will be able to run query in it but it will also make all other restore impossible.
If you need to look at the data while in the middle of the restore process, you could use the "with standby" option
Restore database [dbname] from disk =N'path to backup' with STANDBY=N'path to the standby file that will be created'

Your other option would simply be to complete your restore (your transaction log backup) and make sure that on the last one, you change the "norecovery" by "recovery"


"Cannot insert the value NULL", but there is no NULL values! This question follows the answer of my other question there:
Insert with OUTPUT correlated to sub query table

The Person.LastName column has a NOT NULL constraint. When I execute this code:

CREATE TABLE tempIDs
( PersonId INT,
  FinancialInstitutionId INT
);

MERGE INTO Person
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
  VALUES (GetDate(), 'Legal', fi.Name, fi.Street, fi.Number, fi.City, fi.[State], fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
OUTPUT inserted.Id, fi.Id INTO tempIDs;

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

I get the following error:

Cannot insert the value NULL into column 'LastName', table 'Person'; column does not allow nulls. UPDATE fails.

The thing is that there is no FinancialInstitution.Name that is NULL.

SELECT Name FROM FinancialInstitution WHERE Name = NULL

This returns no row. Beside, if I replace fi.Name with a value ('A Last Name') the request works.

NULL is not a value. Something cannot '=' NULL

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL


Command Line Creation of a new 12g Oracle Database I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase".

$ . oraenv

ORACLE_SID = [oracle] ? mynewdatabase

Is there a step-by-step example of how I can create a new database instance. Most of the examples I have found on the Internet show examples with a Windows environment and not Linux. Once I know how to create the new database environment, I can use ...

sqlplus / as sysdba

and begin working some database magic. Any assistance would be greatly appreciated!

I have not manually created a database since 7.3.4. You can use dbca in silent mode without x-windows.

Installing and Configuring Oracle Database Using Response Files


Optimize very simple Postgresql query (and understand Query Plan) I have a DB with many tables created using the following structure :

CREATE TABLE table1 (id serial primary key, col1 real, col2 real, ..., coln real)

with about 50 columns and 400k rows. The tables are read-only but are fully dropped and recreated once a day (with new values) and are hosted on a HDD.

When I run a simple query based on the PK:

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461 OR table1.id=7618 OR table1.id=45581 OR table1.id=5238;

I get the following QUERY PLAN :

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=28.290..45.165 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=16.748..16.748 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=11.230..11.230 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.832..0.832 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=4.476..4.476 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.208..0.208 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.074 ms
 Execution time: 45.197 ms
(14 rows)

If a run the exact same query again, the QUERY PLAN becomes:

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=0.031..0.036 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=0.024..0.024 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.078 ms
 Execution time: 0.074 ms
(14 rows)

The first question is why in the first query the actual time of the Beatmap Heap Scan is higher than the expected cost ?

Also, is there a configuration that would improve the query performance ?

Many thanks in advance.

PS :due to RAM limitation I can not use pg-warm tool

You need to understand that the costs provided are not absolute values. It basically has no other meaning than helping the planner comparing plans. So you can't compare cost (that has no unit) to actual time (that's given in ms). You can compare costs between two plans (provided by the same postgres server, because it will depend on configuration parameters and sometimes Postgres version).

Then (and I think you figured it out by yourself), the performance difference between first time and second time is that the second time data are cached, so getting them is way faster...

Your query runs in 45ms when data are not cached... That's pretty good and it wouldn't need farther optimization normally. Remember the first rule for optimization is

  Don't do it

But I think you simplified your problem here so we could understand it. If your query is way more slower that what you showed and does use OR, remember it's quite expensive (in time and resource). You can always rewrite your query like that :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id IN(461,7618,45581,5238);

or like that :

EXPLAIN ANALYZE
(SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=7618
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=45581
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 table1.id=5238);

Or (like a_horse_with_no_name suggested) with a join on a values clause :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 INNER JOIN (VALUES (461),(7618),(45581),(5238)) AS t(id) ON table1.id = t.id;

You might find usefull the using explain official documentation page.


How can I create postgres users for employees that have limited access? I want to create postgres users for our staging + production databases that allow the users limited access to the tables.

I want the users to have read only access to ALL tables except messages & credit_cards. I want to give them update access for the access_tokens & I want them to have read access to newly added tables. How can I do this?

Example DB:

users:

id
name

messages:

user_id FK
message
to_user_id FK

credit_cards:

user_id FK
card info...

access_tokens:

token
user_id FK
expired_date

... Other tables

I tried:

grant USAGE on schema public to james;
grant select on all sequences in schema public to james;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO james;

ALTER DEFAULT PRIVILEGES
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Update 1:

Note that running:

ALTER DEFAULT PRIVILEGES
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Will throw the following error from postgres (rds):

  ERROR:  must be member of role "james"

However running the grant commands worked. It seems I cannot alter the default privileges for the postgres rds database.

ALTER DEFAULT PRIVILEGES
      FOR ROLE james
      IN SCHEMA public
      GRANT SELECT ON TABLES TO james;

This grants select on tables created by 'james', to 'james'.  Which is generally unnecessary, as the creator will usually be owner and will automatically have access.  You need to change first occurrence of 'james' to whoever it is that will be creating the new tables.

Also, you should probably change the last occurrence of 'james' to something like 'readonly' or 'developer', and than grant that role to 'james', rather than naming 'james' explicitly.


How to extract xml attribute from a column? I have a column called target_data in the sys.dm_xe_session_targets that returns the path where the pertinent file resides in the format of xml tags.
This data is not in XML datatype. One has to cast it to the XML data type.

This is how the typical field in the column looks like:

  <EventFileTarget truncated="0">
  <Buffers logged="135" dropped="0"/>
  <File name="C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel"/>
  </EventFileTarget>

I want to retrieve the name attribute of the file tag i.e. C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel in this case.

I tried the following script for the same, however it is throwing errors:

select xe.m.value('EventFileTarget/file/@name[1]','nvarchar(500)'),*
from sys.dm_xe_session_targets as s
cross apply (select cast(target_data as xml) as m) as xe

The error reads :

  Msg 2389, Level 16, State 1, Line 1 XQuery [xe.m.value()]: 'value()'
  requires a singleton (or empty sequence), found operand of type
  'xdt:untypedAtomic *'

I'm perplexed. Please help

The XQuery expression in the value() function must return at most one value.

Your expression EventFileTarget/file/@name[1] is saying that from all EventFileTargetnodes you want all file nodes and from those nodes you want the first name attribute.

Given this sample XML:

<EventFileTarget>
  <file name = "FileName 1"/>
  <file name = "FileName 2"/>
</EventFileTarget>
<EventFileTarget>
  <file name = "FileName 3"/>
  <file name = "FileName 4"/>
</EventFileTarget>

Your expression returns all four filenames.

Change the XQuery to (EventFileTarget/file/@name)[1] to get the first name attribute with the path EventFileTarget/file.

With XML that looks like this

<EventFileTarget>
  <file/>
  <file/>
</EventFileTarget>
<EventFileTarget>
  <file/>
  <file name = "FileName 4"/>
</EventFileTarget>

The result (EventFileTarget/file/@name)[1] is FileName 4.

In your case there can probably only be one EventFileTarget node and only one file node but SQL Server does not know that so you have to build the XQuery to guarantee only one value returned. (EventFileTarget/file/@name)[1].


How to limit the number of sessions for a specific login How can we let just one program to connect to the database, in case many programs have the connection string.
(limit the number of sessions for a specific login)

If all programs are using the same credentials in the connection string, you might be able to achieve what you're after by using a Logon Trigger to limit the number of sessions for a specific login. For example, in the following code (taken from the documentation), the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;


Limit TLS and Cipher Suites on SQL Server 2016-2019 I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized).  I'm having trouble tracking down how to configure these options.

Where would I find the documentation in how to do this, and does anyone have any examples... for comparison, I'm using the following options with NginX, and could probably limit SQL Server to a subset of 256bit only.

ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384;

Looks like the same registry entries (SCHANEL) are used for IIS as well, so using the IIS Crypto application should work for both.

IIS Crypto Faq - What registry keys does IIS Crypto modify?

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\NULL\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\DES 56/56\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 40/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 56/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 40/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 56/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 64/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\Triple DES 168\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\AES 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\AES 256/256\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA256\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA384\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA512\Enabled

These are the advanced keys:

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\Diffie-Hellman
HKLM\SYSTEM\CurrentControlSet\Control\Lsa\FipsAlgorithmPolicy

To reorder the cipher suites, it modifies the registry key here:

HKLM\SOFTWARE\Policies\Microsoft\Cryptography\Configuration\SSL\00010002


how to find tables with either sparse or a column set column? from the error message below:

  A compressed index is not supported on table that contains sparse
  columns or a column set column

as I was rebuilding an index and changing the data_compression to page

This was after making good use of sparse column or compression data wonderful scripts.

Question:

what are column set columns and how to find tables with either sparse or a column set column?

Just use the sys.columns table:

SELECT name, is_sparse, is_column_set
FROM sys.columns

Column sets are:

  Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. A column set is like a calculated column in that the column set is not physically stored in the table. A column set differs from a calculated column in that the column set is directly updatable.


Troubleshooting Oracle ORA-08103 - object no longer exists I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against  it.

Since I'm truncating a different partition than the ones I'm intending to query, I expect this to work. I suspect a rogue query plan, doing PARTITION LIST (ALL) where I want (ITERATOR) or (JOIN-FILTER).

The query is complex, joining multiple partitioned tables.

So my question is: can I get any more detail out of Oracle to figure out precisely which table (or index) in the query is causing the problem? And is there any way I can capture the query plan that was in effect when the error occurred? (which could be different than the plan if I look at it right now)

How intermittently are you getting the error?

Do you have any logging in the trigger that is truncating a partition? You could use a simple procedure that writes (in an autonomous transaction) some information about which partition is being truncated.

Also create the on servererror trigger as suggested by Leigh Riffel, and log the query and error stack.

Finally, I would enable tracing of all occurrences of the long running query (assuming this query isn't executed all too often!):

alter session set sql_trace = true;
alter session set tracefile_identifier = wrschneider08103;

before the query and

alter session set sql_trace = false;

after.

Then, once the error has again reared its ugly head, run tkprof on the last trace file with 'wrschneider08103' in its name...

Hopefully you'll be able to correlate the information you've thus collected.


Postgresql requested starting point is ahead of the WAL flush position of this server I recently did a move of a VM and didn't pay close enough attention.

The VM container the postgres master. Some time passed between the backup and the move and now I'm getting stuff like this:

ERROR:  requested starting point 14C/93000000 is ahead of the WAL flush position of this server 14C/524BE2E0

The problem is going to be the fact that the slave received more WAL archives in between the time of the backup and the move.

The problem is: how do I fix it?

I've already done a new basebackup, cleaned wal archives with archivecleaner, but restarted the slave just keeps it coming back.

Sounds like slave WAL is ahead of master.

What's the solution here? I can't find it on google. Is there a way to reset this wal flush position? To tell the server to start anew?

EDIT: I ended up fixing it by deleting the slave and redeploying it, having it take the new basebackup as starting point.

I ended up deleting the slave and redeploying a new one with the "new" basebackup.

Obviously this is not ideal, but it works. It's a last resort solution. It's literally setting up that part of the environment fresh.


lock escalation issue when using DDL trigger I have a problem, I am trying to create a log table for my database so I can keep track of changes. I have created the following trigger that runs when a table is altered, created and dropped:
CREATE TRIGGER TableTrigger
ON DATABASE
FOR
    CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TableLog (
        EventDate,
        EventType,
        Existing_Table_Name,
        New_Table_Name,
        Changed_By
    )
    VALUES (
        GETDATE(),
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA(),
        EVENTDATA(),
        USER
    );
END;
GO

But for example I change a name of a column in a table the event data commandtext XML shows this
<TSQLCommand>
      <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
      <CommandText>ALTER TABLE dbo.Languages SET (LOCK_ESCALATION = TABLE)</CommandText>
</TSQLCommand>

Instead of the full command. How do I stop this from locking and letting me see the full command?

Is is preferred that you post a full repro. I tried to repro this and below code returned "ALTER TABLE" and not the text you posted.
My guess is that you use some tool to modify the table and this tool starts by changing lock escalation before doing the real stuff. Just a guess, though, since we don't have a repro.
Anyhow, here's a repro using your trigger code and just doing a SELECT of the data you return from EVENTDATA.
And, as @Mo64 mentioned, you should pick up the correct element from EventData. See the second SELECT statement in the trigger code (the first is from your code):
CREATE TRIGGER TableTrigger
ON DATABASE
FOR
    CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;

--Not the correct element:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')

--This is the one you want:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
END;
GO

CREATE TABLE t(c1 int)
GO

ALTER TABLE t ALTER COLUMN c1 tinyint

Also, your trigger code is strange. You pick up the data from EVENTDATA several times, without picking up the value that you really want (the value that is to go into existing_table_name and new_table_name). Dig into XPath a bit more to get the values you really want. Below is a snippet from a DDL trigger that captures a couple of element values, perhaps that might get you started:
DECLARE @ev xml
SET @ev = EVENTDATA()
SELECT @ev
DECLARE @PostTime datetime2 = @ev.value('(/EVENT_INSTANCE/PostTime)[1]','datetime2');
DECLARE @LoginName sysname = @ev.value('(/EVENT_INSTANCE/LoginName)[1]','sysname');
DECLARE @TSQLCommand nvarchar(max) = @ev.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');


Get minimal logging when loading data into temporary tables After reading The Data Loading Performance Guide, I am still unsure if it's necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index, in order to get minimal logging.
Obviously, the temp table is created in TempDB, which operates in the SIMPLE recovery mode, so I would have thought that it was a perfect candidate for minimal logging; however, I can not find a passage to confirm it.
Is a temporary table a candidate for minimal logging, and if so, is it worth adding the TABLOCK hint as recommended for permanent tables?

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging.

No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table (dbo.temp) created in tempdb, because these can be accessed from multiple sessions.

Note that even where the full minimal logging optimizations are not applied, tables created in tempdb benefit from other optimizations like not needing to log REDO information. You can test to see whether rows or pages are being logged using the undocumented sys.fn_dblog. Regular logging will feature row-logged records like LOP_INSERT_ROWS.

Note that adding TABLOCK to a local temporary table is required to get parallel execution with INSERT...SELECT queries in SQL Server 2016, see the Microsoft Knowledge Base article:

Poor performance when you run INSERT.. SELECT operations in SQL Server 2016


How do I stop psql from displaying executed SQL? When I execute SQL, I see the query echoed back at me.

=# \d
LOG:  statement: SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
  [... LOT OF STUFF CUT]
                   List of relations
 Schema |          Name          |   Type   |  Owner
--------+------------------------+----------+----------

All queries get echoed too,

=# SELECT * FROM ( VALUES (1) ) AS t(x);
LOG:  statement: SELECT * FROM ( VALUES (1) ) AS t(x);
 x
---
 1
(1 row)

How do I disable this feature? I've tried lots of things.

There are two methods that can cause the query to be echoed.

With psql -E (or \set ECHO_HIDDEN) the queries that psql executes with client commands \command are echoed back at you. You can see if this is set by running \set and looking for ECHO_HIDDEN. If set, you should try running without -E, not setting the option yourself, and making sure it's not set in the ~/.psqlrc. If set the query will look like this above every client command

********* QUERY **********
query
**************************

If you see LOG:  statement:, you may have awkward configurations on the server, like

client_min_messages='LOG';
log_statement='all';

See the docs on Error Reporting and Logging for more information. You should check,

SHOW client_min_messages;
SHOW log_statement;

If set, this is an indicator that someone opened up the server-configuration file and wanted more logging but didn't know what they were doing.

In your case you have LOG:  statement: so check the server settings.


Can the new “SCRAM-SHA-256” password authentication mechanism in Postgres 10 and later work with any JDBC drivers? Postgres 10 added support for a new password authentication mechanism, Salted Challenge Response Authentication Mechanism (SCRAM-SHA-256, see RFC 7677). Discussed by Michael Paquier.

➥ Can this mechanism work with JDBC drivers?

If not, why not?

If so, have any drivers yet implemented this feature?

I have not verified this myself, but the answer seems to be Yes, from version 42.2.0 of the driver from the jdbc.postgresql.org project.

To quote the changelog for Version 42.2.0:

  Support SCRAM-SHA-256 for PostgreSQL 10 in the JDBC 4.2 version (Java 8+) using the Ongres SCRAM library.

See GitHub pull request # 842.

That driver uses the SCRAM Java Implementation project by ongres.


When to cluster a table using an index? [postgresql] https://www.postgresql.org/docs/9.1/sql-cluster.html
Now suppose I have pg_repack installed so that it won't lock the table when clustering.

Should you cluster a table using an index that has a high idx_scan?

It does not make sense to cluster a unique uuid index right? Since reordering the table won't help to retrieve information easily.

Would it make sense to do a cluster on a composite unique index?

If a particular column has been joined (inner/left) with several queries, would it make sense to cluster a table related to the column's index?

What exactly we should look for when selecting a particular index to cluster a table on?

If the index that you use for clustering is on column col of table public.tab, run
SELECT correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'tab'
  AND attname = 'col';

If that value is 1.0, the order is perfect, if it is close to 0, the order has rotted. You best use trial and error to find from what value of correlation on performance gets bad.


Error while running stored Proc sp_BlitzIndex (TM) v4.2 - September 03, 2016 I am getting below error while running "sp_BlitzIndex(TM) v4.2 - September 03, 2016". My SQL server version is:

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

The error message is:

  Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1373 [Batch Start Line 0]
  ABCD database failed to process. The user does not have permission to perform this action.

  Create temp tables.

  ABCD
  Inserting data into #IndexColumns for clustered indexes and heaps
  Inserting data into #IndexColumns for nonclustered indexes
  Inserting data into #IndexSanity
  Failure populating temp tables.
  Last @dsql:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT    12 AS database_id,
                    so.object_id,
                    si.index_id,
                    si.type,
                    'ABCD' AS database_name,
                    COALESCE(sc.NAME, 'Unknown') AS [schema_name],
                    COALESCE(so.name, 'Unknown') AS [object_name],
                    COALESCE(si.name, 'Unknown') AS [index_name],
                    CASE    WHEN so.[type] = CAST('V' AS CHAR(2)) THEN 1 ELSE 0 END,
                    si.is_unique,
                    si.is_primary_key,
                    CASE when si.type = 3 THEN 1 ELSE 0 END AS is_XML,
                    CASE when si.type = 4 THEN 1 ELSE 0 END AS is_spatial,
                    CASE when si.type = 6 THEN 1 ELSE 0 END AS is_NC_columnstore,
                    CASE when si.type = 5 then 1 else 0 end as is_CX_columnstore,
                    si.is_disabled,
                    si.is_hypothetical,
                    si.is_padded,
                    si.fill_factor,
                    CASE WHEN si.filter_definition IS NOT NULL THEN si.filter_definition
                         ELSE ''
                    END AS filter_definition
                    , ISNULL(us.user_seeks, 0), ISNULL(us.user_scans, 0),
                    ISNULL(us.user_lookups, 0), ISNULL(us.user_updates, 0), us.last_user_seek, us.last_user_scan,
                    us.last_user_lookup, us.last_user_update,
                    so.create_date, so.modify_date
            FROM    [ABCD].sys.indexes AS si WITH (NOLOCK)
                    JOIN [ABCD].sys.objects AS so WITH (NOLOCK) ON si.object_id = so.object_id
                                           AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
                                           A...

I have db_owner privileges on the database. Not sure what all permission I need to run the same.

You also need VIEW SERVER STATE permission in order to run sp_BlitzIndex. This is because it is querying DMVs.


Query on char with selector with N prefix is very slow I have a table with a CHAR(36) "id" column, which is a clustered primary key. It has multiple other columns, including an "imdbId" column, which is an INT.

When I update using

UPDATE Movies SET imdbId=12 WHERE id = N'012e1892-872e-4bb0-804b-f70ff59689ae';

the query takes forever. And I don't really understand why. Can't SQL Server convert the Unicode string (as it does not contain special characters) and then use the index?

If I remove the N prefix, the query returns instantly.

The problem is, of course, that an existing application does all of its queries with the N prefix. Is converting my "id" column to NCHAR my best option?

It's bad enough that they are using a GUID as a Clustered PK, but they had to make it worse by making it a string and then doing a case-insensitive, locale-aware comparison? Amazing!

Well, if you see that huge of a difference between passing in a string without the N and with the N, then that indicates that the Collation of the silly CHAR(36) column is a SQL Server Collation (one starting with SQL_), most likely SQL_Latin1_General_CP1_CI_AS (the default for US installs).

But you are in luck. There are three options that should help greatly, given that you cannot change the queries themselves:

Ideally: Change the datatype of the id column to be UNIQUEIDENTIFIER: this will be faster not only because it will convert the N'xxx-xx-xx... value into a GUID first and then do a binary comparison (due to Data Type Precedence), but each row will be something like 22 bytes smaller (36 bytes for VARCHAR(36) down to 16 bytes for UNIQUEIDENTIFIER, minus another 2 or so for getting rid of a variable-length column).
If you can't do #1: Change the Collation of the id column to a Windows Collation, and preferably a binary one (ending in _BIN2), such as Latin1_General_100_BIN2. This won't save any space, but won't be nearly as expensive to convert the incoming NVARCHAR literal to CHAR(36) (or vice-versa), AND will be a binary comparison (much faster than case-insensitive, locale-aware) since it is a GUID and not subject to linguistic rules.
If you can't even do #2: Change the Collation of the id column to a Windows Collation that isn't a binary one, most likely Latin1_General_100_CI_AS. This doesn't save space or even do a binary comparison, but does allow for sloppy coding / ad-hoc queries with mixed-case values AND is still much faster than the conversion used by the SQL Server Collation.

You can see this behavior yourself via the following test:

SET NOCOUNT ON;

CREATE TABLE dbo.GuidPkAsUI (
  ID UNIQUEIDENTIFIER NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsUI_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCci (
  ID CHAR(36) COLLATE Latin1_General_100_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARci] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARci_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCbin (
  ID CHAR(36) COLLATE Latin1_General_100_BIN2 NOT NULL CONSTRAINT [PK_GuidPkAsCHARbin] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARbin_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCsql (
  ID CHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARsql] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARsql_InsertTime] DEFAULT (GETDATE())
);

INSERT INTO dbo.GuidPkAsUI ([ID])
    SELECT NEWID()
    FROM   master.sys.objects
    CROSS APPLY master.sys.all_columns;

INSERT INTO dbo.GuidPkAsVCci ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCbin ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCsql ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

SELECT * FROM dbo.GuidPkAsUI;
-- Pick a value from half-way down and paste into
-- the 4 queries below

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsUI
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCci
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCbin
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCsql
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 7157 (yikes!)


If the users need INSERT/UPDATE/DELETE permissions, is Windows auth still more secure than SQL Server auth? Some background first:

The problem described below wouldn't exist at all if the database in question would have been built with a DBA's mindset:
Data access only through views and stored procs --> the user is able to use the application without having any permissions on the table.

But the database in question was built with a "developer's mindset", so the app sends INSERT, UPDATE and DELETE queries to the server --> it needs an account with sufficient permissions to be able to access the database.

Apparently everyone agrees that Windows authentication is more secure than SQL Server authentication, for example here:

Secure way to connect: Windows Authentication or SQL Server Authentication and why?
Does the PCI standard forbid the use of Windows Authentication?
Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

My question:
Is this still valid when the database was built with a developer's mindset, like described above?  (when the users needINSERT/UPDATE/DELETE permissions on the tables)

Why I'm asking this:
Our main app is an MS Access frontend with a SQL Server database. At the moment, we are using Windows authentication, but I'm considering switching to SQL authentication.

The problem I'm seeing:
Everyone connects to the database with his own Windows account.
So in order for the application to work, the Windows accounts of everyone and their dog need to have INSERT/UPDATE/DELETE permissions on the tables.

This means that literally EVERYBODY here is able to just create a new Access database, link a few tables from our main database and edit (or delete) them.
And yes, we have a few power users who really know how to do this.

From that viewpoint, it's hard for me to understand why this security threat is apparently not considered when recommending Windows authentication over SQL authentication.

To me, the possibility that anyone can just edit or delete tables with his Windows account screams insecurity.
On the other hand, the hypothetical danger that someone outside the IT department could get a clear-text SQL Server auth password from a config file is quite unlikely.
(given that there are no clear-text config files lying around on the users' machines - which is the case at our company)

As I said in the beginning - I know that this problem only exists because our app is written in a way so that the users need INSERT/UPDATE/DELETE permissions on the tables.
But then, there are a lot of databases like this out there...and I still see only recommendations that Windows auth is always more secure than SQL Server auth.
IMO, the answer should be (as always) "it depends", because you can't really tell without knowing more about the database in question.

So, does no one else see this problem or am I missing something?
Or is there a way to use Windows auth without all users being able to delete data?
(besides changing all data access to stored procs)

In my view Windows Authentication is still more secure.  Just.

With windows authentication you can allow a group of people (via a Windows Group) to have access to the database.  Only those users can use an application, any application, to connect to the database.

If you use a SQL Server account then anyone who knows the credentials (and invariably these always leak out) can connect to your database.  Again with any application.

You are absolutely correct that with Windows authentication can connect directly to the database and issue any query they see fit - due to the model your app requires.

A third option

What I would suggest in this situation is the use of an application role: http://msdn.microsoft.com/en-us/library/ms190998.aspx

The problem you have here is you have to be able to code the application to be able to use the application role - that's not always easy.  Afraid I'm not an Access expert to be able to advise here specifically.

The advantage of an application role is that you can restrict which applications can perform certain commands.  Its similar to a database role but has a password assigned to it that is needed before the rights of the role can be inherited.  Using an application role with a Windows group you could limit the users who can connect to a database and the application that they can use to query the database.


Disable console output when executing a SQL file using MySQL source command I begin by launching the MySQL command line tool:

mysql --user=myusername --password=mypassword --silent --force -b

The last -b option is used to disable beep on error.

Then I choose a database:

use Mydatabasename;

Then I execute SQL form a file like this:

source c:\x\y\z\myfile.sql

That's when things go slowly. I've executed part of the file before so the console is filled with duplicate row errors which slow execution badly. I get 5-10 statements executed per second. Without duplicate rows the code executes tens of thousands of statements (30k+) every 5 seconds.

I need to do this since the file is large and I can't execute it in one go.

Here is the problem. The OS has two modes to print things

stdout
stderr

The --silent  only affects stdout. How do you nail stderr?

Try one of the following, and see if it works:

mysql --user=myusername --password=mypassword --silent --force -b 2> nul
mysql --user=myusername --password=mypassword --silent --force -b --tee=nul

Give it a Try !!!

CAVEAT : I have dealt with something this before when answering a question about mysqldump : How to log verbose output from mysqldump?


SQL Server 2008 R2 - Specify the Full Backup to use when doing a Differential Backup When I backup my Database, I do a Full Backup every day who goes on a tape.
I am trying to use the Sunday's Full backup to set up an additionnal Differential Backup, how can I do this? It only takes the last Full Backup.

Full (Run on Sunday) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Partial (Run each Day, but use the wrong Full Backup) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Partial_20120105.bak' WITH  DIFFERENTIAL ,  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'DATABASE-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

You should do the daily backups as copy_only, so that it doesn't break the chain for transaction log backups or differential backups. After that the daily full backup won't be taken into account as basis for the differential backups.

Quote from MSDN:

"COPY_ONLY
    Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base."


Unicode error with \u0000 on COPY of large JSON file into Postgres I'm using Postgres 9.4 on Ubuntu 14.04.

I have a 30GB JSON file that I'm trying to COPY into Postgres.

But I keep getting the following error:

COPY <table>(comment_jsonb) FROM '<json file>' WITH (format csv, quote e'\x01', delimiter e'\x02', escape e'\x01');
ERROR:  unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.

I tried searching for the offending Unicode string to remove it, but because it's \u0000, which is NULL, I can't seem to get a regex to work. And when I print it into console, I have no idea where the NULL is, because (at least my guess is) it seems to print as nothing (though I have no idea what it is actually doing).

Is there a way to skip these errors?

Alternatively, how could I replace any instances of that Unicode in my JSON file?

In a (perhaps) similar case I had been confronted with \u0000 in a string I needed to process as JSON. The follow replace worked for me:

regexp_replace(stringWithNull, '\\u0000', '', 'g')

Hope this helps.