Design Pattern - One of Many Parent Tables I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables.  I've seen two solutions for the problem, but neither is personally satisfying.  I'm curious what other patterns you've seen out there?  Is there a better way to do it?

A Contrived Example
Let's say my system has Alerts.  Alerts can be received for a variety of objects -- Customers,  News, and Products.  A given alert can be for one-and-only one item.  For whatever reason  Customers, Articles and Products are fast moving (or localized) so the necessary text/data cannot be pulled into Alerts upon creation of an Alert.  Given this setup I've seen two solutions.

Note: Below DDL is for SQL Server but my question should be applicable to any DBMS.

Solution 1 -- Multiple Nullable FKeys

In this solution the table that links to one-of-many tables has multiple FK Columns (for brevity's sake the below DDL doesn't show FK creation).  THE GOOD - In this solution it's nice that I have foreign keys.  The null-optinality of the FK's makes this convenient and relatively easy to add accurate data. THE BAD Querying isn't great because it requires N LEFT JOINS or N UNION statements to get the associated data.  In SQL Server, specifically the LEFT JOINS preclude creating an indexed view.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    ProductID   int null,
    NewsID      int null,
    CustomerID  int null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

ALTER TABLE Alert WITH CHECK ADD CONSTRAINT CK_OnlyOneFKAllowed
CHECK (
    (ProductID is not null AND NewsID is     null and CustomerID is     null) OR
    (ProductID is     null AND NewsID is not null and CustomerID is     null) OR
    (ProductID is     null AND NewsID is     null and CustomerID is not null)
)

Solution 2 -- One FK in each Parent Table
In this solution each 'parent' table has an FK to the Alert table.  It makes it easy to retrieve alerts associated with a parent.  On the down side, there is no real chain from the Alert to who references.  Further, the data model allows for orphaned Alert's -- where an alert is not associated with a Product, News, or Customer.  Again, multiple LEFT JOINs to figure out the association.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

Is this just life in a relation database?  Are there alternate solutions you've found more satisfying?

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

performance on insertions and updatings
complexity on querying
storage space

So, choose that comfier to you.


MYSQL Delete event is not running I have below database tables,
Tables Definition
A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

and created below event to delete data older than 2 months
DELIMITER //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00'
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;

and I checked : SHOW PROCESSLIST
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
| Id    | User            | Host      | db        | Command | Time  | State                       | Info             |
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
|     5 | event_scheduler | localhost | NULL      | Daemon  | 61763 | Waiting for next activation | NULL             |
| 34948 | root            | localhost | baridi_DB | Sleep   |   984 |                             | NULL             |
| 34949 | root            | localhost | baridi_DB | Query   |     0 | starting                    | SHOW PROCESSLIST |
+-------+-----------------+-----------+-----------+---------+-------+---------------------------

But the events is not deleting any thing that matches the conditions, what Im missing here?
Any help will be much appreciated

You have to make the event stay put using ON COMPLETION PRESERVE
DELIMITER //
DROP EVENT IF EXISTS daily_delete //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)
ON COMPLETION PRESERVE
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;

See my examples in the following posts

Jun 08, 2015 : Use event schedule and stored procedure to automatically average every hour MySQL
Jun 08, 2015 : Choosing what table fields to partition on
Sep 19, 2014 : Creating Event that execute at end of each month
Aug 28, 2014 : Adding time to an event that runs on a weekly basis

This will kickoff the event starting midnight tomorrow
You never start an event in the past. You must always start an event in the future.


Simplify SQL Query which aggregates per year This query works, but uses TO_CHAR(date_created, 'YYYY') three times.

Is there a way to simplify this query?

SELECT TO_CHAR(date_created, 'YYYY'), count(*)
FROM ticket
WHERE date_created >= '2013-01-01' and
      date_created <= '2017-06-01'
GROUP BY TO_CHAR(date_created, 'YYYY')
ORDER BY TO_CHAR(date_created, 'YYYY');

Result:

 to_char | count
---------+--------
 2013    | 441500
 2014    | 574660
 2015    | 247910
 2016    | 601234

I want to simplify it. Performance does not matter at all (in this case). Unfortunately I found no matching tag for this question. I took "optimization".

I use PostgreSQL

A CTE can be used in this case:

WITH yourdata as (
    SELECT TO_CHAR(date_created, 'YYYY') AS tc_date_created
    FROM ticket
    WHERE date_created >= '2013-01-01' AND
          date_created <= '2017-06-01'
)
SELECT tc_date_created, count(*)
FROM yourdata
GROUP BY tc_date_created
ORDER BY tc_date_created;


How to generate PostgreSQL COPY bin files? My Googlefu is failing me. Are there any code snippets, either in Python or FORTRAN, that shows how to create binary files to be used by the COPY command in PostgreSQL?

Your best bet really is to create CSV files and then import them with COPY.  If you want to, you can then wrap them simply in a COPY ... WITH CSV FROM STDIN statement.

The existing docs on the COPY command should be sufficient.  See http://www.postgresql.org/docs/9.1/static/sql-copy.html


Adding a new data set in SSRS with nested stored procedures I have spOne which builds a couple of global temp tables with over 500 lines of code. Normally it would return a single result set but I wanted to leverage the code for building the temp tables without duplicating it. So, I added a @showResults param and if it equals 0 then it will build the temp tables but not execute a SELECT statement against them.

Now I have spTwo which calls spOne in the beginning with @showResults = 0 and then continues the work it has to do before returning a single result set.

In SSRS, I am trying to add spTwo as a data set but it is auto-detecting spOne's result set even though it would not be executed given the param input.

How do I add spTwo as an SSRS data set?

Updated:

A representation of the stored procedures can be found here.

I was able to add the data set by putting it into the XML manually, the GUI refused to save the change.


Microsoft SQL Server Not Showing Server Name in object Explorer I Recently Installed Microsoft SQL Server 2014 (SSMS) on my local machine for development learning purposes. I am running Windows 10 32 bit. When i try to connect, Server Name does not appear. Screen Shot is attached.

According to my research, name of the local server should appear in the marked field.
I think that Installation is successful.

I am unable to find the solution to this problem. I installed SSMS 2015 RC0, Problem was not solved. Then i uninstalled it and Installed SSMS 2014. But the problem is not solved.
I remember perfectly that about a  year ago. I installed SSMS and there was no such problem. I can't figure out Why the server name is not appearing. When i try to connect by manually entering Local server name(./), the following error occurs

Any help would be appreciated.

It looks like you have only installed SQL Server Management Studio (SSMS), and not SQL Server.

SSMS is the tool used to connect to and manage SQL Server Instances, if you haven't installed a SQL Server instance you have nothing to run your queries against!

Press Windows Key + R (Run) and type services.msc, check to see if the 'SQL Server' appears and that it is running.


Issues deploying SSIS package to SQL Server in Azure AD Domain Services (AADDS) environment We use Azure Active Directory (Azure AD) in my organisation, and we recently deployed Azure AD Domain Services AADDS) to handle a SQL Server 2019 we use internally.
My Azure AD domain is (fictitiously) contoso.com, while my AADDS is aaddscontoso.com. Microsoft strongly recommends that users do not create their managed domain (the AADDS deployment) with the same domain name as their Azure deployment. They also strongly recommend against using something like contoso.local. So of I went to create our AADDS as aaddscontoso.com and everything seems to be working perfectly.
The Azure AD works with AADDS, (at least with the Vanilla way we have set-up) is:

All of all laptops, desktops, etc are joined to the Azure AD through the Azure AD join (the cloud method)
We can access the managed domain and its hots (including the SQL server) in our Azure Network via Azure VPN. For instance, right now I am VPN into our Azure network and I can remote desktop to the SQL Server.

Here is where the problem starts. Today I just noticed that trying to deploy an SSIS package from Visual Studio from my laptop (whilst connected on VPN) won't work.
I tried using Windows Authentication and the authentication fails with the following error:

The target principal name is incorrect.  Cannot generate SSPI context.
(Microsoft SQL Server, Error: 0)

So I tried using the Active Directory Password Authentication option, then I got the following error:

A connection was successfully established with the server, but then an
error occurred during the login process. (provider: SSL Provider,
error: 0 - The certificate chain was issued by an authority that is
not trusted.) (Microsoft SQL Server, Error: -2146893019)

Then I tried to be clever and create a SQL Login with the right administrative rights and the connection worked. However it fails on the deployment of the package, saying that you cannot use a SQL account for this. At least this proves that the connection to the server is not the issue.
I THINK that this problem could be simplified as follows: I want to deploy an SSIS package from the PC in DOMAIN A to a SQL server found in DOMAIN B. How can I do that? (Granted that this is not really it, but SSIS seems to think it is).

My solution was to create a shortcut to the following command:
C:\Windows\System32\runas.exe /netonly /user:targetuser@aaddscontoso.com "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\devenv.exe"

