How can I improve the performance of a sorted join? This feels like such a common question, I'll understand if it is closed but if so please suggest a better place I could ask. I have the following two tables of interest:
CREATE TABLE [dbo].[Sessions]
(
    [Id] [int] PRIMARY KEY,
    [DateConnected] [datetime] NOT NULL,
    [Origin] [nvarchar](max) NULL,
    [TrackerId] [int] NULL,
    [Imei] [nvarchar](max) NULL,
    [Sim] [nvarchar](max) NULL,
    [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
    [Id] [int] PRIMARY KEY,
    [RequestId] [int] NULL,
    [SessionId] [int] NOT NULL,
    [DateProcessed] [datetime] NOT NULL,
    [Direction] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [Acknowledgement] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Version] [tinyint] NOT NULL,
    [Command] [tinyint] NOT NULL,
    [Flags] [tinyint] NOT NULL,
    [Checksum] [tinyint] NOT NULL,
    [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
    [TrackerId] ASC,
    [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
    [SessionId] ASC,
    [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

My most common query, and most expensive (quite often times out now) involves listing all packet transmissions for a particular tracker.
DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

This was good at first, but now there is a lot of data, it has slowed right down. My attempt to get the query plan today took 2 minutes, and shows that it will be using a table scan, rather than the index I created. Even when I force the index, it is still very slow.
In comparison, if I choose a session first, and search only for packet transmissions recorded within that session, the query uses the index and is incredibly fast.
My most successful attempt to speed up the query has been to order the results first by session id, then by date processed, to match the index order. While this is not technically always correct, it is acceptable. However, even this has started to time out, and I feel like there is something wrong with my understanding of how to make the JOIN faster.
What can I do to improve the performance of this query?
Querying with DATETIME variables instead of DATETIME2 has simplified the query plan, however it is still very slow.

Sessions has 265,929 rows

PacketTransmissions has 32,916,233 rows
That works out to be 123.7 packets per session, on average.

Some of the sessions are for unregistered devices, so they create a session, send between one and three packets, and then the session is rejected by the server.

I will normally be debugging a registered device, so the actual number of packets per session is considerably higher, between 300 and 5000 packets per session

Some trackers may maintain the same session for a month at a time if they have connectivity

I have in the past had a bad experience with changing the clustered index to use a non-sequential key. It results in a lot of out-of-order writes, and page splits, and the insert performance drops significantly.
The problem with the actual execution plans is that I don't want to run the database at max DTU for up to an hour, and potentially have inserts fail in the meantime.

Perhaps this is crazy, but I like to try a bit of blue-sky-thinking every once in a while, so I'd consider adding the TrackerId column to the dbo.PacketTransmissions table to avoid the join completely.  Obviously, this means you need to modify the row-insert procedure for the table, which may or may not be feasible.

However, this change, combined with a simple index:

CREATE INDEX IX_PacketTransmissions ON dbo.PacketTransmissions
(
    TrackerId ASC
    , DateProcessed ASC
)
INCLUDE (Id); --not strictly required, since the primary key
              --is always included in every non-clustered index
              --I include them just to be explicit

creates a query plan using a run-of-the-mill index seek, combined with a key lookup for each row returned.  As in:

To test this, I created a minimally complete verifiable example:

USE tempdb;

IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL
DROP TABLE dbo.[Sessions];
IF OBJECT_ID(N'dbo.PacketTransmissions', N'U') IS NOT NULL
DROP TABLE dbo.PacketTransmissions;
GO

CREATE TABLE [dbo].[Sessions]
(
      [Id] int
        CONSTRAINT PK_Sessions
        PRIMARY KEY CLUSTERED
    , [DateConnected] datetime NOT NULL
    , [Origin] nvarchar(max) NULL
    , [TrackerId] int NULL
    , [Imei] nvarchar(max) NULL
    , [Sim] nvarchar(max) NULL
    , [ProtocolVersion] tinyint NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
      [Id] int
        CONSTRAINT PK_PacketTransmissions
        PRIMARY KEY CLUSTERED
    , [RequestId] int NULL
    , [SessionId] int NOT NULL
    , [DateProcessed] datetime NOT NULL
    , [Direction] int NOT NULL
    , [Sequence] int NOT NULL
    , [Acknowledgement] int NOT NULL
    , [DateRecorded] datetime NOT NULL
    , [Version] tinyint NOT NULL
    , [Command] tinyint NOT NULL
    , [Flags] tinyint NOT NULL
    , [Checksum] tinyint NOT NULL
    , [Data] varbinary(max) NULL
    , [TrackerId] int NULL
)
GO

INSERT INTO dbo.[Sessions] (Id, DateConnected, Origin, TrackerId, Imei, Sim, ProtocolVersion)
SELECT ROW_NUMBER() OVER (ORDER BY sc1.id)
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(nvarchar(max), CRYPT_GEN_RANDOM(128))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(tinyint, CRYPT_GEN_RANDOM(1))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

INSERT INTO dbo.PacketTransmissions (Id, RequestId, SessionId, DateProcessed, Direction, Sequence, Acknowledgement, DateRecorded, Version, Command, Flags, Checksum, Data, TrackerId)
SELECT ROW_NUMBER() OVER (ORDER BY s.Id)
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(3))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(2))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CRYPT_GEN_RANDOM(128)
    , s.TrackerId
FROM dbo.[Sessions] s
    CROSS JOIN (SELECT v.n
    FROM (VALUES (0), (1))v(n)) v;
GO

On my system, this creates around 700,000 session rows, and double that number of transmission rows.

The query then becomes:

DECLARE @TrackerId int = 100;
DECLARE @StartDate datetime = '2017-03-10';
DECLARE @EndDate   datetime = '2017-03-12';

SELECT [PacketTransmissions].*
FROM [PacketTransmissions]
WHERE [PacketTransmissions].[TrackerId] = @TrackerId
    AND [PacketTransmissions].[DateProcessed] > @StartDate
    AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC;

That link does not indicate a table scan. That is an index scan. With a narrow date range the date is more selective than the join so it (properly) does the date first.

Keep the PK clustered index on the two Id

Have three separate indexs

[PacketTransmissions].[SessionId]
[PacketTransmissions].[DateProcessed]
[Sessions].[TrackerId]

Or

[PacketTransmissions].[SessionId], [PacketTransmissions].[DateProcessed]
[PacketTransmissions].[DateProcessed], [PacketTransmissions].[SessionId]
[Sessions].[TrackerId]

Just my formatting - same query

DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
  FROM [Sessions]
  JOIN [PacketTransmissions]
    ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
   AND [Sessions].[TrackerId] = @TrackerId
   AND [PacketTransmissions].[DateProcessed] > @StartDate
   AND [PacketTransmissions].[DateProcessed] < @EndDate
 ORDER
    BY [PacketTransmissions].[DateProcessed] DESC


How to solve multidimensional data relationship I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.

Imagine a complex fashion product Pants with these Properties as dimensions:

Length (35,36,37,38)
Size (32,33,34,35)
Color (White, Red, Blue, Yellow, Green)
Kneepatch (Woodcutter, Brown, Silver)
Linked-belt (Brown, Beige, Black)

So there are 4*4*5*3*3 = 720 product variants. I know this is extreme, but I'm purely doing this to immediately test if my system will be able to handle it in a later phase.

So trimmed down I have:

Products

id
name

Properties

id
name

Thinking of RDBMS logic aside I would get a table like this, which of course isn't possible as it would constantly add properties and adds tons of nulls:

Productvariants

 id
 product_id
 property_1_id
 property_2_id
 property_3_id
...etc.

So this is a bit harder than a usual Color/Size combination you normally have on a product.

Now I can fake this methodology and just dump a serialization of Property ID's in one field on the Productvariants, like so:

Productvariants

id
product_id
properties {1, 2, 3}

.. and then split them in my ORM, but I'm afraid this will greatly increase the needed queries in the future (with tens of thousands of product variants) and optimization doesn't seem possible anymore that way.

Does anyone have experience with this and how to maybe intelligently do this? I'm using MySQL.

Products

ProdId
ProdName

Properties

(PropId) (primary key, if required)
ProdId (composite key)
PropCode (composite key)
PropValue (composite key) (if storing a specific value, otherwise you can remove this to simply store the possible properties for any given product)

Basically you store multiple values for a given property that all point back to a single product.  The composite key would need to be unique and you would need to ensure data integrity by either linking PropCode to another table or restricting the values on entry.

If you want to fix it in the relational model you're going about it the wrong way.  You need a table for every kind of fashion product.  It means more SQL work but not, as might seem, infinitely more work.  Instead of two tables, you might have 50.  The payoff is in a simpler application logic and simpler, better-performing queries.

The DBMS will work for you only if you relate attributes by making them columns, not rows.  The moment you put an attribute in a row, you prevent the server from enforcing domain constraints.  And your queries will suffer as you try to reconstruct the table from the rows.

The set of attributes is more stable than you might think.  Many application programmers seem to think the problem domain expands rapidly and unpredictably.  In fact, we've had color, size, and length for a long time, and it will be longer still before we start selling shirts made of helium or shoes as singletons.


A query that lists all mapped users for a given login When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:

I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every database one at a time and retrieves information from sys.database_permissions

Is it possible to write a single query that retrieves the user mapping information shown above or am I forced to use a cursor or sp_MSforeachdb or something like that?

Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb (background here and here).
This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.
DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS,
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS,
    STUFF((SELECT N'','' + r.name
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:
DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max),
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ',
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.

What about EXEC master..sp_msloginmappings?


Any way to maintain a partial index on only the highest element in Postgres? I need to

SELECT MAX(created_at) FROM someTable

whenever my application starts up. The table is enormous (billions of rows). The index on created_at that I created to make this query fast is using up multiple gigabytes. Is there a way to create some kind of partial index only on the highest element? Or any way to make this take up less space? Or an alternate approach entirely? All I ever need to query on this column is the maximum element.

The best is to take an alternative approach and not to query the large table all the time, if that is the only purpose of the index.

Create a second table that only contains a single timestamp and define a trigger on sometable that updates that small table whenever a new row arrives or an old one is updated.

This approach will only work well if you never delete the row with the latest created_at or never update created_at to an earlier timestamp.

If you can afford to have this information not always up-to-date, you can can create a materialized view:

create materialized view my_view as select max(created_at) from my_table;

And you only need to run (which should be very fast):

select max from my_view;

This object takes very little space. However everytime that you need to have this materialized view updated, you need to refresh it and without an index it will need to scan the whole table:

refresh materialized view my_view;


How do I migrate Postgres database using an intermediate file I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server.
The network pipe recommended by the Postgres docs won't work for me.  The docs also unhelpfully say "Or you can use an intermediate file if you wish" without giving an example of how to do this.
This is what I've tried.  On the originating server, I ran
$ sudo pg_dump -C {dbname} > ~/dumpfile.sql

to create the dump file.
On the target server, I get this error:
$ sudo -u postgres psql {dbname} < ~/dumpfile.sql
could not change directory to "/home/username": Permission denied
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

Of course the target database does not exist; I used the -C option when creating the dumpfile so that it would be created automatically during upload, but evidently this is not automatic.  I read through the man page for psql, but I didn't see a flag that would make it recognize the "create database" part of the dump file.  Web searching didn't turn up anything, and naturally the Postgres documentation is useless.
How do I upload the file and have the "create database" portion recognized?
Edit
The could not change directory to "/home/username": Permission denied line is not the error. This is just something Postgres always does on my installation.
I have tried the upload command from the /tmp/ directory with 777 permissions on the file.  It still does not work:
/tmp$ ls -Alh
-rwxrwxrwx 1 username group 5.1M Oct 21 11:07 dumpfile.sql
/tmp$ sudo -u postgres psql {dbname} < /tmp/dumpfile.sql
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

When you are restoring a dump taken with -C, the database you specify on the command line is name of the database you connect to in order to execute the creation of the new database.  Therefore it can't be the name of the new database.  It seems like you already know this, you just haven't thought it through.  psql is trying to connect to {dbname} because that is what you explicitly told it to do.
PostgreSQL doesn't "treat" sudo commands differently.  Sudo is in charge, not PostgreSQL.  Different commands do different things, and PostgreSQL is just along for the ride.
You are trying to tell the "postgres" system user to read a file located in  your daily user's home directory.  It probably doesn't have permission to do that.  Again, this is not PostgreSQL's choice.  You could put the file somewhere else (like /tmp) and give it world read permissions, or you could change your pg_hba.conf so you can connect to the database as your daily user rather than needing to sudo all the time.

troubleshooting steps:

make sure directory exist
make sure you are using the right user normally we use postgres user you can switch to it using sudo -i -u postgres if you are using a sudoer user, and su - postgres if you are using root
postgres user should have the needed permission for this directory
postgres service needs be up and running, you can check it by running ps -ef |grep postgres or the traditional way service postgresql status


innodb table does not exist I have four databases each holding several hundred tables.

Every table in one database suddenly returns error code 1146 Table xxx does not exist. The other databases are fine. I can see all the .ibd and .frm files ok and their various file sizes suggests they are not empty. There is an ibdata1 as well.

From workbench I tried Analyse Table, which indicates InnoDB: Tablespace is missing for table '...' and going on to 'Doesn't exist'.

I created new test tables and they work fine.

OSX was updated the other day, but as the other databases are fine I can't see a connection.

I haven't manually moved/copied etc any files etc.

Something has gone awry, but what?

This sounds like all your .ibd file have been detached. Why ?

Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko

ibdata1 has an internal data dictionary that registers every .ibd by a unique tablespace_id. Somewhere along the line, all the tables in the trouble database have become detach from the data dictionary.

I wrote about this years ago

Apr 23, 2012 : How can extract the table schema from just the .frm file?
Dec 21, 2011 : Table compression in InnoDB?

To rightfully give credit where credit is due, I learned about this concept of detached .ibd files and trying to connect them back to the data dictionary from a blogpost written by Chris Calendar back in Feb 2009. I once helped client recover 30 tables with the information from his post (See my post ERROR Cannot find or open table? on what I did to help that client). Please read his biog and then my posts on what you need to try.

As for the why the table would be come detached, here is an example

Your datadir is /var/lib/mysql
Your have a database called mydb
You have a table called mytable

If you create an InnoDB table called mytable in the mydb database

You get two files

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

.ibd file would be assigned a tablespace_id.

If you drop that table and create it again, the table would have a different tablespace_id.

Now, picture this:

You make a backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table
You drop the table and create it (TRUNCATE TABLE internally does the same thing), ending up with tablespace_id of 28
You restore the backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table

This causes the detachment because table mydb.mytable has the old tablespace_id of 27 while the data dictionary within ibdata1 has 28.

Since you said you didn't move or copy anything, then you may have too many InnoDB tables. Each InnoDB table needs two file handles to be opened and accessed.

I hope I have given your the information you need to help you recover your database.

Please check error logs , if it is showing error in .frm file , your tables are corrupted.
cd /var/lib/mysql/mysql
.FRM  =>  It has the table structure of your table or table definition
.MYI  =>   It has the indexes of your table
.MYD =>   It contains your data

And also check mysql table status using mysqlcheck table option. If it is showing status error your tables are corrupted


Mysql find 10 nearest point from given lat/long per type? I have a database given with 3 different types of locations A, B and C. These points have latitude and longitude information with them.

Now I have a random lat-long given and I want to find the closest 10 points per type to that given location - how can I do that? Anyone has an idea?`

Thanks for any help

Searching for the nearest 10 points of interest gives, usually, near points. In that case the usual cartesian distance (SQRT((LAT1 - LAT2)^2 + (LONG1 - LONG2)^2)) can be used.

Given a generic table (for the definition of latitude and longitude I've used this pointer.)

CREATE TABLE POI (
   ID INT IDENTITY(1, 1)
 , `Name` Varchar(100)
 , `Type` CHAR(1)
 , `LAT`  Decimal(10, 8)
 , `LNG`  Decimal(11, 8)
)

The query would be something like

DECLARE myLAT Decimal(10, 8) DEFAULT 45.48561;
DECLARE myLNG Decimal(11, 8) DEFAULT 123.47981;

(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'A'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'B'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'C'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)

In case the point of interest are few and far from the 'here' point and from themselves you have to use a different formula for the distance, to account that the earth is, more or less, a sphere.


PostgreSQL Select statement produces errors in datagrip but not pgAdmin4 I can run the following script without errors in pgadmin4

SELECT * from tbl

I have tried the following in datagrip

SELECT * from tbl

and

SELECT * from public.tbl

Neither of the above scripts worked. The following error was returned:

[2018-05-12 21:16:44] [42P01] ERROR: relation "public.tbl" does not exist
[2018-05-12 21:16:44] Position: 15

This ended up being really simple and a very silly mistake, but hopefully this will help someone else.

I simply needed to select the correct database. The default is postgres. The database I had my tables in was webadmin. You can change the database when you make your connection in datagrip. The screen where I made the change looked like this

@Alex,

It's better to change a search path at top right angle of the console

Refer to documentation


How can I recover my tables in sql server database I deleted 3 tables from my database with data and I have no backup. How can I restore tables with data in my database ?

How can I restore tables and data with database log file?(Database recovery model is full)

Yes, as Iren and Rahul said, you can use ApexSQL tools. If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records

On the other hand, ApexSQL Log cannot recover records lost when a table was dropped, it can only recover the table structure

In case the records that were lost using DELETE (not DROP TABLE), both ApexSQL Log and ApexSQL Recover can help

The advantages of ApexSQL tools over recovery to a point in time is that ApexSQL will recover just the tables you specify (creates CREATE TABLE and INSERT INTO scripts) , while a point-in-time recovery will roll back all the transactions that happened in the meantime

A DROP TABLE statement marks the MDF file pages used by the dropped table for deallocation. These pages are actually still in the MDF file until overwritten by new operations. To prevent new operations overwrite the data necessary for successful recovery, we recommend creating a copy of the original MDF and LDF files immediately

Disclaimer: I work for ApexSQL as a Support Engineer

You try this links ans get your solution

http://www.sqlrecoverytool.com/deleted-sql-records-recovery.html

http://apex-sql-log.apex-sql-llc.qarchive.org/


Grant create permission on a specific schema in Oracle 11g I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this correct?

You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema.  I suggest you look into proxy authentication.  This basically involves altering user A to allow user B to proxy as A:

ALTER USER A GRANT CONNECT THROUGH B;

Then the connection uses user B's authentication, but gets the permissions of user A.

connect B[A]/Password@Database

This question was somewhat covered by my more specific question here.

Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object.  While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema.  For example, the user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema.  These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema.  The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.

You could just create a role with those permissions, and grant the role to whoever needs that ability.


Fastest way to create local copy of live InnoDB MySQL database? We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+).

Every month or so we need to copy this database to our development machines to work on locally.

Previously we have exported the database with MySQL Workbench, downloaded the .sql file to our development machines, and then imported it, again with MySQL Workbench. The problem is now the database is so large, we can't do the restore quick enough for it to finish over the weekend! So on Monday morning we have a half-imported database to work with.

What do other people do in this situation?

The only solution I can think of so far, is doing the restore to an unused machine, then copying the data to all our development machines (5 of them). Would this work?

You can take a backup from the live MySQL with Xtrabackup.

Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions:

Install the key:

# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add the source file:

# cat /etc/apt/sources.list.d/percona.list
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

Update the local cache:

# apt-get update

And install xtrabackup:

# apt-get install xtrabackup

To take a copy of the database run following:

    # innobackupex .

It will create a directory like "2010-03-13_02-42-44". Copy that directory to a development box, apply redo log:

    # innobackupex --apply-log /data/backups/2010-03-13_02-42-44/

Then the directory is ready to use. Copy it back to MySQL datadir and fix ownership:

    # cp /data/backups/2010-03-13_02-42-44/ /var/lib/mysql
    # chown -R mysql:mysql /var/lib/mysql

Then you may start MySQL.

More details you can find on http://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html

Alternatively you may consider setting up replications from the main server to your dev machines. It would require to have a direct connection from your master to the slaves (the dev machines), but it doesn't need to be permanent. If you connect it once a month then the master will replicate only the changes, which should still be much faster.

I doubt any other backup solution can do better than MySQL Workbench (which uses mysqldump behind the scenes). All solutions doing a normal backup have to dump the content and restore it later. The only alternative is to replicate changes (or move binary datafiles, which is a bad idea and may not work in the future).


Can't rename columns in PostgreSQL views with CREATE OR REPLACE In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.

This works

CREATE OR REPLACE VIEW gfam.nice_builds AS
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

The above makes a duplicate of the family_tree table but the following attempt fails:

CREATE OR REPLACE VIEW gfam.nice_builds AS
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

ERROR:  cannot change name of view column "family_tree_id"

How can I rename columns?

I can reproduce your error ... in my case, I created a column first as 'date' then as 'x' (was trying to see if it was an issue with a reserved word; it wasn't:

ERROR:  cannot change name of view column "date" to "x"

If you issue a drop view first, it'll let you re-create the view with a changed name.  I have no idea why create or replace won't do it.

Clarification by Colin 't Hart:

The documentation for CREATE VIEW explains it pretty well, I think:

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

This is because the view is already saved with the column names. Delete the saved view and then run the modified view with the new 'AS' column names.


Could not find the Server Agent in SSMS I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8

  Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.

I cannot not find SQL Server Agent. Where can I make this SQL Server Agent visible?  I have given the current user all the access permission.

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc...

The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx

Go to connection properties -- type msdb instead of default under options > connection properties > Connect to Database > msdb -- then connect


MySQL IS NULL / IS NOT NULL Misbehaving? Please have look at this table:

mysql> desc s_p;

+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| id                      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| s_pid                   | int(10) unsigned | YES  | MUL | NULL    |                |
| sm_id                   | int(10) unsigned | YES  | MUL | NULL    |                |
| m_id                    | int(10) unsigned | YES  |     | NULL    |                |
| created                 | datetime         | YES  |     | NULL    |                |
| s_date                  | datetime         | YES  |     | NULL    |                |
| estimated_date          | datetime         | YES  | MUL | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Now Have a look at these queries:

mysql> select count(*) from s_p where estimated_date is null;
+----------+
| count(*) |
+----------+
|   190580 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from s_p where estimated_date is not null;
+----------+
| count(*) |
+----------+
|    35640 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from s_p;
+----------+
| count(*) |
+----------+
|  1524785 |
+----------+

The counts above are not matching. While as per my understanding:

Count with IS NULL and Count with IS NOT NULL should be equal to count when queried
without where clause.

Any idea on whats happening here?

===================================================

Update on 17th Feb 2012

Since, I found that a lot of people are asking about the kind of values estimated_date currently has. Here is the answer:

mysql> select distinct date(estimated_date) from s_p;

+----------------------+
| date(estimated_date) |
+----------------------+
| NULL                 |
| 2012-02-17           |
| 2012-02-20           |
| 2012-02-21           |
| 2012-02-22           |
| 2012-02-23           |
| 2012-02-24           |
| 2012-02-27           |
| 2012-02-28           |
+----------------------+
9 rows in set (0.42 sec)

As you can see above estimated_date either has NULL or a valid datetime values. There are no zeros or empty strings "".

Can this(original issue) happen if the index on estimated_date has some problem/s?

===================================================

Update on 18th Feb 2012

Here is the show create table output:

 | s_p | CREATE TABLE `s_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned DEFAULT NULL,
  `sm_id` int(10) unsigned DEFAULT NULL,
  `m_id` int(10) unsigned DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `estimated_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `sm_id` (`sm_id`),
   KEY `estimated_date_index` (`estimated_date`) USING BTREE,
  ) ENGINE=InnoDB AUTO_INCREMENT=1602491 DEFAULT CHARSET=utf8 |

Again, I can only suspect index on estimated_date here.

Also, the mysql server version is 5.5.12.

Do you have some zero dates? Datetime values of 0000-00-00 00:00:00 are considered by MySQL to simultaneously satisfy is null and is not null:

steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)

steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

See: http://bugs.mysql.com/bug.php?id=940

This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.

Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null and is not null counts should exceed the unrestricted count)...

This is expected. For a column that is nullable a 0==NULL="" and so on. So the first check actually returns rows where no date was set or its perceived analogous to "0/NULL"


Printing comments or statements when executing script to a output file using TOAD/ORACLE Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help identify particular sections of the script during the execution.

For instance we have a .sql script that runs in the evening that checks for discrepancies on particular inventory numbers. These inventory numbers have lets say a store_id of 1, 2, 3, 4, 5.

The script has a select statement attached to each of these store_id. I would like to be able to place a PRINT statement for the select statement of Store_ID 1 so I can see the output.txt file and see the heading 'Store_id 1" then the inventory numbers, then see heading "Store_id 2" etc...

in MSSQL SSMS it would like something like this

PRINT 'Searching Inventory Discrepancies at Store 1'
Select * from table1 where store_id = '1' etc...

PRINT 'Searching Inventory Discrepancies at Store 2'
Select * from table1 where store_id = '2' etc...

If I need to clarify please advise, thank you!

The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file:

BEGIN
    DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE ');
END;
/

You should also issue SET SERVEROUTPUT ON to ensure the PUT_LINE command will work.

Here are some commands you can use while using DBMS_OUTPUT.PUT_LINE to control the formatting of the output:

SET UNDERLINE OFF
SET LINESIZE 32767
SET PAGES 10000
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF
SET HEADING ON
SET NEWPAGE 0
SET HEADSEP OFF
SET WRAP OFF
SET TERMOUT OFF

In my case, I had to use particular settings to allow my output to stop word wrapping and causing the formatting to get thrown off.

SET LINESIZE 32767
SET HEADING ON
SET HEADSEP OFF
SET WRAP OFF

Or just do this:

Select * as 'Searching Inventory Discrepancies at Store 1'
from table1 where store_id = '1' etc...


query regarding combining an update and an insert query into a single query in mysql i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data.

I can use a select to get the old data, an insert to history and finally an update to change data.

can i have all these in a single query in mysql without using stored procedures, triggers, etc.. like using locks etc.. if so give me a small sample.

To do this without the risk of blocking another user trying to update the same profile at the same time, you need to lock the row in t1 first, then use a transaction (as Rolando points out in the comments to your question) :

start transaction;
select id from t1 where id=10 for update;
insert into t2 select * from t1 where id=10;
update t1 set id = 11 where id=10;
commit;

I have found that this query works across SQL & MySQL Servers
INSERT INTO t2 SELECT * FROM t1 WHERE id=10; UPDATE t1 SET id=11 WHERE id=10;

Hope this is useful to some else also in future.


SQL Server Agent fails after installing SQL Server 2019 CU2 After installing CU2 our SQL server agent randomly stops working.  If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click:
  https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A severe error occurred on the current command.  The results, if any,
  should be discarded. (Microsoft SQL Server, Error: 0)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4013&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

Any clue what could be causing this?

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will be provided there as we investigate.
FYI, the bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you're hitting this issue, revert back to CU1. We apologize for any inconvenience we may have caused.
The Cumulative Update 2 for SQL Server 2019 article now has the following warning:

Known issue
There is a known issue with SQL Server Agent in SQL Server 2019 CU2. This will be fixed in the next CU release (SQL Server 2019 CU3).
If you are using SQL Server 2019 SQL Server Agent, it is advised to skip this CU upgrade, and wait for SQL Server 2019 CU3.
If you have already applied SQL Server 2019 CU2 and are experiencing issues with SQL Server Agent, please revert to SQL Server 2019 CU1.

Fixed in CU3
FIX: Error occurs when you interact with SQL Server Agent in SQL Server 2019

We had the same issue. I replaced just the SQLAgent.exe with the older version that I found in C:\Windows\Installer\$PatchCache$  the file was called MPT_SQLAGENT_EXE_64 and renamed it to SQLAgent.exe.    I had also checked the .dlls related to SQLAgent but they had not been updated.


Adding a nullable column concurrently with an update or insert In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update?

For example:

ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;

Referencing the documentation for ALTER TABLE (highlighting mine),

  The changes specified in ALTER TABLE are implemented immediately. If
  the changes require modifications of the rows in the table, ALTER
  TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M)
  lock on the table to make sure that no other connections reference
  even the metadata for the table during the change, except online index
  operations that require a very short SCH-M lock at the end. In an
  ALTER TABLE…SWITCH operation, the lock is acquired on both the source
  and target tables. The modifications made to the table are logged and
  fully recoverable. Changes that affect all the rows in very large
  tables, such as dropping a column or, on some editions of SQL Server,
  adding a NOT NULL column with a default value, can take a long time to
  complete and generate many log records. These ALTER TABLE statements
  should be executed with the same care as any INSERT, UPDATE, or DELETE
  statement that affects many rows.

Also, Paul Randal has some good information about what happens when you add columns (and how long the operation might take) in Misconceptions around adding columns to a table.  To summarize some main points:

New column is nullable, with a NULL default. The table’s metadata
records the fact that the new column exists but may not be in the
record. This is why the null bitmap also has a count of the number of
columns in that particular record. SQL Server can work out whether a
column is present in the record or not. So – this is NOT a
size-of-data operation – the existing table records are not updated
when the new column is added. The records will be updated only when
they are updated for some other operation.
New column is nullable, with a non-NULL default. It depends which
version of SQL Server you’re using:

Before SQL Server 2012: This IS a size-of-data operation. The
non-NULL default forces all existing records to be updated when the
column is added, and so the null bitmap will be updated too.
SQL Server 2012 onward: same behavior as for a NULL default nullable
column (i.e. metadata only operation)

New column is not-nullable (obviously with a non-NULL default). This
IS a size-of-data operation.

the entire column will be null for all the records in the table

begin tran

ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;

select MyColumn,* from MyTable

rollback

run this command and check what happens!!!

the code inside rollback update temporily so u can check ,, by modifying thecode also


Why MongoDB collections are dropped automatically? I have installed mongodb 3.6 standalone instance and created some collections through insert query in mongo shell. My app points to the db server, the app creates some new collections, it reads, writes and so on. This is running for more than two months. If we restart the instance we can see in mongodb collections which are created through insert query are lost. Now we can see the same behavior in other environments. Also, while analyzing this problem, we found that during mongodb server start there was a error saying "mongodb failed to create WiredTiger bulk cursor: Device or resource busy". Any idea what that error line implies?

The below messges are found in mongodb log file during server shutdown

**2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got signal 1 (Hangup), will terminate after current cmd ends**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] shutdown: going to close listening sockets...**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock**

**2018-08-28T12:44:46.152+0530 I FTDC [signalProcessingThread] Shutting down full-time diagnostic data capture**

**2018-08-28T12:44:46.158+0530 I STORAGE [signalProcessingThread] WiredTigerKVEngine shutting down**

**2018-08-28T12:44:46.265+0530 I STORAGE [signalProcessingThread] shutdown: removing fs lock...**

**2018-08-28T12:44:46.265+0530 I CONTROL [signalProcessingThread] now exiting**

**2018-08-28T12:44:46.266+0530 I CONTROL [signalProcessingThread] shutting down with code:0**

And during server startup the below messages are found in the log file,

**2018-08-28T14:19:39.256+0530 I STORAGE [thread2] createCollection: config.system.sessions with generated UUID: e981a966-4f87-45a9-9adb-6b7e2fdb8803**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] build index on: config.system.sessions properties: { v: 2, key: { lastUse: 1 }, name: "lsidTTLIndex", ns: "config.system.sessions", expireAfterSeconds: 1800 }**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] building index using bulk method; build may temporarily use up to 500 megabytes of RAM**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] failed to create WiredTiger bulk cursor: Device or resource busy**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] falling back to non-bulk cursor for index table:index-12--7471022903774062594**

**2018-08-28T14:19:39.278+0530 I INDEX [thread2] build index done. scanned 0 total records. 0 secs**

I have gone through your error log and find out the MongoDB Bug affected version error , which are able to see in your error log such as:

  "mongodb failed to create WiredTiger bulk cursor: Device or resource
  busy"

As per MongoDB jira documentation here as it is Server Bug having affected version was 2.8.0-rc0.

The Bug fix version of MongoDB is 2.8.0-rc1.

And the second Bug error, which is

  2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got
  signal 1 (Hangup), will terminate after current cmd ends

As jira documentation here It's affected Bug in MongoDB version 3.0.12.

As per @RamonFernandez from jira documentation, seems like mongod is receiving the SIGHUP signal and terminating, which is expected behavior. You need to check who or what is sending this signal.


How is this SQL Server PK violation possible? I'm getting an error that I thought was impossible. I have a SQL Server (ver 11.0.6594) table that looks like this:

CREATE TABLE [IDMaster](
  [ID] [int] NOT NULL,
 CONSTRAINT [PK_IDMaster] PRIMARY KEY CLUSTERED
([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]

It's basically supposed to do what the IDENTITY does now (it's been around a while) – it is used to get new unique integer values, that are used in a few other tables.

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table – all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it is getting one.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

I don't know how this is possible, but apparently it is; because it just happened.

Imagine any crazy, odd mixture of other ways to try generating a new ID value from this same table, and they probably aren't far off from what exists in this system. But I can't think of any way that they could cause that particular error. It was my understanding that the transaction locking used by SQL Server enforces that, within one SQL statement that doesn't have (NOLOCK) on it or anything, there can't be any interference with the rows that it is referencing.

The only other factor, which I don't think could be related but who knows, is that this SQL is wrapped in a TRY, with some options set. Here's the SQL that got that error:

SET XACT_ABORT ON;
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRY

INSERT INTO IDMaster (ID)
 SELECT COALESCE(
  -- make sure the new value is odd
  CASE WHEN MAX(COALESCE(ID,0)) % 2 = 0
   THEN MAX(COALESCE(ID,0)) + 1
   ELSE MAX(COALESCE(ID,0)) + 2
   END,1)
 FROM IDMaster;

END TRY
BEGIN CATCH
  WHILE (@@TRANCOUNT > 0) ROLLBACK;
  THROW;
END CATCH
WHILE (@@TRANCOUNT > 0) COMMIT;

Please explain:

How is it possible to get that error?
If possible, how can I prevent it from ever happening (without altering the table)?

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.

It definitely IS possible. This is due to your isolation level and how locking works.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

This is due to locking; initially in the read committed isolation level will take a shared lock on the (in this case) key while reading what the maximum value is. After it figures it out, an X lock will be needed to insert into the table. That's the simple overview of it.

When you have a single session, this isn't a problem. When you have multiple sessions this is a huge problem as shared locks are compatible with each other. This means that multiple readers can obtain the same value.

  don't know how this is possible, but apparently it is; because it just happened.

See above.

So How Do You Fix It?

Don't try to be smarter than SQL Server. Use the available constructs that fit the need - in SQL Server 2012 (major version 11) has both identity and sequences. Use them.
Purposely kill your concurrency by either using serializable or lock hints such as XLOCK. This will on purpose block other sessions which means you'll have less performance... and you're doing it on purpose... so yeah that seems bad but a possible solution.
Pre-create values (again trying to be smarter than SQL Server) and use XLOCK + READPAST to get a little better concurrency from a different table. Blah. Let me reiterate #1, don't try to be smarter than SQL Server [in this case].

This is only a partial answer -- I figured out the "how to fix" thanks another DBA. The solution is to use

WITH (TABLOCK, HOLDLOCK)


Can someone explain why left joining two views in mysql is so slow? Here is a question I asked yesterday - https://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow.

I got a good answer that helped me but I don't understand why the LEFT JOIN is so much slower than the lookup.  The LEFT JOIN was 16 seconds - and I am pretty sure my tables are at least 90% optimized - and when doing the lookup it is just .14 seconds.   When I LEFT JOIN tables it is not this slow so why views?

According to the MySQL Documentation on Views

  Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

The first thing that must be realized about a view is that it produces a result set. The result set emerging from query invoked from the view is a virtual table because it is created on-demand. There is no DDL you can summon afterwards to immediately index the result set. For all intents and purposes, the result set is a table without any indexes. In effect, the LEFT JOIN you were executing is basically a Cartesian product with some filtering.

To give you a more granular look at the JOIN of two views, I will refer to a post I made last year explaining the internal mechanisms MySQL uses to evaluate JOINs and WHEREs (Is there an execution difference between a JOIN condition and a WHERE condition?). I will show you the mechanism as published in Understanding MySQL Internals (Page 172):

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

OK, it seems like indexes should be used. However, look closer. If you substitute word View for Table, look what happens to the mechanism's execution:

MECHANISM MODIFIED

Determine which keys can be used to retrieve the records from views, and choose the best one for each view.
For each view, decide whether a view scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the view scan becomes faster.
Determine the order in which views should be joined when more than one views is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused views from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

Every table (view) has no index. Thus, working with virtual tables, temp tables, or tables with no indexes really becomes indistinct when doing a JOIN. The keys used are just for JOIN operations, not so much for looking things up faster.

Think of your query as picking up two phone books, the 2014 Yellow Pages and the 2013 Yellow Pages. Each Yellow Pages book contains the White Pages for Residential Phone Numbers.

In late 2012, a database table was used to generate the 2013 Yellow Pages.
During 2013

People changed phone numbers
People received new phone numbers
People dropped phone numbers, switching to cell phone

In late 2013, a database table was used to generate the 2014 Yellow Pages.

Obviously, there are differences between the two Phone Books. Doing a JOIN of database tables to figure out the differences between 2013, and 2014 should pose no problem.

Imagine merging the two phone books by hand to locate differences. Sounds insane, doesn't it? Notwithstanding, that is exactly what you are asking mysqld to do when you join two views. Remember, you are not joining real tables and there are no indexes to piggyback from.

Now, let's look back at the actual query.

SELECT DISTINCT
viewA.TRID,
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

You are using a virtual table (table with no indexes), viewA, joining it to another virtual table, viewB. The temp table being generated intermittently would be as large as viewA. Then, you running an internal sort on the large temp table to making it distinct.

EPILOGUE

Given the internal mechanisms of evaluating JOINs, along the transient and indexless nature of the result set of a view, your original query (LEFT JOIN of two views) should be getting running times that are orders of magnitude. At the same time, the answer you got from StackOverflow should perform well, given the same JOIN algorithm I just described.

I hope the gory details I just posted answers your question as to why.

The answer has to do with the method of performing each of these operations.

Since views are inherently unindexed, JOIN operations using fields from views will take longer than JOIN operations using tables since the scan cannot use an index.

In this case, the lookup also limits the number of records that have to be returned in processing- it only pulls records from one view that don't exist in the other. The JOIN pulls all records and then checks to see if records exist in both.


SQL Agent jobs in Basic Availability Group setup I have the following scenario on SQL Server 2016 Standard Edition 13.0.4411.0 (SP1-CU1):

2 database servers (DBNode1 and DBNode2). I have a database in a Basic Availability Group, with DBNode1 being the Primary and DBNode2 being the Secondary. As expected, if I try connecting to the DB on DBNode2, it fails as expected with the "The target database, ..., is participating in an availability group and is currently not accessible for queries" error

I've created a SQL Server Agent Job on DBNode2 only (the Secondary) to perform a dummy UPDATE on the database every minute as a test and expected this to fail as that DB should not be accessible on that Secondary node.

UPDATE dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

The "Database" option for the job step is set to my DB.
However, it succeeds and has actually performed the operation on the Primary node. (See images 1 and 2 below)

If I change the Job to target the master database, and use the following TSQL command structure, this does then error as I'd expect, being the Secondary node (See images 3 and 4 below).

UPDATE MyDB.dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

It seems like behind the scenes, despite targetting the local server, this is actually working out that the target DB is in an Availability Group and so connects to the Primary node and runs the command there. Is this normal / documented behaviour?

UPDATE:

Added screenshots to show the setup, to avoid confusion.

1 - original job setup showing the unexpected behaviour

2 - original job history output, showing the successful execution

3 - revised job setup showing the expected behaviour

4 - revised job history output, showing the failed execution as expected

Sadly, I'm going to argue that you're just 'scratching the surface' in terms of some of the complexity associated with SQL Server Agent Jobs and some of the complexities you face with Availability Groups (AGs). And, I say "sadly" because Microsoft has actually done a VERY poor job of addressing these concerns to the point where there's virtually no guidance from them.

So, for example - assume you get this working as expected and then you've got a job set to run at the same time on both servers and it'll automatically run on the current/correct replica (depending upon which is the primary) - great/perfect. Until someone comes in and disables the job or changes the schedule - but only makes that change on ONE server (not both) - and then there's a failover. Now what? (Or, in other words, if it's important that a job run (or NOT run) then it's important that whatever changes are made to the job should be synchronized between one server and the next (in a 2-node AG) and, yet, it's incredibly easy to let jobs like this get out of sync - even when there's only 1 'DBA' managing the servers.

As such, I spewed out a set of blog posts on all of this a while back:
http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-27-options-and-concerns-more-advanced-dep

The blog posts are VERY MUCH a 'stream of consciousness' sort of affair - and there are a couple of mis-steps in the posts and things are LESS insightful than I'd like. Still, by skimming through those posts, you can gain some great insights (i think) on some of the problems to expect and OPTIONS for working around those (with various pros/cons configured).

Eventually, I'll rewrite that set of posts into something more concise (and more 'wise' - i.e., that benefits from the number of AGs I've worked with since those posts - and all of the changes I've made). That said, here's a rough overview of what I do now to address these details:

I fully use the UDFs that determine whether the replica is the principal or not (either based on db-name or ag-name).
Rather than use the 'job state table' I outlined (as that's too clunky), jobs are either a) run as 'server-level' jobs (backups, server event logs/history maintenance/etc.) or b) assigned a 'disabled category' - meaning the job is/should be disabled (and then my code checks to make sure 'disabled' jobs are disabled and so on) or c) set to a SQL Server Agent Job category where the NAME of the category = the name of the AG they're in - so that when failover happens, code traps said failover and responds on the new primary by enabling all jobs of categoryname = 'ag-name' and doing the opposite on the secondary (if it's still up).
Still running synchronization checks (vs server-level details and jobs) every x minutes - to catch situations where something changes on one server - but not the other. (I don't bother letting CODE try and sync the differences - instead, it just reports that something is different.)


Select a distinct row I have a MySQL table:

o_id | Salesperson |   fax   | Pincode
1      Sandy         2110271   12011
2      Sandy         2452356   24523
3      Ricky         1023564   45510

I need to get the following result:

Salesperson |   fax   | Pincode
Sandy         2452356   24523
Ricky         1023564   45510

(Note: I don't need o_id column to be displayed)

I have tried:

select distinct salesperson,faxno,pin
from OPPORTUNITY

and:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By (salesperson)

...but still I am getting:

Salesperson |   fax   | Pincode
Sandy         2110271   12011
Ricky         1023564   45510

I have tried a lot of scenarios present on the web but still not able to achieve the expected result.

Is there any property or function that will give "Sandy" -  whose o_id is max.

Example:

Select distinct sales, faxno, pincode
from opportunity where o_id= max(o_id)

Although above code is not working, but anything like that can help?

The row:

Sandy         2452356   24523

is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via:

select max(o_id), salesperson
from T
group by salesperson

An important property of SQL is that it is closed, i.e. a result of a query is a relation (table). So we can simply join this with the original table:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id
   and x.salesperson = y.salesperson

Even if question does not say so explicitly, there is reason to believe that salesperson is functionally dependent of o_id. If that is the case it is sufficient to join by o_id alone:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id

In SQL2003, window functions were introduced. They are a perfect fit for this kind of query. I have heard the next version of MySQL will support them, but I guess you are not using that.

You also mention this construction:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By salesperson

It is probably the construction in MySQL that causes the most confusion. It is invalid in most (all?) other products, because the result is non-deterministic or random. What happens is that MySQL randomly picks a faxno, pincode for each salesperson.

You can solve this using ROW_NUMBER() or RANK()

SELECT  Salesperson,fax,Pincode
FROM    (
    SELECT  Salesperson,fax,Pincode,
            ROW_NUMBER() OVER(PARTITION BY Salesperson ORDER BY o_id DESC) AS [ROW_NUMBER]
    FROM    OPPORTUNITY
)   T
WHERE   [ROW_NUMBER]    =   1


SSAS Tabular: ImpersonationMode that is not supported for processing operations I have a SQL 2016 SP1 SSAS Tabular Instance. I've deployed a model with the following properties

When I try to process the database or a table I get an error "The datasource contains an ImpersonationMode that is not supported for processing operations".

But if I change the impersonation info on the connection properties to use the service account instead of the current user it works fine.

We also don't get this issue if we change the Default Mode to DirectQuery instead of import, but we need to use Import because we need to use DAX username function for row level security.

I am an admin on the SSAS instance and also an admin on the SQL Server instance that is the data source. Why can't I process the SSAS tabular model as my user?

I agree the documentation on this is a bit terse, but the error message on the other hand is very clear. ImpersonateCurrentUser is not supported for models attached to a SSAS instance.

This is mentioned in the documentation of the ImpersonationMode Enumeration:

  ImpersonateCurrentUser: Not supported for tabular model databases attached to an Analysis Services instance.

As well as in the documentation on Impersonation (SSAS Tabular) under the Options header:

  When configuring impersonation, or when editing properties for an
  existing data source connection in Analysis Services, you can specify
  one of the following options:

  ImpersonateWindowsUserAccount
  ImpersonateServiceAccount

Change Current User for Service Account under impersonalization of the conection


Recursive CTE to find Total for all children Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part.

Meaning if I search for 'Rivet', I want to know the total count at each level within the assembly, not just the direct children count.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Currently, I can get the direct parents, but want to know how to extend my CTE to allow me to roll this information upward.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

Creation script

CREATE TABLE [dbo].[Instances] (
  [InstanceID] NVARCHAR (50) NOT NULL,
  [Part] NVARCHAR (50) NOT NULL,
  [ParentID] NVARCHAR (50) NOT NULL, );

INSERT INTO Instances
Values
  (1, 'Assembly', 0),
  (50, 'Rivet', 1),
  (50, 'Rivet', 1),
  (2, 'SubAssembly', 1),
  (50, 'Rivet', 2),
  (51, 'Bolt', 2),
  (51, 'Bolt', 2),
  (3, 'SubSubAssembly', 2),
  (50, 'Rivet', 3),
  (50, 'Rivet', 3),
  (4, 'SubAssembly2', 1),
  (50, 'Rivet', 4),
  (51, 'Bolt', 4)

This recursive CTE (SQL Fiddle) should work with your sample:

WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*)
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

Output

ParentID    Count
1           6
2           3
3           2
4           1

Note: You mentioned in comments that the question only contains a simplified sample table and real data have proper indexes and handle duplicates and data adequately.

Data used (SQL Fiddle):

DECLARE @Instances TABLE(
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;

I'm not sure I understand what you mean by "amount" and where table (?) PartInstances and columns id and count come from in your sample but I calculated what I guess from your sample data.

;with ins as (
select [InstanceID], [Part],[ParentID],0 lvl
from instances where ParentID=0
union all
select i.[InstanceID], i.[Part],i.[ParentID], lvl+1
from instances i
inner join ins on i.parentid=ins.InstanceID
)
select InstanceID,part,COUNT(*) cnt
from ins
group by instanceid,part

I hope this will give you some ideas.

Update

I understand that this is a test example but your data breaks everything starting from 1NF. Most probably your table should be break in two and normalized.


MySQL - Deduct from two columns with a remainder I have the following table:

Id,  balance, otherBalance
=============================
1,     100,      50

I would like to create an update query that receives a parameter 'amount' and does the following:
deduct from both balances columns the entire amount. Starting with the 'balance' column first, and only when it is depleted, deduct the remainder from the 'otherBalance'.

Examples

Executing update(98), will result in:

Id,  balance, otherBalance
=============================
1,     2,      50

Executing update(102), will result in:

Id,  balance, otherBalance
=============================
1,     0,      48

Executing update(151), will result in no change since both balances are less than the amount to be deducted.

You can use the variables to do the trick:

UPDATE table AS w
  JOIN amounts AS z ON z.w_Id = w.ID
   SET w.balance = IF( (@remainder := w.balance - z.amount) >= 0, @remainder, 0 )
     , w.otherBalance = IF( @remainder >= 0, w.otherBalance, w.otherBalance + @remainder )
;

Here I suppose that amount is fetched from the another table amounts and perform the mass update for all the rows at once.

If the amount is bigger than balance, then @remainder becomes negative therefore I've added it to the otherBalance instead of subtracting it.

I had a go at this - thanks to a MySQL "feature" (albeit documented - see comments), it was tricky!

I was trying to solve this using pure SQL - it's relatively easy to do it in procedural code. But it can be done using SQL IF you have the update amount parameter in another table!

First I did this:

CREATE TABLE my_tab (my_id INTEGER, balance_1 UNSIGNED, balance_2 UNSIGNED);
                           -- the reason for `UNSIGNED` is dealt with below!
INSERT INTO my_tab VALUES (1, 100, 50);

CREATE TABLE my_update_tab (update_amount INTEGER);
INSERT INTO my_update_tab VALUES (98);

And then I wrote the following SQL:
(check out the dbfiddle.com here)

UPDATE my_tab
SET
  balance_2 =
    CASE
      WHEN (SELECT update_amount FROM my_update_tab) < balance_1 THEN balance_2
      WHEN (SELECT update_amount FROM my_update_tab) > balance_1 THEN (balance_2 + balance_1 - (SELECT update_amount FROM my_update_tab))
  END,
  balance_1 =
    CASE
      WHEN (SELECT update_amount FROM my_update_tab) < balance_1 THEN balance_1 - (SELECT update_amount FROM my_update_tab)
      WHEN (SELECT update_amount FROM my_update_tab) > balance_1 THEN 0
  END;

And then I ran:

SELECT * FROM my_tab;

To get the following (correct but see comments) result:

my_id   balance_1   balance_2
    1           2          50

Please see the comments to this post - there is a problem with MySQL (IMHO, still a bug). Thanks to ypercube for helping me complete this answer!

The MySQL Server doesn't appear to be aware that an UPDATE on one table even to 2 (or many) fields is normally a single transaction which is why balance_2 is updated before balance_1 - totally contrary to any sort of logic - this cost me a whole bunch of time!

PostgreSQL (again, surprise surprise) gets it right - see the dbfiddle here! (1, 2, 50) And this one (1, 0, 10) - where amount is bigger than balance!

Maybe you should consider a decent RDBMS  (hint, PostgreSQL!) - one that obeys Codd's rules and other such logical concepts! :-) As a final point about MySQL - it doesn´t have CHECK constraints - so you have to use UNSIGNED so they can't go below zero - your transaction will fail if there´s any attempt to do so. The changes suggested by ypercube have been incorporated into the db-fiddle and this answer!


Why can't I use a CASE statement to see if a column exists and not SELECT from it? Why does something like this not work?

SELECT
CASE
WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL
ELSE Somecol
END AS MyTest
FROM Customers;

I am just checking if the column exists, however, SQL Server complains about Somecol not existing.  Is there an alternative to this in a single statement?

The following query uses the same idea as in this amazing answer by ypercube:

SELECT x.*
FROM (SELECT NULL AS SomeCol) AS dummy
CROSS APPLY
(
  SELECT
    ID,
    SomeCol AS MyTest
  FROM dbo.Customers
) AS x;

It works like this:

if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol;
if the table has no such column, the reference will still be valid, because now it will be resolved as dummy.SomeCol: dummy columns can be referenced in that context.

You can specify multiple "spare" columns that way. The trick is not to use the table alias for such columns (which is a frowned-upon practice in most situations, but in this case omitting the table alias helps you to resolve the issue).

If the table is used in a join and the other table has its own SomeCol, you will probably need to use the above query as a derived table before using it in the join in order to keep the trick working, something like this:

SELECT ...
FROM
(
  SELECT x.*
  FROM (SELECT NULL AS SomeCol) AS dummy
  CROSS APPLY (
    SELECT
      ID,
      SomeCol AS MyTest
    FROM dbo.Customers
  ) AS x
) AS cust
INNER JOIN ...
;

If I understood it correctly...

You can use the query something like below and act accordingly based on the count... If the count is  > 1 then it means you have the col in that table, and the count = 0 then you don't have that col in that table

SELECT count(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('Id')
        AND TABLE_SCHEMA='dbo' and TABLE_NAME ='UserBase' ;


Is there any (hidden) built-in function on MS-SQL to unquote object names? Sometimes I store object names (identifiers) in some of our databases, for example in some parameter tables. Because I select records from these tables using the '=' or 'LIKE' comparison operators, I must take care to store these names always with or without brackets.

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

or

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

However MS-SQL has some functions where you can use object names with or without brackets, for example the OBJECT_ID() function. I've set up a minimal example on dbfiddle.uk.

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Now I can use OBJECT_ID() to check if the table TEST exists in this way:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

It doesn't matter if I pass the identifier TEST with or without brackets, parser is smart enough to remove the brackets.

Well, I can simulate this by adding a scalar function that remove brackets from one string:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']',
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2),
                   @TXT);
    END;
GO

And then use it in this way:

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT
FROM   TEST
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2
 3 | obj3

SELECT ID, dbo.UNQUOTENAME(OBJECT)
FROM   TEST
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4

But my question is:

Is there any hidden built-in function that removes brackets using T-SQL?

dbfiddle here

Sometimes I store objects names in some of our databases
I must take care to store this names always with or without brackets.

An "object name" is technically called an identifier.  In some contexts an identifier will be appear TSQL code surrounded by [ and ] or " and ".  These characters are not part of the identifier, and you should never store them.
Instead store the identifier as a nvarchar(128) (or sysname), and add the delimiters at runtime using the QUOTENAME function.
The inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.
Note that QUOTENAME has an optional second parameter, and if you specify a single quote character for that parameter QUOTENAME does not create a valid delimited identifier expression.  It emits a varchar literal expression.

When square brackets are needed -- it is because your identifier is already a reserved keyword. Using them arbitrarily is not only not necessary, it leads to much confusion as you can see.

In my opinion, the best way is to avoid using reserved identifiers in the first place.


Find "n" consecutive free numbers from table I have some table with numbers like this (status is either FREE or ASSIGNED)

id_set  number  status
-----------------------
1       000001  ASSIGNED
1       000002  FREE
1       000003  ASSIGNED
1       000004  FREE
1       000005  FREE
1       000006  ASSIGNED
1       000007  ASSIGNED
1       000008  FREE
1       000009  FREE
1       000010  FREE
1       000011  ASSIGNED
1       000012  ASSIGNED
1       000013  ASSIGNED
1       000014  FREE
1       000015  ASSIGNED

and I need to find "n" consecutive numbers, so for n = 3, query would return

1       000008  FREE
1       000009  FREE
1       000010  FREE

It should return only first possible group of each id_set (in fact, it would be executed only for id_set per query)

I was checking WINDOW functions, tried some queries like COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), but that's all I got :) I couldn't think of logic, how to do that in Postgres.

I was thinking about creating virtual column using WINDOW functions counting preceding rows for every number where status = 'FREE', then select first number, where count is equal to my "n" number.

Or maybe group numbers by status, but only from one ASSIGNED to another ASSIGNED and select only groups containing at least "n" numbers

EDIT

I found this query (and changed it a little bit)

WITH q AS
(
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
         ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,
       MIN(number) AS first_number,
       MAX(number) AS last_number,
       status,
       COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
         rnd - rn,
         status
ORDER BY
     first_number

which produces groups of FREE/ASSIGNED numbers, but I would like to have all numbers from only first group which meets the condition

SQL Fiddle

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here's a SQL Fiddle demo* link for this query: http://sqlfiddle.com/#!1/a2633/1.

UPDATE

To return only one set, you could add in one more round of ranking:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
),
ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY id_set, grp) AS rnk
  FROM counted
  WHERE cnt >= 3
)
SELECT
  id_set,
  number
FROM ranked
WHERE rnk = 1
;

Here's a demo for this one too: http://sqlfiddle.com/#!1/a2633/2.

If you ever need to make it one set per id_set, change the RANK() call like this:

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

Additionally, you could make the query return the smallest matching set (i.e. first try to return the first set of exactly three consecutive numbers if it exists, otherwise four, five etc.), like this:

RANK() OVER (ORDER BY cnt, id_set, grp) AS rnk

or like this (one per id_set):

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

* The SQL Fiddle demos linked in this answer use the 9.1.8 instance as the 9.2.1 one doesn't appear to be working at the moment.

CREATE TABLE #ConsecFreeNums
(
     id_set BIGINT
    ,number VARCHAR(10)
    ,status VARCHAR(10)
)

CREATE TABLE #ConsecFreeNumsResult
(
     Seq    INT
    ,id_set BIGINT
    ,number VARCHAR(10)
    ,status VARCHAR(10)
)

INSERT #ConsecFreeNums
SELECT 1, '000002', 'FREE' UNION
SELECT 1, '000003', 'ASSIGNED' UNION
SELECT 1, '000004', 'FREE' UNION
SELECT 1, '000005', 'FREE' UNION
SELECT 1, '000006', 'ASSIGNED' UNION
SELECT 1, '000007', 'ASSIGNED' UNION
SELECT 1, '000008', 'FREE' UNION
SELECT 1, '000009', 'FREE' UNION
SELECT 1, '000010', 'FREE' UNION
SELECT 1, '000011', 'ASSIGNED' UNION
SELECT 1, '000012', 'ASSIGNED' UNION
SELECT 1, '000013', 'ASSIGNED' UNION
SELECT 1, '000014', 'FREE' UNION
SELECT 1, '000015', 'ASSIGNED'

DECLARE @id_set AS BIGINT, @number VARCHAR(10), @status VARCHAR(10), @number_count INT, @number_count_check INT

DECLARE ConsecFreeNumsCursor CURSOR FAST_FORWARD FOR
SELECT
       id_set
      ,number
      ,status
 FROM
      #ConsecFreeNums
WHERE id_set = 1
ORDER BY number

OPEN ConsecFreeNumsCursor

FETCH NEXT FROM ConsecFreeNumsCursor INTO @id_set, @number, @status

SET @number_count_check = 3
SET @number_count = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @status = 'ASSIGNED'
    BEGIN
        IF @number_count = @number_count_check
        BEGIN
            SELECT 'Results'
            SELECT * FROM #ConsecFreeNumsResult ORDER BY number
            BREAK
        END
        SET @number_count = 0
        TRUNCATE TABLE #ConsecFreeNumsResult
    END
    ELSE
    BEGIN
        SET @number_count = @number_count + 1
        INSERT #ConsecFreeNumsResult SELECT @number_count, @id_set, @number, @status
    END
    FETCH NEXT FROM ConsecFreeNumsCursor INTO @id_set, @number, @status
END

CLOSE ConsecFreeNumsCursor
DEALLOCATE ConsecFreeNumsCursor

DROP TABLE #ConsecFreeNums
DROP TABLE #ConsecFreeNumsResult


Executing generated script from output I created a script that generates and outputs the required ALTER SYSTEM KILL SESSION commands that I need to execute.

main script: (without all the conditions, for better use here in SE)

SELECT 'ALTER SYSTEM KILL SESSION '||CHR(39)|| ao.SID ||',' || ao.SERIAL# ||CHR(39) || ' IMMEDIATE;'
FROM v$session ao where (length(trim(ao.username)) = 5
                and upper(substr(trim(ao.username),1,1)) = 'E'

output example:

ALTER SYSTEM KILL SESSION '418,30413' IMMEDIATE;
ALTER SYSTEM KILL SESSION '755,19371' IMMEDIATE;
ALTER SYSTEM KILL SESSION '809,6715' IMMEDIATE;

How can I execute the generated ALTERs ?

I'm using Oracle SQL Developer 4.0.0.13

You'd copy and paste the commands and then run them.  If you want to do this in a single step, you can write a small PL/SQL block

BEGIN
  FOR s IN (SELECT *
              FROM v$session ao
             where (length(trim(ao.username)) = 5
               and upper(substr(trim(ao.username),1,1)) = 'E')
  LOOP
    EXECUTE IMMEDIATE  'ALTER SYSTEM KILL SESSION '||
                         CHR(39)|| s.SID ||',' || s.SERIAL# ||CHR(39) ||
                         ' IMMEDIATE';
  END LOOP;
END;

Use this button - it executes the output as as script.


Two instances sharing same system database? I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default instance on DATA-SVR, and a named instance on DATA-SVR\Product.

When trying to investigate a problem on the system, I checked the data link files on all workstations, and saw that some users were connecting to DATA-SVR, and others were connecting to DATA-SVR\Product. They were able to do this, because both instances have exactly the same databases. Not good, to say the least.

However, when I checked the database paths between the two instances, they are both pointing at the same data files. I checked that this was the case by creating a table in DATA-SVR, and then accessing the same table from DATA-SVR\Product. They're definitely pointing at the same data, so catastrophic failure was averted. However, to clean things up, I tried detaching the databases from the DATA-SVR\Product instance so that there was only one instance to connect to, but this also detached the database from the default instance.

We have a service running on the server, which for some reason is only able to access the named instance, and not the default instance, so I've now changed all users to use the named instance. Everything is working now. However, I'm still uneasy about how this situation could have come about.

Can anyone shed some light on what is happening here, and how to fix it (preferably without reinstalling the database server)?

This happened to me once when I restore system database of SQL 2014. When I investigate further, the 2 different name was pointing to same physical file and from @@version I can see both having similar naming.
The database which I restored was pointing to another database and it got fixed when I revert back from single user mode to multi user.


How to use psql with no password prompt? I wrote a script to REINDEX indexes in a database. Here is one of them:

echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX scm_main.unq_vbvdata_vehicle;"
if [[ ${?} -eq 0 ]]; then
    echo "reindex for unq_vbvdata_vehicle finished at: `date "+%F %T"`" >> ${LOG_FILE}
else
    echo "reindex for unq_vbvdata_vehicle failed" >> ${LOG_FILE}
    exit 1
fi

The problem is I can not run this script in standalone mode. psql is prompting password every time it runs. There is also two limitations:

I can not create a user on database with no password.
Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?

You have four choices regarding the password prompt:

set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html
use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
use a connection URI that contains everything: http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532

Very helpful answers in this thread. I'm just adding this for ubuntu 18.04:

sudo PGPASSWORD=yourpasswordHere -u postgres psql

This will take you into the postgres without the password prompt, without having to set any environment variables. This is not a permanent setting.


Specified key was too long; max key length is 1000 bytes in mysql 5.6 one of the application server is internally creating the database on my mysql  but when ever the following create table command gets executed

 CREATE TABLE ofRoster (

    rosterID              BIGINT          NOT NULL,
    username              VARCHAR(64)     NOT NULL,
    jid                   VARCHAR(1024)   NOT NULL,
    sub                   TINYINT         NOT NULL,

    ask                   TINYINT         NOT NULL,
    recv                  TINYINT         NOT NULL,
    nick                  VARCHAR(255),
    PRIMARY KEY (rosterID),
    INDEX ofRoster_unameid_idx (username),
    INDEX ofRoster_jid_idx (jid)

 )

i am getting following error

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

i have set my default engine to MyISAM because i was getting following error in InnoDB

specified key was too long max key length is 767 bytes

my current engines are as follows

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

now i really don't know how would i get rid off it as the application server itself is creating database automatically in mysql so i dont have control over it.

i am using Server version: 5.6.10 MySQL Community Server (GPL) version

character set of column

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.10-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you'll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

  I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn't the case, in this case -- InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)... so either they've documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.

COMPRESSED doesn't truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition.  Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10.  If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

...but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.

I don't see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is...

...there's not a readily available workaround here.  My other thought was "character sets" but the difference between latin1 and utf8 still isn't sufficient to explain 1,024 vs 1000 or 767.  I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.

Try using partial index

ALTER TABLE ofRoster ADD INDEX (jid(32));

You would want to investigate the size of the index, the improvement, and then the amount of additional reads necessary.