Replace targetuser@aaddscontoso.com with the user name of your Azure Managed Domain (AADDS).
Make sure you are pointing to the path of the correct Visual Studio shell. In my case I have SSDT for Integration Services install on the shell of Visual Studio 2019 Enterprise.

If you want this on your start menu, you can open the start menu location on Windows Explorer at: C:\Users\YourLocalHostUser\AppData\Roaming\Microsoft\Windows\Start Menu\Programs
I created a shortcut just like this:

I might even create my own icon (some sort of mesh between VS icon and the Azure AADDS icon).
Now, the only caveat (not really) is that you must provide the password of the the target credentials at a command prompt upon launching the shortcut. I think it is a nice compromise, to be honest.
Alternatively, you could download Sysinternals ShellRunAs to have a nicer GUI at the start, and use it instead of the runas.exe. But not only would you still need to provide the password upon launching the shortcut, but the username as well!


How to use WITH RECURSIVE with values in an array? I'm sure I'm missing something simple here, but I'm trying to use PostgreSQL's WITH RECURSIVE to create a graph of children records.

The data (simplified) looks like this:

CREATE TABLE offspring (parent, children)
AS VALUES
  ( 'aaaaaaaa-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ bbbbbbbb-8865-4b40-a482-78fc8a27d1bb, cccccccc-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'bbbbbbbb-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ dddddddd-8865-4b40-a482-78fc8a27d1bb, eeeeeeee-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'cccccccc-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ ffffffff-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'dddddddd-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 11111111-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'eeeeeeee-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 22222222-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'ffffffff-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
  ( '11111111-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
  ( '22222222-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] );

I've taken several stabs at it that I thought would work, but nothing has proven successful.

This simplified view reflects just the fields necessary to do the recursion, but when done, I'll also need to check timestamps on each record to filter appropriately, but I'm guessing I can handle that once the basic recursive query is in place.

My goal here is to end up with a single CTE that maps a single parent UUID to a UUID array of all of its children.  Neither depth nor order are important in the resulting array.

Any help is appreciated.

Evidently, the answer required me to step away from it for a little bit.

Here's the query that solved my problem:

WITH RECURSIVE descendents AS (
  SELECT parent,unnest(children) AS child
  FROM offspring o
  WHERE parent='aaaaaaaa-8865-4b40-a482-78fc8a27d1bb'
    UNION ALL
  SELECT o.parent,unnest(o.children) AS child
  FROM offspring o, descendents d
  WHERE o.parent = d.child)
SELECT distinct(child) FROM descendents;

SQL Fiddle


Buffer Pool and Multiple instances of SQL Server If you have multiple instances of SQL Server running on the same windows server system,

a) Does buffer pool is shared amount multiple instances?

b) What is the effect of running DBCC DROPCLEANBUFFER OR DBCC FREEPROCCACHE? Would that only affect the SQL Server instance you ran the command on or all the instance(s) on the windows box would flush SQL server cache?

The buffer pool in SQL Server isn't shared across intances.

DBCC DROPCLEANBUFFER removes all "clean" pages from the buffer pool, "clean" pages are pages which haven't been modified since being read into the buffer pool.

DBCC FREEPROCCACHE removes all cached execution plans from the plan cache.


SQL Data type for "Reference" fields in table - SQL Server I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit welcome).

I will draw a brief overview of the fields functions to try and illustrate requirement clearer. The fields with the asterisks I am calling "reference" fields, there are many more event types but just keeping example brief and hopefully clear. Table-Events should be like a junction table. I am trying to change my ERD (of which this is a small section) so that my events aren't hardcoded and I reduce redundancy and if in future new EventTypes are needed I can just update EventTypes to add them.

Edit: Each Column here represents a table with the first Table- representing the table name the second field the GUID field etc.

Table-Player     Table-Events      Table-EventTypes
PlayerID         EventsID          EventTypeID
firstName        Minute            Try*
secondName       Fk_EventType      Conversion*
Fk_team          Fk_Player         Penalty*

As such though Try/Conversion/Penalty will never have any data it will just be selected in the Events Table with Fk_Player and minute. So what data type should I use.

Here is what I would do based on your requirements:

CREATE DATABASE [demo];
GO

USE [demo];

CREATE TABLE [dbo].[Player](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NULL,
    [secondName] [varchar](50) NULL,
    [fk_team] [int] NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([playerID] ASC)
 );
GO

CREATE TABLE [dbo].[EventTypes](
    [EventTypeID] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NULL,
 CONSTRAINT [PK_EventTypes] PRIMARY KEY CLUSTERED ([EventTypeID] ASC)
 );

GO

CREATE TABLE [dbo].[Events](
    [eventsID] [int] IDENTITY(1,1) NOT NULL,
    [Minute] [varchar](50) NULL,
    [fk_EventType] [int] NULL,
    [fk_Player] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ( [eventsID] ASC)
 );
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD
CONSTRAINT [FK_Events_EventTypes] FOREIGN KEY([fk_EventType])
REFERENCES [dbo].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_EventTypes]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD
CONSTRAINT [FK_Events_Player] FOREIGN KEY([fk_Player])
REFERENCES [dbo].[Player] ([playerID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_Player]
GO

INSERT INTO [dbo].[EventTypes] ([EventType])
      VALUES('Try'), ('Conversion'), ('Penalty');
GO
select * from EventTypes;

This should be what you need.   Have a great day!


postgresql pgadmin3 queryconnect attribute does not work I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click.

Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the server), (connect to the database), (open a query window), (open a previous file) then FINALLY I can run my SQL statement.  I want this all to be done with one click.

pgadmin3 appears to have features to do do all this work for you on the commandline:

el@defiant ~ $ pgadmin3 --help
Usage: pgadmin3 [-h] [-s <str>] [-S] [-Sc <str>] [-q] [-qc <str>] [-f <str>] [-cm <str>] [-ch <str>] [-cp <str>] [-c <str>] [-t]
  -h, --help                            show this help message
  -s, --server=<str>                    auto-connect to specified server
  -S, --serverstatus                    open server status window
  -Sc, --serverstatusconnect=<str>      connect server status window to database
  -q, --query                           open query tool
  -qc, --queryconnect=<str>             connect query tool to database
  -f, --file=<str>                      file to load into the query tool in -q or -qc mode
  -cm:<str>                             edit main configuration file
  -ch:<str>                             edit HBA configuration file
  -cp:<str>                             edit pgpass configuration file
  -c:<str>                              edit configuration files in cluster directory
  -t                                    dialog translation test mode

But I can't get it to work, I can either connect to the database or open the file I want.  It doesn't seem to allow me to simultaneously connect to a server, connect to a specific database, open a query window, and open a previous sql file.

What are the proper commands to connect to server, database and open a query editor and load a file into it?  I've tried a hundred variations, but pgadmin3 either just does not do anything and does not give an error message, or it does only part of what I told it to do with no error message.

This is the command I'm using, it does open my file, but it connects to the wrong database, and I have to manually change it.  The command line option to choose the database does not work.

pgadmin3 --file=/home/el/Desktop/sqlfile_for_postgresql.sql --query --server=defiant

I found the answer to my problem.  The pgadmin3 tool won't let you specify both a server and a database.  The server itself HAS a default database and when you select it with the --server option, it won't let you pick a database.  An error message would have been nice!

The 'defiant' server I made has in itself a default connection to a database, you have to set that default database, and you can't use commandline options to change it.

First, you must be disconnected from the default connection to the server you want to edit.  Either make a new connection to a server or edit your existing one by right clicking your server, mine is: defiant(localhost:5432).

Then right click it, go to properties, you'll see a field called `maintenance DB', make that the connection to the database you want to be the default for this server connection.  I set mine to kurz_prod.  Click OK and restart.

Then the following command works.  It opens pgadmin3, it gets a connection to the server and database that I specify, it opens a query window, it loads a previous file I specify into it.  And immediately I can run statements against it.

This command is correct in opening an sql file with a connection to a server and database:

pgadmin3 --file=/home/el/Desktop/sqlfile_for_postgresql.sql --query --server=defiant


What type of rows does `SELECT my_table FROM my_table` return? I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges):

SELECT my_table
FROM my_table;

It seems to turn a one-column result, with multiple rows.

What would be the name of the type of each row returned? A row value of my_table?

(I tried reading the documentation (sql-expressions) but, I could not find a specific answer.)

It's a named composite type: https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

When you create a table, a TYPE with the same name is created.

A row of that table is of that type.


SQL Server 2017 password complexity rule SQL and Windows version: Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

Servers are in Workgroup.

Our Password Complexity Parameters for SQL login:

has at least one digit
has at least one uppercase character
has at least one special character
has at least one lowercase character

We are able set initial password for new SQL login as a TestingDB@001 and testingdb@001. But new initial password should be TestingDB@001 NOT testingdb@001 as per above Complexity Parameters. Is this expected behaviour of SQL 2017 for SQL login password?

We have tried to create OS user (non admin windows account), OS level also its taking both passwords (TestingDB@001 and testingdb@001). As per above Complexity Parameters, it should accept only TestingDB@001 password. Is the above SQL login password issue is due to OS?

Please suggest how to achieve above Complexity Parameters in password creation.

We have set below policy in local computer policy on DB server.

Is this expected behaviour of SQL 2017 for SQL login password?

Yes, it is expected. You might have overlooked the three of the following four categories part of the Password Complexity doc:

  The password contains characters from three of the following four    categories:

  Latin uppercase letters (A through Z)
  Latin lowercase letters (a through z)
  Base 10 digits (0 through 9)
  Non-alphanumeric characters such as: exclamation point (!), dollar    sign ($), number sign (#), or percent (%).

Therefore the password TestingDB@001 has characters from the four categories while testingdb@001 has characters from three (which is the minimum acceptable to pass), so both passwords are in accordance with the policy.

As for the Windows login it has a policy Reference that is quite like the one from SQL Server and also says The password contains characters from three of the following categories.


mysql not using index w/ group_concat I've got a simple temp table that I'm creating below

CREATE  TEMPORARY TABLE IF NOT EXISTS Helper0910912001524518044820 (
    usergroupID INT,
    StoreID INT ,
    INDEX st (`usergroupID`)
    ) engine=memory AS
SELECT  usergroup0.*
    from
    (
        SELECT  156825 as usergroupID,
                MVStoreID as StoreID,
                1 as assignmentCount
            FROM  MVMasterEntity_AttachedStores
    ) usergroup0 ;

/* queryTime - 0.5284 seconds */

I then query against it with the following query....

Select usergroupID, group_concat(StoreID) as Inspects
    from Helper0910912001524518044820
    group by usergroupID;

but am not having any luck with getting this query to use indexes.  I've tried the following indexes.

( StoreID ) , ( StoreID, UsergroupID ), ( UsergroupID ), ( UsergroupID, StoreID )

The explain that I get is always the same.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  AssHelper0910912001524518044820 ALL                 903452  Using filesort

Any help explaining why I can't use indexes for this query would be greatly appreciated.

No index is useful.  There is only one value of usergroupID (as you have written the query), so there is no use for it to be indexed.

So, the Optimizer decides to simply read the data without using any index.

One thing you can do is to get rid of the extra layer of SELECTs.

Another thing is to get rid of assignmentCount, since it is not used.

To achieve the same result set, simply do (without a tmp table):

SELECT  156825 AS usergroupID,
        GROUP_CONCAT(MVStoreID) as Inspects
    FROM  MVMasterEntity_AttachedStores

With this formulation, INDEX(MVStoreID) would probably help.

(I suspect you watered down the actual code too much, and I have not answered your real question.)


Best practice for other Table Data in an Order At the moment I try to create a good Database structure for storing all Data belonging to an Order/Inquiry.

I use MySQL and Python at the moment and for now it isn't an real DB, just learning and testing!

The Order contains Information from the following Tables of my DB:

Customer
Address
Person
Product --> Copied to an Order Product

My questions are:

With the Order Product i copy all Product Data to an new Table and "attach" it to the OrderID, that was my best guess to make the Order Products independent from changes to the product later on. Something like a new Price for the Product which should not affect old orders to be changed. Is that a good way to achieve this?
I think i have the same problem with Addresses, Persons and also the Customer it self. If a customer changes its address i dont want to transfer that change to old orders. What is the best way to store such data independent or is there any other way of doing this?
We have Idicators for our IDs to make it easier to recognize them. An ID for an Address Looks like this "AD0000001" or for an Contact Person "CP0000001" i noticed that i have to seperate the number and the Characters to do auto_increment in my DB. Is it a good practice to save the indicating Characters as an Default Value to my Table? And put them together in my Select Queries?

If i have missed some information or anyhing else, let me know! I will add it to my post!

It would be helpful to see your actual SQL scripts; an English description can be ambiguous.  It sounds like you have all the fields from your Address, Customer, and Product tables in your Orders table, to capture a snapshot of the data as it was when the order was placed.  Is that right?  If so, that's a valid approach, but it will take up a lot of space.  Space is cheap, but performance is not: keep your tables lean.

First, do you not separate Orders and OrderDetails?  Or is that what OrderProduct is meant to do?  You should only need customer and address information in the header table.  Your detail table would then typically be at a level of specific line items, with quantities and prices.  In some cases, a logical order might be shipped to multiple locations, or at separate times, and you'd need Address, DateShipped, DateReceived, DateReturned, etc., at the line item level.

Second, changing values for customers and addresses.  It's good that you're giving this some thought.  The topic you need to read up on is "slowly-changing dimensions:" today, customer #123, Mr. Smith, lives on 101 Main Street, next year she's Ms. Smith and she lives on 1st Avenue, but she's still customer #123.  There are many approaches to handling this problem.  Typically, rather than recording this information in the Orders table, you'd record each version of the customer as a record in the Customers table, with a persistent customer number (123) and a version number (1 for Main Street, 2 for 1st Avenue), something like this:

 CustomerSK CustomerID VersionID FirstName LastName Address DateEffective DateExpired IsCurrent
 ---------- ---------- --------- --------- -------- ------- ------------- ------------ ---------
 42942      123        1         Robert    Smith    10001   2018-12-07    2019-12-22  0
 42943      123        2         Roberta   Smith    10002   2019-12-23    NULL        1

 OrderID CustomerSK DatePlaced
 ------- ---------- ----------
 103101  42942      2018-07-19
 103102  42943      2019-12-24

You may have a calculated field which concatenates customer ID and version ID to make 123001 and 123002, or create a new auto-increment field for the table, with no relation to the customer IDs.  This is known as a "synthetic key", and is labeled CustomerSK in the sample above.

Your Orders table would either have two fields, CustomerID and VersionID, or a single CustomerSK field.  You use this field, or pair of fields, to join to the Customers table and get the name and address as of when the order was placed.  You could join to Customers again, on C1.CustomerID = C2.CustomerID AND C2.IsCurrent = 1, to get the customer's name and address right now.

IsCurrent can be a calculated field on DateExpires IS NULL.  Alternatively, you may prefer a far future value like 2100-12-31 for customer records which have not yet expired.  Be sure to have a unique constraint on { CustomerID, VersionID } and on { CustomerID } WHERE IsCurrent = 1.

In a real example, you'd have multiple addresses for each customer.  The same principle holds: when an address changes, record the new value, mark the old value as expired, and mark the new value as current.

Third, concatenating a text prefix to a numeric code is not going to be ideal for IDs.  You should distinguish between people-friendly IDs and database IDs.  The former may be alphanumeric, to help people recognize them, but data integrity and joins should be based on the latter, for best performance.  It's easy enough to display a prefix before the numeric ID, if necessary, in GUIs.  Consider dates: you always store them using a DATE data type, never as a number or text, but you can format them however the user likes when displaying data.

I'm not sure what distinction you're drawing between customers and people.  Pedantically, it may seem strange to put prospects in a Customers table if they've never actually ordered anything, so you may prefer to call it People or Contacts, but what information would you store for a customer that you wouldn't for anyone else?  LastOrderID or DateLastOrder, perhaps?  Those may be sparse columns in a table which includes prospective customers, but NULLs don't take up much space.


Looking for industry standard and/or tool to represent and export RDBMS metadata Do you know of an industry standard that is designed to describe the metadata contents of a relational database (or part of it, such as tables, columns, etc.)?

Do you know if a tool exists that does export the metadata of any of the top 5 RDBMS tools in any format such as XML (without having to type in DDL in the syntax of particular database)?

Thank you.

If you want a set of views that are "common" across database vendors (Oracle, MySQL, PostgreSQL, ...), there are the INFORMATION_SCHEMA views.

They are supposed to be a common standard across vendors (exactly how true I don't know). In SQL Server (versions >= 2000) you access them via [INFORMATION_SCHEMA].{view_name}, where {view_name} is any one of:

CHECK_CONSTRAINTS,
COLUMN_DOMAIN_USAGE,
COLUMN_PRIVILEGES,
COLUMNS,
CONSTRAINT_COLUMN_USAGE,
CONSTRAINT_TABLE_USAGE,
DOMAIN_CONSTRAINTS,
DOMAINS,
KEY_COLUMN_USAGE,
PARAMETERS,
REFERENTIAL_CONSTRAINTS,
ROUTINE_COLUMNS,
ROUTINES,
SCHEMATA,
TABLE_CONSTRAINTS,
TABLE_PRIVILEGES,
TABLES,
VIEW_COLUMN_USAGE,
VIEW_TABLE_USAGE,
VIEWS.

Here's some tips on how to make use of them: INFORMATION_SCHEMA.TABLES | mssqltips.com.


How to select unique records in PostgreSQL without using Distinct clause I have a query which selects number of records for each house.

SELECT
   -- 3 rows
   a.house,
   -- 12 rows (for each house)
   a.id,
   -- for each ID (there are 4 repeating records)
   -- out of these 4 records, only 1 is unique while remaining are
   -- repeated records
   a.points
FROM records a
group by a.house
order by a.house, a.id

Above query returns 144 rows (Here it is how: 4 repeatation for each number in ID column thus 12 IDs of one house returns 48, so 48 x 3 = 144 rows) . The third column contains 4 records (for each number in ID column) in which there is only 1 unique point for each house just like ID of the house. Thus, the output should give 12 (IDs and points) x 3 (houses) = 36 rows. The desired sample output would be:

house rows points
1      1    1
1      2    2
1      3    3
1      4    4
1      5    5
1      6    6
1      7    7
1      8    8
1      9    9
1      10   10
1      11   11
1      12   12
2      1    1
2      2    2
2      3    3
2      4    4
2      5    5
2      6    6
2      7    7
2      8    8
2      9    9
2      10   10
2      11   11
2      12   12

How can I remove these repeating records from third column without using PostgreSQL Distinct clause?

A query that may get the "desired output" could look something like this:
(as @joanolo had suggested: adding the second and third column to the GROUP BY)

select
  R.house
, R.id rows_
, R.points
from jkrecords R
group by R.house, R.id, R.points
order by R.house, R.id;

See a worked example here.
Apologies to everyone, and to @a_horse_with_no_name in particular, for any confusion I may have caused with my "assumptions".


Window functions , for instance LAG should do the trick:

SELECT run_date, count(distinct car_id)
  FROM
  (
   SELECT id as car_id, date_trunc('day', (time::timestamp) AT TIME ZONE 'EST') as run_date,
   battery - LAG(battery,1) OVER (partition by id,date_trunc('day', (time::timestamp) AT TIME ZONE 'EST')
     order by time) as diff
   from test_db
  )a
  WHERE diff >0
  GROUP BY run_date


what happens to user connections when an AG fails over? I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over.

We have a 2 node AG running SQL 2016 SP2 CU10 in synchronous commit mode, connecting with a listener.

What controls the switching of connections to the new primary node?  is there a way to accelerate the switch?  Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?

what controls the switching of connections to the new primary node?

The Availability group (AG) listener directs incoming connections to either primary or read replica depending on how the AG is configured (whether it allows read requests or not).

When a failover occurs, a role reversal happens and a secondary replica transitions to the primary role and the former primary will transition to a secondary role.

During a failover, all client requests are terminated. This is your application downtime. Now, after the failover, when a client reconnects to the AG listener, the listener will reconnect to the new primary (except for read intent connect requests).

  Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?

If your application supports :

MultiSubnetFailover = TRUE, then you will have a perf gain (as described in my answer.)
MultiSubnetFailover = False, then you should lower the HostTTL settings to a lower value e.g. 5 secs vs 300 secs of default.

Ref:

How the Connection Access Configuration Affects Client Connectivity
Connection Timeouts in Multi-subnet Availability Group


Automatically add core fields to a table when created I was reading these articles on StackOverflow regarding triggers and created date and updated date in mysql, and was wondering whether it is possible to create a trigger that checks if these two fields are present in the create table statement, and either creates the relevant trigger, or creates the fields, as well as the trigger? I am aware that there will be some tables for which this is not relevant, but I will address that in the future.

What is the best way to set up last-modified and date-record-added fields?

adding column to all tables in a mysql database (unknown table names)

The feature you are asking for is called "DDL triggers".

And no, MySQL doesn't have that. So you can't do this in a trigger.


SQL Server Partition I am trying to create partitions on a table of one of my test databases called WingTipToys2012.

Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and FgThree, as follows:

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgTwo;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgThree;

Then, I created one additional file for each file group, as follows:

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file1',
  filename='C:\...\file1.ndf', size=5MB) TO FILEGROUP FgOne;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file2',
  filename='C:\...\file2.ndf', size=5MB) TO FILEGROUP FgTwo;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file3',
  filename='C:\...\file3.ndf', size=5MB) TO FILEGROUP FgThree;

Then I created the partition function, as follows:

CREATE PARTITION FUNCTION WTPartFunction (int)
AS RANGE LEFT FOR VALUES(30, 60);

The problem comes when trying to create the partition scheme. To do so, I execute the following command:

CREATE PARTITION SCHEME WTPartScheme
AS PARTITION WTPartFunction TO (FgOne, FgTwo, FgThree);

But after executing the instructions above, I get the following error:

  Msg 208, Level 16, State 58, Line 1
  Invalid object name 'FgOne'.

It looks like the file group FgOne is not recognized, but I know it's there. Funny enough, SQL server also knows it is there. If I try to create another file group with the same name:

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;

I get the following error:

  Msg 5035, Level 16, State 1, Line 1Filegroup 'FgOne' already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.

I am not sure what I am doing wrong. Can anyone help?

I think you are running this in the context of master, when you are creating partitions you need to be in the context of the database, before you create the function and scheme do USE[database_name].

If you check master you will see your partition function is there, not in the database you think it is.


Hot Swapping Sql Server Databases I manage a database in SQL Server for a business's customer transaction.  The data is updated four times daily from a very complex Oracle database.  The SQL Server database is then connected to by a .Net application that provides visualization of the data.  This application only reads the SQL Server data, there is no writing back to the database.

At this point the data updates are taking 15 to 20 minutes.  This locks the application out of the database.  I am looking for a way to update the data without overly inconveniencing the users.

I was wondering about doing the updates into a second database, then swapping which database the application talks to.  This of course has to be done entirely on the server side as I don't want to have to make the users log back in.

If I do the updates into a second database, if the DB engine is hitting hard at the updated database, does this greatly affect the performance of users looking at the live database.

I would appreciate suggestions as to how best do this (or some other) approach to minimize downtime for the users.

Introduce a third database and use synonyms. For each table (I'll call it dbo.MyTable for this example), it looks something like this:

Point the synonym in the "shell" database to the version in the "live" database. So create synonym shell.dbo.MyTable for live.dbo.MyTable.
Do your most nasty data load in the "load" database
When you're ready to cut over, drop and recreate the synonym from step 1: drop synonym shell.dbo.MyTable; create synonym shell.dbo.MyTable for load.dbo.MyTable

You've effectively swapped your live and load databases and the application is none the wiser.


Identify rman files and delete them I need to clean up space and do a full backup of database.
Old backups are safe on tape.

My backups are on 2 specifics directories (2 channels).

So I did:

DELETE EXPIRED BACKUP;
DELETE OBSOLETE;
DELETE BACKUP;
DELETE DATAFILECOPY ALL;

Next LIST BACKUP did not found nothing.

Still, in my directories I have about 4 TB of data. Database is 8 TB.

File has names like:

20160226historyotqusdq7_1_1.bck
20151105historydata_D-HISTDB_I-3526265592_TS-history_2013_FNO-186_pmqlg4jl.bck

And so on, some hundreds of files.
HISTDB is the sid, history_2013 is one of the tablespaces.

How can I indentify what that files are and clean them in a safe way?

Oracle is version 11.2.0.2 on redhat linux, kernel version 2.6.18-238.el5

If the database does not know about those files, you should catalog them in RMAN:

catalog start with '/path/to/files';

After that you can use your above commands for identifying or deleting these files.

Deleting old backups in a safe way is achieved by running DELETE OBSOLETE, with the required retention set in configuration or specified in the command (e.g. DELETE OBSOLETE RECOVERY WINDOW OF 14 DAYS;).


Access 2010 After Insert data macro to update another table I have an Access DB with 3 tables.

Table 1
Appointments
Fields with Data that is not used anywhere else, but some of the information is inserted into 3 with a after insert Script.

Table 2
Usage Data of Salesmen
I need some of the fields from here and put them on Table 3 after the afterinsert-Script

example Row:

ID  Date      Month   NT-Account  Data
--  --------  ------  ----------  ---------
 1  1.1.1899  201409  Some User   (Numbers)

Table 3
Performance
(IDFS to the Appointment ID, Date of the Coaching, NT-Account, Personal Information)
(is from the insert Script and works well.

Now to the usage Data, it's daily and my seniors want to test the sum of this daily data with weekly usage, so i did a query which holds the information i want.

Agent   Login Date  Week    Sum1  Sum2  Sum3
------  ----------  ------  ----  ----  ----
*user*  01.01.1991  199101    10     2     0

So basically we just download the Usage data from a big data collecting Program and it's giving us the Infos we need.

Now to my problem.

I made a AfterInsert Macro which will automatically put some of the Appointments data in it , but i need some information from a different table. How can i set the macro to execute a update or a script?

An "After Insert" data macro can certainly modify records in other tables. You can use the LookupRecord, EditRecord, and CreateRecord Data Blocks as required.

However, a data macro cannot run a VBA script. Your actions are limited to those available in the data macro environment itself.


write_location and flush_location of pg_stat_replication view I'm confused about the write_location and flush_location of pg_stat_replication view . The document say, write_location stands for last transaction log position written to disk by this standby server , flush_location stands for Last transaction log position flushed to disk by this standby server, is that any difference?

Query pg_stat_replication view on primary database

sent_location :      Last transaction log position sent on this connection
write_location:      Last transaction log position written to disk by this standby server
flush_location:      Last transaction log position flushed to disk by this standby server
replay_location:     Last transaction log position replayed into the database on this standby server

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 23453
usesysid         | 24619
usename          | repuser
application_name | node1
client_addr      | 192.168.2.37
client_hostname  |
client_port      | 58412
backend_start    | 2015-09-07 20:56:13.141438+08
backend_xmin     |
state            | streaming
sent_location    | 2/3D397B1C
write_location   | 2/3D397B1C
flush_location   | 2/3D397B1C
replay_location  | 2/3D390954
sync_priority    | 1
sync_state       | sync

The two columns in pg_stat_replication are basically pointing out the difference between write(2) and fsync(2)

From the write(2) manpage:

  write() writes up to count bytes from the buffer pointed buf to the file referred to by the file descriptor fd.

  The  number  of  bytes  written  may be less than count if, for example, there is insufficient space on the underlying physical medium, or the RLIMIT_FSIZE resource
  limit is encountered (see setrlimit(2)), or the call was interrupted by a signal handler after having written less than count bytes.  (See also pipe(7).)

  For a seekable file (i.e., one to which lseek(2) may be applied, for example, a regular file) writing takes place at the current file offset, and the file offset is
  incremented  by  the number of bytes actually written.  If the file was open(2)ed with O_APPEND, the file offset is first set to the end of the file before writing.
  The adjustment of the file offset and the write operation are performed as an atomic step.

  POSIX requires that a read(2) which can be proved to occur after a write() has returned returns the new data.  Note that not all file systems are POSIX conforming.

From the fsync(2) man page:

  fsync() transfers ("flushes") all modified in-core data of (i.e., modified buffer cache pages for) the file referred to by the file descriptor fd to the disk device
  (or other permanent storage device) where that file resides.  The call blocks until the device reports that the transfer has completed.  It  also  flushes  metadata
  information associated with the file (see stat(2)).

  Calling  fsync()  does  not  necessarily  ensure  that the entry in the directory containing the file has also reached disk.  For that an explicit fsync() on a file
  descriptor for the directory is also needed.

  fdatasync() is similar to fsync(), but does not flush modified metadata unless that metadata is needed in order to allow a subsequent data retrieval to be correctly
  handled.   For  example,  changes  to  st_atime  or  st_mtime (respectively, time of last access and time of last modification; see stat(2)) do not require flushing
  because they are not necessary for a subsequent data read to be handled correctly.  On the other hand, a change to the file size (st_size, as  made  by  say  ftrun-
  cate(2)), would require a metadata flush.

  The aim of fdatasync() is to reduce disk activity for applications that do not require all metadata to be synchronized with the disk.

Under normal operation by default, PostgreSQL calls fsync (or fdatasync, if you're on linux), to ensure that the data in a WAL segment is well and truly written to disk.

In normal operation, when a write call happens, it's basically copying that data from PostgreSQL to the filesystem cache, which is just another bit of RAM. If the server crashes, then you will still lose that data, because it's still in RAM, even though it's just a different part of RAM.

On COMMIT, though, PostgreSQL calls fsync, which forces the WAL segment data to be written out to disk, not just to the filesystem cache in RAM. Which ensures that after a crash, committed data is still committed, and can be replayed.

It also has the added advantage of needing to call fsync fewer times for the data files, because all of the necessary data to properly replay the changes to those files are contained in the WAL segments, which are durably persisted to disk.

A nice description of this behavior under Linux can be found in the article Flushing Disk Buffers.

The syncing behavior can be controlled with wal_sync_method and fsync

Hope that helps answer your question. =)


Index on foreign key makes query extremely slow We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.

The queried table (table3) has an indexed PK, three FK with indexes and a compound unique constraint on the three FKs. The offensive query looks like this:

SELECT ...
  FROM table1 t1, table2 t2, table3 t3
  WHERE t1.abs_id = ?
    AND t3.vgs_id = t1.vgs_id
    AND t3.ai_id > ?
    AND t2.id = t1.t2_id
    AND t2.status = 2
    AND t2.felddimension = 0
    ...

Only instance restart solved the issue. Even killing connections did no help.

After futher investigation on the FKs and the indexes, it turned out that the index on the t3.ai_id column causes the severe drop in performance. After disabling this one the unique constaint served the query extremely fast.

The problematic part is AND t3.ai_id > ? (range scan). Unique scan does not cause any trouble.

Now the question is, how can an index cause such a slowdown and moreover, how can I investigate the cause? It simply doesn't add up for me.

Competitive times: normal 10 s, slowdown > 2 min or never returning.

EDIT 1 (2013-06-05): Upon Jack Douglas' and Chris Saxon's advises, I have ran stats and then performed explain plan, I have made a giant leap forward.

I have calcucated schema stats with and without the index. Regardless whether the index is available or not, the optimizer uses the 3-field-composite-unique-index making the query extremely fast.

Here's the explain plan from SQL Developer:

So far so good, now I have added a hint into the query to use the bad index, and the explain plan is:

Now, this is extremely slow. But why?

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)


Is there a less manual way of changing database properties for all databases in an instance? I've recently audited a SharePoint server and found configurations that are against best practice.

I'm hoping there is better and quicker way than right-clicking and changing these settings manually on over 100 database.

For instance, all the databases are set to autogrow by 10MB and the log files are set to autogrow by 1%.  I'd like for these to be a set number and not a percentage.

Any resources or recommendations would be greatly appreciated!

I often generate T-SQL like this:

select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)'
from sys.databases
where databaseIsMisconfigured = 1

Select the entire column, past it into a new window and F5.


SSRS (2005): conditionally format null/zero count cells based on group Okay, so I have an SSRS report column-grouped by weeks wait (and sorted by days wait, because weeks wait is a text category field), and a count of the number of people who've been waiting in each cell.

It's been requested that I format text in the 6+ weeks categories red and the remainder green for a quick visual representation of people who've been waiting an acceptable amount of time. So, the font colour is formatted with:

=Iif(Fields!Days_Wait.Value>42,"Red","Green")

However, this only works where the value has associated rows returned from the query. Where there's a count of 0 in the cell, it doesn't have a "Days_Wait" value to look up and so defaults to green.

Short of making the query return a whole load of superfluous empty lines so that every row category/column category combination has a row against it, is there any way to do this? I'm currently stuck with Visual Studio 2005 because the universe is a cold and unforgiving place.

I finally found the solution was to reference the category it's grouped by (weeks wait) - it's a little cumbersome because it's a string, but:

  =Iif( Instr("7-8wks8-9wks[...]51-52 wks52 wks +",ReportItems!Weeks_Wait.Value) > 0,"Red","Green")


PostgreSQL on Amazon RDS: out of memory error on bytea field > 64MB I am attempting to dump a modestly sized (less than 3GB) database from Postgres 9.5.4 running on Amazon RDS. The instance is a db.m4.xlarge, which has 4 virtual cores and 16GB RAM. It's using the default parameter group.

My dump fails like this:

$ pg_dump -h xxxxx.rds.amazonaws.com -U admin -d mydb -Fc -f dump-20170919.psql
Password:
pg_dump: Dumping the contents of table "plugindata" failed: PQgetCopyData() failed.
pg_dump: Error message from server: out of memory
pg_dump: The command was: COPY public.plugindata (plugindataid, pluginkey, filename, lastmoddate, data) TO stdout;

Because attempting to SELECT the problem field also causes an error, I was able to narrow it down to a specific field in one record. The data field (type bytea) in this record has a size of 66,459,495 bytes, which is an order of magnitude larger than any other record in the table.

I have tried increasing both work_mem (to 1GB) and maintenance_work_mem (to 3GB) to no avail. I'm not all that familiar with memory tuning for Postgres, so I assume there's something else I need to tweak?

EDIT: Server logs contain the following:

...:admin@mydb:[14270]:LOG: could not send data to client: Connection reset by peer
...:admin@mydb [14270]:ERROR: canceling statement due to user request
...:admin@mydb:[14270]:LOG: could not send data to client: Broken pipe
...:admin@mydb:[14270]:LOG: SSL error: bad write retry
...:admin@mydb:[14270]:FATAL: connection to client lost

I have verified that ssl_renegotiation_limit is set to 0.

PostgreSQL's handling of large binary fields is very memory-inefficient. It's quite normal for it to keep three or more copies of a given field in memory when procesing a row, albeit briefly. One TOASTed Datum, one deTOASTed Datum, one stringified hex-formatted Datum. I wouldn't be surprised if SSL added another copy.

That said, this doesn't really look like a memory issue to me. Lets say somehow PostgreSQL insanely stored TEN copies of the field. It's only 63MB. You'd need 640MB of RAM. That's nothing for your server. So unless RDS's postgres is patched to do things normal PostgreSQL doesn't do, or runs with some weird control groups configuration or something, it's unlikely to be a true memory problem.

I suspect the real issue is something else.

work_mem isn't relevant here, as it's only considered for things like sorts. It's not a memory quota for a backend.

"Out of memory" can be reported in error cases where something fails that should not usually fail, under conditions that are usually caused by not being able to allocate memory. Given that, I'm very suspicious of the "bad write retry" report from OpenSSL.

You've excluded issues with renegotiations, but in an opaque system like RDS it's hard to tell what else it might be.

Can you show more of the logs? That may be informative. Especially if you retry after setting log_error_verbosity = verbose and log_min_messages = debug1 in your configuration.

Also, what's the output of SELECT pg_column_size(x), octet_length(x) FROM mytable WHERE ..., where x is the problem row?

(Edit your question to add more info, then comment here to notify me)

If this were normal unpatched PostgreSQL on a real host I'd get you to attach gdb and capture the error so we could see exactly what's happening. But in this case your best bet will be to contact Amazon's support, since they're the only people who can actually see what's happening on the host.


Can column name be "Group" in PostgreSQL or in any databases I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group".
I tried creating it but it always throw a syntax error near the word = "group". I am really curious since the keyword in SQL is "group by" not group, so what is the reason, I cannot rename or create a column with a name "group".
Syntax and error I am using and getting:
ALTER TABLE test RENAME COLUMN sum TO group;
ERROR:  syntax error at or near "group"
LINE 1: ALTER TABLE test RENAME COLUMN sum TO group;

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier.
To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier.
ALTER TABLE test RENAME COLUMN sum TO "group";

Note that when using quoted identifiers, you need to always quote it. And it becomes case sensitive. "group" is a different column name than "GROUP".
For more details please see the manual.
In general it is a very bad idea to use names that require quoting. It will save you a lot of trouble if you can come up with a different name that does not require quoting.


Why would you index text_pattern_ops on a text column? Today Seven Databases in Seven Weeks introduced me to per-operator indexes.

  You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as long as the values are indexed in lowercase.

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

  We used the text_pattern_ops because the title is of type text. If you need to index varchars, chars, or names, use the related ops: varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops.

I find the example really confusing. Why is it useful to do this?

If the column is type text, wouldn't the other types (varchar, char, name) be cast to to text before being used as a search value?

How does that index behave differently from one using the default operator?

CREATE INDEX moves_title_pattern ON movies (lower(title));

The documentation often gives you an answer to such questions.  Like in this case, too:

  The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar,
  and char respectively. The difference from the default operator
  classes is that the values are compared strictly character by
  character rather than according to the locale-specific collation
  rules. This makes these operator classes suitable for use by queries
  involving pattern matching expressions (LIKE or POSIX regular
  expressions) when the database does not use the standard "C" locale.
  As an example, you might index a varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

  Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >=
  comparisons to use an index. Such queries cannot use the
  xxx_pattern_ops operator classes. (Ordinary equality comparisons can
  use these operator classes, however.) It is possible to create
  multiple indexes on the same column with different operator classes.

The documentation goes on to say:

  If you do use the C locale, you do not need the xxx_pattern_ops
  operator classes, because an index with the default operator class is
  usable for pattern-matching queries in the C locale.

You can check your locale as follows (it is likely to be UTF8 rather than "C"):

postgres=> show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8


Identifying Unused Stored Procedures This next year, I am helping an effort to clean several SQL Server environments.

We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.

Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called.  On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).

One approach to this is to use the SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not.  From then, we could move the procedures to a different schema in case a department comes screaming.

Is using the Profiler the most effective approach here?  And/Or have any of you done something similar and found another way/better way to do this?

You can use server side trace  (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis.

Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the data is flushed).

You can even schedule a job to capture DMV data to a table to keep it persisted.

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

Refer to :

How to find unused Stored Procedures in SQL Server 2005/2008
Identifying Unused Objects in a Database


Ms VS2017 with SSDT has the 'User with Password' template but thinks it's wrong In my database project when I choose the 'User with Password'  template in the Add New Item dialog, code is generated, kind of

CREATE USER [u2]
    WITH PASSWORD = 'jbniq_ushfbwc=l5nAxu8aapmsFT7_&#$!~<Jt}ho_aypip@'

GO

GRANT CONNECT TO [u2]

And it's immediately marked as having syntax error:

SQL46010: Incorrect syntax near 'jbniq_ushfbwc=l5nAxu8aapmsFT7_&#$!~<Jt}ho_aypip@'.

VS 2017 Comminity with SSDT 15.1.61903
Shoud I change some project settings or what?

As Aaron mentioned in the comments, try enabling containment in the database settings:

That's after right-clicking the database project and choosing "properties" in Visual Studio.

If you are not using contained databases, use the "User" script template instead of the "User with Password" script template.


SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows? I am using the import wizard in SQL Server 2014. When I import a text file with 40 columns with 1 million row it is pretty fast. On the other side when I am importing a few rows with 268 columns it is taken AGES ! The import seems to proceed very slowly "blocks" of 128 records. And between each "block" there are minutes going by...
Could someone give me some hints to try understanding what is happening?
Thank you in advance

At first, I guess the size of the 268 columns can be pretty big. So just look at number of bytes in one row, multiply by 128 and you'll see how much data SQL can cache at one time.

Second, there might be a lot of data type conversions from text to numerics and dates.

At third, there might be foreign keys, indexes, constraints and even triggers on the table you are writing into.

At fourth, the table can be in another database, which is placed on a slower drive.

At fifth, I guess all 268 columns can't get into the same page, and records go to LOB pages.

So, there might be even more reasons. I might suggest you to use clustered Columnstore index on that table, that might improve the loading situation.


Why is there still a varchar data type? Many of my databases have fields defined as varchars.  This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)

After working with databases for about 5 years, I've found I eventually run into problems with the limited nature of the varchar field and I have to modify my fields to store data as nvarchars.  After having to make another update to a table, converting a varchar field to an nvarchar, I just had the thought-- why are we still doing it this way?  I've long since made the mental decision to define all of new my text fields to nvarchar, instead of varchar, which is what I learned to do from my text books when I was in school 10 years ago.

It's 2011 and there was a new release of SQL Server last year.  Why do we continue to support a varchar datatype when we can/should instead be using nvarchar?

I know that it is often argued that nvarchars are "twice as large" as varchars, so storage space usage could be one arguement for maitaining varcars.

However, today's users could define their nvarchars to store the data as UTF-8 instead of the default UTF-16 if they want to save on storage space.  This would allow for 8-bit encoding if that is primarily desirable, while giving assurance that the rare 2-8 byte character that gets inserted into their DB would't break anything.

Am I missing something?  Is there a good reason why this hasn't changed over the past 15-20 years?

varchar work is good enough for a lot of Western European languages (Norwegian, Danish, German, French, Dutch etc too) subject to some collation issues
See this on SO varchar vs nvarchar performance nvarchar has serious performance implications
This is trivial compared to dealing with dates MDY vs DMY


Balancer is down after stopping config servers I had an urgent issue to solve in admin DB

I stopped all config servers, and made changes in the primary one.
After restarting all other servers - balancer working only on the server where I did changes, the other two have:

balancer:
        Currently enabled:  yes
        Currently running:  no

Also rs.status() returns:

configsvr> rs.status()
{
        "info" : "configsvr",
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76
}

Need some help to fix it
Should I restart all config servers with some options, or need to build replication from the beginning?

First: balancer is running ONLY at config server replica sets PRIMARY, never on SECONDARY.

Second: (as) your error message says that, that node where you gave rs.statatus() command is not started in replica set mode, more like maintenance mode. i.e. started without --replSet or replication.replSetName config parameter and maybe also without "sharding.clusterRole: configsvr" parameter.

You can check what where starting parameters, by just reading mongod.log file. There is (at startup) list of parameters what that mongod used when it was started.


How to make a trigger that will compare input with a value of other table? I had made two tables with the following fields:

TABLE 1: "Bookings"
  FIELD: "fk_flight_number"
  FIELD: "date_of_reservation"

TABLE 2: "Flights"
  FIELD: "flight_number"
  FIELD: "date_of_departure"

I want to add a condition, just for the consistency of my data. I want to check ON INSERT:

1) in Bookings - fk_flight_number must exists in Flights

2) date_of_reservation must be smaller (<) from date_of_departure.

How to do that? I have read about something similar and the keyword was TRIGGER. I think in my case it must be the same. Can you show me how to refer the second table? The logical conditions are not important - I can handle it. I need some snippet, because I don't know how to start writing the TRIGGER I need for. Or If you know some article describing similar problem - I will appreciate it too. Thank a lot. Of cource I'm not 100% sure that the best approach is TRIGGER. Any other advice will be appreciated too. One more time - thank you!

SQL Server Answer

CREATE TRIGGER FightNumberExists ON dbo.Bookings
AFTER INSERT
AS
IF EXISTS (SELECT fk_flight_number
           FROM inserted i
           INNER JOIN Flights f
              ON f.flight_number = i.fk_flight_number
           WHERE i.date_of_reservation < f.date_of_departure
      )
BEGIN
    RAISERROR ('Flight Number does not exist', 16, 1);
    ROLLBACK TRANSACTION;

    RETURN
END;

GO

Notes
In SQL Server the values are first loaded into the 'inserted' or 'deleted' table before being written to the physical table.

You can accomplish this with adding constraints to the tables as well.


Case overcounting what is expected So I am attempting to setup a query that can track actions that are automated within our web system.
The first thing I am attempting to check is the reminders we are sending out to customers.
I have got a query that should be returning what I am expecting but it's overcounting and I have no idea what it's doing. The query is as follows (I'm sure its terribly written this isn't normally job):
    SELECT  COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-05-13%'
                      THEN O.`Order ID` END
                 ) AS 'Expected 35 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-35-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 35 day Emails Sent',
            COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-06-04%'
                  AND  E.type IS NULL THEN O.`Order ID` END
                 ) AS 'Expected 14 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-14-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 14 day Emails Sent'
        FROM
        (
            SELECT  `permit_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', type AS 'Order type'
                FROM  `user_permit`
                WHERE  `status` = 'Awaiting Approval'
                UNION
             SELECT  `voucher_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', `type` AS 'Order type'
                FROM  `user_voucher`
                WHERE  `status` = 'Awaiting Approval'
        ) AS O
        LEFT JOIN
        (
            SELECT  `view`, ref, receiver_id AS 'user_id', created_at
                FROM  communication
                WHERE  `view` LIKE '%evidence%') AS C  ON (O.`Order ID` = C.`ref`
        )
        LEFT JOIN  user_evidence E  ON (O.user_id = E.user_id);

Now the results I am given are 2, 16, 14, and 26. Having been through the communication table and Counted the entries I'm looking for I got 3 and 11 for the second and fourth result (I don't actually know how many results there should've been for the first and third results)
One of the main problems with the way that the communication table is setup is that as the orders may receive many reminders / other emails there are many entries with the same ref (Order ID) associated with them which many of the other examples of queries I've found online have in common.
The communication table has the format:
+--+----+---+------------------+-----------+----+----------+----------+
|ID|type|ref|communication_type|receiver_id|view|created_at|updated_at|

the view is the email template that is going out and is what is easiest to check.
If any more information is needed please ask.

COUNT before JOINing, else you will be counting the result of the JOIN.
Possibly it is as simple as putting the C counts in the C subquery; ditto for O.  At that point, the subqueries may as well deliver only counts, not all the other columns.  And, then, why JOIN?


Using REGEX_LIKE to Implement a Check Constraint Background

I am working on the database design for an application that tracks information about database development projects at my company.  The project management office (PMO) assigns a psued-number to each project based on if the project's cost is counted toward merger integration or not.  To illustrate, the number assigned will either start with INT (for a project contributing to merger integration) or NINT (for a project not contributing to merger integration).  Then a sequential number is assigned.  For example, INT175 would be 175'th project identified contributing to merger integration.  An additional wrinkle is the PMO sometimes wants to create multiple projects with the same number as they are "related".  So for example there could be an INT175a project, an INT175b project, and so on.

While the PMO considers this number to be the identifier for a project, in the database I will only use the column as an alternate unique key both to ensure duplicate projects are not created and as a search key for users.  I want to implement a check constraint on that column to ensure only a valid number matching the rules I gave above can be entered.

What I've Tried - Regular Expressions

My first thought was to use a regular expression.  In Oracle, my target DBMS, we have the REGEX_LIKE function that can be used.  I researched the documentation (here, here, here, here, and here )  on regular expressions and found the options to be dizzying!  For whatever reason my brain gets tangled up trying to take the list of all the various pattern matching options and apply it to my specific example.  I'm one of those people who work best with examples.  Unfortunately, I haven't found many examples out there.  What I have found have either been too simple, or so complex I couldn't grasp them.

What I have tried thus far is something like this:

select c
from (
  select 'INT1756b' c from dual union all
  select 'INT175a' c from dual union all
  select 'INT75a' c from dual union all
  select 'INT75' c from dual union all
  select 'NINT2283a' c from dual union all
  select 'NINT2283' c from dual union all
  select 'NINT915c' c from dual union all
  select 'NINT915' c from dual union all
  select 'NINT95b' c from dual union all
  select 'NINT95' c from dual union all
  select 'ABC123' c from dual
) x
where REGEXP_LIKE ( c, '(NINT|INT)\d{2}[0-9]|[a-z]' );

My test "table" x has what I foresee as an example of all the possible combinations of valid numbers plus one invalid number.  They will all either start with INT or NINT, and then have at least 2 numbers up to 4 numbers, and then optionally a lower case letter on the end.

My Struggle - Handling Varying Lengths

What I am really struggling with is how to deal with the varying lengths of the numbers.  There can be 2, or 3, or 4 numbers, which may or may not then be followed by a lower case letter.  What I came up with above, just as a starter, was to look for 2 numbers after the INT or NINT.  I know there will always be at least two numbers.  Then I just look to see if the rest are in the set of digits or lower case letters.  Now I know this isn't good enough but it is all I could come up with after a day or two of racking my brain on this.

Plea for Help!

Has anyone done something similar with regular expressions who could point me in the right direction or give me an example?  Is there a more elegant and simple way to implement this constraint?  I appreciate any and all help and pointers!

This isn't really a database administration question, but...

Firstly, your pattern does not prevent entries like this: XXXINT123abogus, because it does not say that "INT" or "NINT" should be at the beginning of the string, neither does it say that the lowercase letter, if it's there, should be the last symbol of the string.

You may want to try something like:

 +----------------------------- begins with
 |+---------------------------- optional "N"
 ||  +------------------------- then "INT"
 ||  |  +---------------------- then digits
 ||  |  | +-------------------- minimum 2
 ||  |  | | +------------------ maximum 4
 ||  |  | | |   +-------------- then a lowercase letter
 ||  |  | | |   |  +----------- which is optional
 ||  |  | | |   |  |+---------- and nothing more
 ||  |  | | |   |  ||
 ||  |  | | |   |  ||
'^N?INT\d{2,4}[a-z]?$'


Lock only a row in insert command sql server I have this initial code below to create and insert some values on my table
IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock
CREATE TABLE dbo.Teste_Nolock (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)

INSERT INTO dbo.Teste_Nolock
VALUES ('User'), ('Name'), ('DataBase'), ('SQL Server'), ('Power BI'), ('SSRS'), ('SSIS'), ('SSAS'), ('Blog')

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Nolock Test'
WHERE Nome = 'User'

When I execute update without a commit or rollback, SQL Server is locking my table and i can't execute the command SELECT * FROM dbo.Teste_Nolock WHERE Nome = 'SQL Server' for example. How can i solve this kind of problem?

Make sure you have an index on the Nome column, allowing SQL Server to "bypass" (not having to read) those rows you exclude in your WHERE clause. I.e.
CREATE INDEX yourindexname ON Teste_Nolock(Nome)


Multi-table optimal database design I'm a bit new to the database design. Thus this question to people who may have more experience.

I need to design a database that needs to store statistical data for many systems. The data is collected every day. There may be a couple hundred statistical counters. The number of systems can also grow.

Which database design is more efficient? From long term maintenance standpoint, from performance standpoint, etc.

Design 1: one giant table with columns for counters. Then each system on each date will add its number of entries.

Design 2: every system gets its own table dynamically created. It will contain a line for every day of statistics collection. There will also be one more table of tables that will contain the list of all system tables.

Of the two options mentioned, without any doubt, the first is better.  MySQL is fine at handling large tables.  There is no reason whatsoever to break the data into separate "equivalent" tables.  In fact, having multiple tables with the same layout is usually an indication of a poor database design.

For performance, you can then add indexes on the system and date (or both in one index).  You can also learn about performance.

There are some reasons why you would split the data into separate tables per system.  Here are two:

If the statistics maintained for each system were very different from other systems, then it would be more efficient to store only the appropriate columns.
If system requirements mandated data separation or permissioning.  The latter is easier to do on the table level.

You might also consider an entity-attribute-value (EAV) approach.  This would be a table where you basically have four columns:

date
system name
statistic name
value

You then repeat rows for each statistic.  This is usually less efficient than the single-table approach.  But it can be beneficial, particularly when you have lots of statistics, and most systems have different sets of statistics.


How should row-specific metadata be created handled for an outer join view? I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when a ticket was paid and is only created when a ticket is first paid.

I'm currently using a view to present all of this data; a LEFT OUTER JOIN shows all rows from Tickets and associated Invoices data if it's there.

My problem is how do I handle creating new Invoices metadata? I can't update the view directly for new entries, so I have a stored procedure that takes a ticket number and creates or updates the Invoices table, however it only does one ticket at a time; I could be updating hundreds of tickets, so this doesn't work.

There's also no simple select criteria I can use; a specific set of potentially non-sequential tickets may be entered. I can't input a range of tickets, I need an explicit WHERE ticketno IN (Ticket#1, Ticket#2) sort of statement to update only the correct rows.

What is the best way to this sort of insert for related data? I've seen some implimentations of stored procedures taking in a list of values as a parameter, but all of them look very hacky and risky.

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET.  In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.


Selecting Distinct Rows but Counting All Rows I have the following table:

----------------------------------------------
| ID | interestingData |      timestamp      |
----------------------------------------------
|  1 |       400       | 2016-01-23 17:01:00 |
----------------------------------------------
|  1 |       400       | 2016-01-24 17:01:00 |
----------------------------------------------
|  1 |       350       | 2016-01-25 17:01:00 |
----------------------------------------------
|  2 |       23        | 2016-01-23 17:01:00 |
----------------------------------------------
|  2 |       34        | 2016-01-24 17:01:00 |
----------------------------------------------
|  2 |       12        | 2016-01-25 17:01:00 |
----------------------------------------------

Where our PK is (ID, timestamp). I'm attempting to determine a query that will give me the unique IDs and the latest interestingData for which interestingData exceeds a threshold. That would, of course, be done with:

SELECT DISTINCT ID
FROM table
WHERE interestingData > threshold
ORDER BY timestamp DESC;

However, I want the count of every occurrence where interestingData exceeded the threshold. My results table would ideally look like

------------------------------------------------------
| ID | interestingData | timestamp           | count |
------------------------------------------------------
|  1 |        350      | 2016-01-25 17:01:00 |   3   |
------------------------------------------------------

Were my threshold 300. I am aware that if you want to pair something distinct with a set of data then a left outer join is going to be in order, but I'm not entirely sure how to go about it. This is the closest I can think of so far.

SELECT DISTINCT ID
FROM table t1
LEFT OUTER JOIN table t2 ON t1.ID = t2.table.ID
WHERE interestingData > 300
ORDER BY timestamp DESC

This gets me the distinct IDs and pairs them with the rest of the data as I need, but no provisions for getting the other parts of the results, let alone the count.

If you want the interestingData and timestamp from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then:

;WITH x AS
(
  SELECT ID, interestingData, [timestamp],
    [count] = COUNT(1) OVER (PARTITION BY ID),
    rn = ROW_NUMBER()
      OVER (PARTITION BY ID ORDER BY [timestamp] DESC)
  FROM dbo.tablename
  WHERE interestingData > 300
)
SELECT ID, interestingData, [timestamp], [count]
  FROM x
  WHERE rn = 1;

Also, try to avoid data types and/or reserved keywords as column names. timestamp is not a great choice because (a) it's not very meaningful and (b) it requires square brackets in a lot of scenarios.


How to view the query of another session in pg_stat_activity without being super user? I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything.

select * from pg_stat_activity

However, is it possible to acheive the same result without being connected as superuser ?

Which privilege/role should I grant/create to have the results that a superuser can see ?

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it.

As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of pg_stat_activity by calling the function.

E.g., run as a superuser:

CREATE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa();

GRANT SELECT ON pg_stat_activity_allusers TO public;

Note that free access to pg_stat_activity is restricted for a reason. It's possible to snoop sensitive information from other people's queries - imagine for example if another user was using pgcrypto. Rather than granting rights to public you should grant them only to a specific user or role that is to act as a surrogate user for monitoring.


how to set a column to fetch the date now? hi i have a sample table named datenow and used the following to create my table on flamerobin to have a column that gets the date today automatically when an entry is added.

CREATE TABLE "DATENOW"
(
  ID Char(8) NOT NULL,
  "Start_Date" Char(10),
  "Client_Name" Varchar(30),
  BALANCE Integer,
  select cast('Now' as date) from rdb$database
  CONSTRAINT PK_datenow PRIMARY KEY (ID)
);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON "datenow" TO  SYSDBA WITH GRANT OPTION;

also tried inserting these sql statement

select date 'Now' from rdb$database
select time 'now' from rdb$database
select timestamp 'NOW' from rdb$database

but i cannot generate a table and cant determine where will i put the sql statement or what to do in order to have a column that gets the date today automat

Like this?

CREATE TABLE "DATENOW"
(
  ID Char(8) NOT NULL,
  "Start_Date" Char(10),
  "Client_Name" Varchar(30),
  BALANCE Integer,
  Now_Column TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_datenow PRIMARY KEY (ID)
);

If you INSERT into the table, and ommit a value for Now_Column (IE: it's NULL), the current timestamp will be used instead.

Also, never use CHAR/VARCHAR datatypes to hold dates (which I suspect you are trying to do with your Start_Date column) - use proper date datatypes.

Untested, by the way - this is from the documentation.


mongodb read data from secondary hidden replicaset We have a mongo cluster (3 member replica set) with two replicaset names (rs0 and rs1). We also enabled balancing and our data is splitted in both replicaset. In both replica set we have add a new replica member as hidden. Please find the hidden response below,

{
    "_id" : 3,
    "host" : "server1-replica-3:27018",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : true,
    "priority" : 0,
    "tags" : {
            "dc" : "east",
            "use" : "reporting"
    },
    "slaveDelay" : NumberLong(0),
    "votes" : 1
}

For a reporting purpose we need to connect both hidden servers (since our data is splitted into both replica sets) via router, so that live data cannot affect with the activities we done for repoting. For this we have tried the below commands to connect the hidden servers, but no luck.

Commands we tried:

With multiple replicaset hidden members:

/usr/local/mongo/bin/mongo mongodb://server1-replica-3:27018,server2-replica-3:27018/databasename?replicaSet=rs0,rs1&w=0&readPreference=secondary&maxPoolSize=50

With read preference secondary

/usr/local/mongo/bin/mongo mongodb://localhost:27017/?readPreference=secondary

Enabled tagging for both hidden servers and tried the below commands:

/usr/local/mongo/bin/mongo mongodb://localhost:27017/?readPreference=secondary&readPreferenceTags=dc:east,use:reporting
/usr/local/mongo/bin/mongo mongodb://server1-replica-3:27018,server2-replica-3:27018/databasename?replicaSet=rs0,rs1readPreference=secondary&readPreferenceTags=dc:east,use:reporting

Can anyone please help us with the steps to connect hidden replicaset members from both replicaset?

Hidden replica set members are invisible to client applications via replica set or mongos connections so all three of your examples are working as designed.
As per the documentation on Hidden Replica Set Members:

Clients will not distribute reads with the appropriate read preference to hidden members. As a result, these members receive no traffic other than basic replication. Use hidden members for dedicated tasks such as reporting and backups. Delayed members should be hidden.
In a sharded cluster, mongos do not interact with hidden members.

You can only connect to a hidden member directly (i.e. by specifying a single hostname and port). Read preferences and tags are ignored for direct connections.
For example, any of the following should work assuming server1-replica-3 is a resolvable hostname and you aren't blocked by firewall configuration:
 mongo server1-replica-3:27018
 mongo --host server1-replica-3 --port 27018
 mongo mongodb://server1-replica-3:27018

If you have a sharded cluster with multiple hidden members, you can only read from one hidden member at a time via a direct connection. Note that bypassing mongos with a direct connection to a hidden secondary may return stale or orphaned data for a sharded collection.
If you want to dedicate nodes for specific read use cases in a sharded cluster, instead of making these hidden you should use secondary read preferences with replica tag sets. Be aware that reading from secondaries may return stale, duplicate, or orphaned data for a sharded collection as at MongoDB 3.4. For more background, see SERVER-5931: secondary reads in sharded clusters need stronger consistency. An improvement for SERVER-5931 was recently committed to the MongoDB 3.5 development branch, so secondary reads in sharded clusters should be safer in the MongoDB 3.6 production release (expected later this year).


Extended event duration Millisecond or Microsecond? There was a question in this forum about Extended Event module_end duration unit, which I answered.  Details here.

Is it always in microseconds for all events?

It is a mix of Millisecond, Microsecond and some are unknown.  Easiest way to find I used below tsql code to determine which is what.

SELECT p.name package_name,
       o.name event_name,
       c.name event_field,
       DurationUnit= CASE
                         WHEN c.description LIKE '%milli%'
                         THEN SUBSTRING(c.description, CHARINDEX('milli', c.description),12)
                         WHEN c.description LIKE '%micro%'
                         THEN SUBSTRING(c.description, CHARINDEX('micro', c.description),12)
                         ELSE NULL
                     END,
       c.type_name field_type,
       c.column_type column_type
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE o.object_type = 'event'
AND c.name ='duration'

Here is the result set from a SQL 2016 SP1 server. I only posting part of it. Look at the DurationUnit column and you can see 3 different values.

For NULL ones I find the best option is to run (if possible with start and end time) and find what the unit is.

I ran the same query against vnext SQL Server and confirmed we will carry the same confusion to the future.