Query Each Database in A Table We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the database(s) that threw an error code.

I have been toying with this syntax; however, it will not execute, I keep getting:

  Msg 422, Level 16, State 4, Line 9
  Common table expression defined but not used.

This is my syntax -- what would be my best way to accomplish this?

Scan the table [dbo].[FailingDatabase] and get the values from the two fields datefaield, database
Execute the Select Statement to produce a result set of the failed databases
Generate an email (I can do this element, this part is no issue)

Here is DDL for my table FailingDatabase:

Create Table [dbo].[FailingDatabase]
(
  datefailed datetime
  ,[database] varchar(1000)
)

Insert INTO [dbo].[FailingDatabase] VALUES
('2016-03-18 08:29:00.000', 'server.database.dbo.tablename')
,('2016-03-18 08:35:00.000', 'server.database.dbo.tablename')

I am trying to use a CTE to query the databases, as I set a flag in the email based off if I can query the database. If it queries succesfully, then I know the database is still up and running. If I am unable to query the database, then it is offline for whatever reason, and I need to immediately try to remedy.

Here is my un-working syntax...

declare @fullsyntax varchar(MAX)
;with faileddb
as
(
  Select Top 2 [databasename] FROM [dbo].[FailingDatabase]
)
select @fullsyntax += 'Select datefaield, database FROM ' + [databasename] + 'from faileddb; '
Print @fullsyntax

Could I also add a where clause like:

FROM '+[databasename]+'
WHERE databasename <> ''Master'';'
from faileddb;

Your dynamic SQL has an error.  Try this:

IF COALESCE(OBJECT_ID('dbo.FailingDatabase'), 0) = 0
Create Table [dbo].[FailingDatabase]
(
    DateFailed datetime
    , DatabaseName varchar(1000)
);
TRUNCATE TABLE dbo.FailingDatabase;
Insert INTO [dbo].[FailingDatabase] (DateFailed, DatabaseName)
VALUES ('2016-03-18 08:29:00.000', 'server.database1.dbo.tablename')
    , ('2016-03-18 08:35:00.000', 'server.database2.dbo.tablename');

DECLARE @fullsyntax VARCHAR(MAX);
SET @fullsyntax = '';
;WITH faileddb
AS
(
    SELECT TOP (2) databasename --why you want TOP(2) is unclear
    FROM dbo.FailingDatabase
)
SELECT @fullsyntax = @fullsyntax
    + CASE WHEN @fullsyntax = ''
        THEN ''
        ELSE CHAR(13) + CHAR(10)
        END
    + 'SELECT datefaield, database FROM ' + [databasename] + ';'
FROM faileddb
WHERE faileddb.DatabaseName NOT IN (
    'master'
    , 'tempdb'
    );
PRINT (@fullsyntax);


Unable to connect to oracle user I have hit a rather frustrating snag in that after installing Oracle 18c.

I have been unable to successfully create a user and then connect to it.

More precisely I can create the user but the connection keeps failing. The commands I’m using are below along with the failed connection attempt. If you have any suggestions it would be greatly appreciated.

I first connect to Oracle as root and then connect to the container XEPDB1

sqlplus system/password

SQL>ALTER SESSION SET CONTAINER = XEPDB1;

I then do the following to create a user.

SQL>create user paul identified by Guardium111;

SQL>grant connect to paul;

SQL>grant create session to paul;

SQL>alter user paul default role all;

Everything above works without error. When I try to connect to user paul I get the following. I’m tearing what little is left of my hair out trying to understand what the issue is.

SQL> conn paul

Enter password:

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Discovered what the problem was.

sqlplus SYS/guardium111@XEPDB1 AS SYSDBA
sqlplus paul/guardium111@XEPDB1

OR

conn paul/guardium111@XEPDB1


Slow delete caused by many foreign keys My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the database, this makes for a very slow delete process when purging old user records.

Our delete query first updates all of the Updated/Added references to the current user and then deletes the user record. The execution plan shows table scans for each related table due to the foreign key. See this post as an example of what we are facing.

The pointers back to added and updated are not really useful other than for analysis during application problems. They are rarely used, and aren't a primary source of good information when they are. I'm wondering if I should remove the foreign key constraints all together or if I should possibly move the auditing of updates to a separate table that keeps a record of table name, column, value, and user id, or if there is some other generally accepted approach that the DBA community gravitates toward for this sort of situation.

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)'
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )


Best Way to Index an INT Column Used for BitWise Queries What is the best way to index a column for an efficient look-up based on a bitwise comparison in the WHERE clause e.g.

SELECT ID, FLAGS, NAME FROM TABLE WHERE FLAGS & 4 = 4

I am in a SQL server 2012 environment.

I understand that an index on the FLAGS column won't be used in this type of query.

I know the other option is to break the FLAGS column out into individual columns, but I wanted to avoid that because in some cases, that would mean 25+ new columns, each of which would need a combination of indexes to support the common queries.

Are there any ways to index the single column so that the bitwise queries are more efficient?

I tried using a computed column with an index as suggested by dudu below, but when I used in on a table which has a clustered index, and start selecting columns (instead of just select count(*) it no longer uses the index on that new computed column.

SELECT
*
INTO
    MYTABLE
FROM
(
    SELECT 'A' COL_A, 'A' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'B' COL_A, 'D' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'C' COL_A, 'S' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'D' COL_A, 'F' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'E' COL_A, 'T' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'F' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'G' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'H' COL_A, 'X' COL_B, CAST(128 AS INT) FLAGS
) SRC

CREATE CLUSTERED INDEX IX_CLU_A ON MYTABLE(COL_A)

ALTER TABLE MYTABLE ADD FLAG_BIT_1 AS (CAST ((FLAGS & 1) AS BIT))
CREATE INDEX MYTABLE_IX_FLAG_BIT_1 ON MYTABLE (FLAG_BIT_1)

SELECT * FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT * FROM MYTABLE WHERE FLAG_BIT_1 = 1

SELECT COUNT(*) FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT COUNT(*) FROM MYTABLE WHERE FLAG_BIT_1 = 1

Index on computed (AKA virtual) column

Demo

with t(i) as (select 0 union all select i+1 from t where i<999)
select 1+t0.i+1000*t1.i as FLAGS into MyTable from t t0,t t1 option (maxrecursion 0)

alter table MyTable add flag_bit_2 as (cast ((FLAGS & 4) as bit))
create index MyTable_ix_flag_bit_2 on MyTable (flag_bit_2)

select count(*) from MyTable where flag_bit_2 = 1


What happens if one DC goes down in Cassandra multi DC setup? We have a Cassandra multi DC setup with 3 nodes in each DC, enabled with NetworkTopologyStrategy and RF 3.

The java client interacts with Cassandra cluster using read and write consistency as LOCAL_QUORUM.

We have our service in each of these DC (same service, just config changes), so that LOCAL_QUORUM helps pick the co-ordinator in the same DC and responds back without waiting for other DC.

There is no interaction between the services running in these 2 DCs.

What would happen if one of the DC goes down completely and is not able to recover soon? Obviously, the service running in the same DC would be affected, but what would happen to the other DC which is still up? I guess the service won't be affected as it talks to local DC only, and think that the read/writes won't be affected even if they are not relayed to the other DC.

All writes are always happens to all DCs (see the diagram in DataStax Architecture guide).

But if the one DC is down, the nodes in the other DC will detect this, and will start to collect hints during the configured hints time window (3 hours by default, and cross-DC hints aren't disabled) - if servers in first DC comes back during this window, hints will be replayed


Set private key password for mysql using ssl 2016-05-09T20:05:05.772747Z 0 [ERROR] SSL error: Unable to get private key from 'server-key.pem'
2016-05-09T20:05:05.772764Z 0 [Warning] Failed to set up SSL because of the following SSL library error: Unable to get private key

My installation correctly locates the certificate files however it does not start SSL as it is unable to get the pass phrase for the private key.

How can I configure a passphrase in the mysql configuration?

I have tried removing the passphrase from the key but openssl on ubuntu 16.04 requires a minimum passphrase length of 4 characters.

I had to make sure that mysql owned the .pem files by cd to directory that contains the .pem files and:

chown -R mysql .
chgrp -R mysql .

After doing that the problem was solved.


Different Date format in predicate when comparing against a date or datetime I have the following query:

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26'

Which gives me the error

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

If I change the WHERE clause to

WHERE   MyDateTimeCol >= '2019-25-11' AND MyDateTimeCol < '2019-26-11'

This works.

However, I change my original query to the equivalent (performance issues aside)

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   CAST(MyDateTimeCol AS DATE) = '2019-11-25'

The query works without any errors

Why does casting the datetime as a date mean I have to use a different Datetime format in the predicate?

In your first example, you compare a datetime to a string. The format for that string is YYYY-MM-DD, meaning that the login's language will determine what is to be interpreted and month vs day. You seems to be in the UK, I assume a British login. Check out for instance sp_helplanguage you'll see how the "separated" datetime format is interpreted for various languages.

The "separated" format is, btw, not to be confused with the ISO 8601 format, which is YYYY-MM-DDTHH:MM:SS (the letter T is what makes the difference).

When you cast the column to the date type, you no longer compare the string to datetime, you compare it to the more modern date type. This will always interpret the "separated" format as ymd, and is in other words not dependent on the login's language.

If you were using the more modern datetime2, btw, you wouldn't have the problem in the first place, since it is handled as date.

See my datetime article for further elaboration: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes .


Select and Update MAC Addresses to Same Format I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;

+-------------------+
| macaddress        |
+-------------------+
| 90CCAADD3341      |
+-------------------+
| 90:3f:ff:11:22:33 |
+-------------------+
| 33:44:aa:bb:34:6a |
+-------------------+
| 8801abcd3231      |
+-------------------+

Is there any way I can bulk update all rows in this column so they are in the same format?

I'd like them all to be uppercase with no colons, see below;

+--------------+
| macaddress   |
+--------------+
| 90CCAADD3341 |
+--------------+
| 903FFF112233 |
+--------------+
| 3344AABB346A |
+--------------+
| 8801ABCD3231 |
+--------------+

How can I achieve this?

Test select:

select macaddress, upper(replace(macaddress,':','')) as new_macaddress
from Macs;

Update:

update macs
set macaddress = upper(replace(macaddress,':',''));

Fiddle

Test data:

create table macs
(
macaddress varchar(20)
);

insert into macs values('90CCAADD3341');

insert into macs values('90:3f:ff:11:22:33');

insert into macs values('33:44:aa:bb:34:6a');

insert into macs values('8801abcd3231');

Documentation links:

UPPER()

REPLACE()


Can not install a database, can I still learn though? I am given access to a domain where I can put my code in and it will go public, like in this example. In order to store the data right now, I am using PHP and I write the data to a file. When the page is loaded, I will read from file and display it in the page.

However, for teaching purposes, I would like to use a database, but I have no privileges, which would allow me to install a database. So, can I do something, that would still allow me to learn how to use a database && apply it to my page?

A negative answer would also be accepted.

I thought of SQLite in-memory, but it says that the database will delete itself, once the connection to the database is closed. What does that mean exactly? It means that it will delete itself when the user exits the page from his browser? This question gives me this feeling.

The easiest way to learn about SQL-Server is to install a SQL Server express.

Even without having an actual website attached to it, Microsoft has a few samples.
And if you want live environment to try out, and see what you can put together, Azure has a free trial.
Which you can use to set up a few vm's.

It's very hard to learn anything from a limited environment, every problem or fault might or might not be caused by something you have control over.
If you want to get the most out of it, set up your own environment.
Or contact whoever is in charge of your environment to see what can be arranged.


Length of LOB data (2135980) to be replicated exceeds configured maximum 65536 I am getting the replication error 'Length of LOB data (2135980) to be replicated exceeds configured maximum 65536'.

sp_configure was previously applied to set it to max value (-1) and a query on sys.configurations confirms that the max value (-1) is in use.

EXEC sp_configure 'max text repl size', -1 ;
RECONFIGURE;
GO

select * from sys.configurations
where name like 'max text repl size%';
GO

I have tried using a large value for 'max text repl size' rather than max value and turning it off and on again, to no avail.

Is there something else that may be overriding the 'max text repl size' setting or anywhere else I can look?

This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size.
Or you may want to try :

EXEC sys.sp_configure N'max text repl size (B)', N'-1'
GO
RECONFIGURE WITH OVERRIDE
GO


SQL Server Schema and login name I'm new to SQL Server, still struggling in understanding schema.

1.Is schema associated with login name?

Is login name the same as 'user name' under SQL Server Authentication?

3.Let's say I login as username 'ITSupport', so any table I created will be sth like ITSupport.TableName, so I don't need to prefix ITSupport to access TableName. So what's my default schema name, ITSupport or dbo

Is schema associated with login name?

No. A login is a server-level principal used to connect to the SQL Server instance. This does not infer any permissions or access to any databases unless a database user has been created and associated with the login, or high-privilege role membership (such as sysadmin) has been granted to the login.

  Is login name the same as 'user name' under SQL Server
  Authentication?

Not exactly. When you create a login (server-level principal) the field login name is used and can be either a SQL Authentication username or a Windows username (in the format DOMAIN\username). When you create a user (database-level principal) the user name field is used and represents the database username. This database-level principal can be linked to a server-level login or it can be a contained user that does not link to principals outside of the database. If it is contained, "user name" represents the username used to connect to the database as that principal. If it is linked to a server-level login then you still log in to SQL Server with the login name but that login is represented within the database as the username for the linked database principal.

  Let's say I login as username 'ITSupport', so any table I created will be sth like ITSupport.TableName, so I don't need to prefix ITSupport to access TableName. So what's my default schema name ITSupport or dbo

Default schema is set at the database user level and if your database user has permissions to create objects (such as membership in the db_ddladmin database role) and does not specify the schema then any objects created by that user where the schema is not specified will be created in the default schema for that user.

Note that a schema does not exist simply because a user was created. If you have not created a schema and set the default schema for your user, you will by default be using dbo and any non-prefixed object names will assume the schema is dbo.

The below script will create some example principals to illustrate the above points:

USE [master]
GO
-- Create a test database
CREATE DATABASE SecTest
GO

-- Create a login. This is a server-level principal that is used to connect to the SQL Server instance.
CREATE LOGIN [SecTestLogin] WITH PASSWORD=N'test1234!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SecTest
GO

-- Create a schema for this user.
CREATE SCHEMA [SecTestSchema]
GO

-- Create a database user and associate it with the server login. Note the difference in names and the default schema specification
CREATE USER [SecTestUser] FOR LOGIN [SecTestLogin] WITH DEFAULT_SCHEMA=[SecTestSchema]
GO

-- Permissions setting to allow user to create objects
ALTER AUTHORIZATION ON SCHEMA::[SecTestSchema] TO [SecTestUser]
GO

ALTER ROLE db_ddladmin ADD MEMBER SecTestUser
ALTER ROLE db_datareader ADD MEMBER SecTestUser
ALTER ROLE db_datawriter ADD MEMBER SecTestUser

The below script will highlight some of the points above:

-- LOGIN AS THE LOGIN CREATED EARLIER

-- Create some tables. Note the prefixed vs non-prefixed tables
CREATE TABLE TableCreate1 (ID INT)
CREATE TABLE dbo.TableCreate2 (ID INT)
CREATE TABLE SecTestSchema.TableCreate3 (ID INT)

-- Show the tables and the schema they're in. Note the schema for non-prefixed table TableCreate1
SELECT s.[name] AS [Schema], t.[name] AS [Table]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

-- Shows the database user and server login for the current user
SELECT USER_NAME() AS [Current Database Principal], SUSER_NAME() AS [Current Server Principal]


Does my table need a primary key and clustered index change? I have a table holding 1.7 Milion rows.

Definition:

CREATE TABLE T(
[ID] [uniqueidentifier] NOT NULL,
[AID] [uniqueidentifier] NOT NULL,
[BID] [uniqueidentifier] NOT NULL,
[iType] [int] NOT NULL,
[MT] [ntext] NOT NULL,
[isM] [tinyint] NOT NULL,
[CDate] [datetime] NOT NULL,
[CBy] [nvarchar](50) NOT NULL,
[xi] [tinyint] NOT NULL

There is no Primary Key and no clustered index. There is one non clustered index on a uniqueidentifier column (that is not the PK candidate).

Looking at the usage statistics I see the following:

           Seeks Scans Lookups Updates TotalKB  UsedKB
HEAP       0     2     1500    65000   1810736  1757128
Non Cl.IX  1500  0     0       65000   56280    56240

I am thinking about adding a primary key on the (obviously previously for this purpose implemented) "ID" column - just to follow the normalization requirements and to have it.
I am also thinking if a clustered index would be helpful or not... There is not a really good candidate to fullfil the Clustered index requirements. Maybe CDate is the least bad one...?

Unfortunately I do not have insight in regulary running queries that I could use to identify if there are range queries and to compare before / after performance with.

Questions:

After adding a primary key - can I expect any performance changes / improvements?
Is it even possible to add a primary key WITHOUT adding a clustered index?
Would you suggest adding a clustered index in this scenario to improve performance? I have read tons of opinitons, discussions and benchmark tests about this - Brent Ozar says "create it as long you can't prove it's better without" - others say (sorry, it is in German) performance usually is not getting better but ofthen worse having a clustered index in place because it requires more reads to get the required data, a benchmark on SE resulted in "Ci are good fur updates only" => so right now I am completely confused what to do with my several HEAP tables (like this one).

To answer question 2 first

2

A primary Key is NOT always the clustered index, it can be the clustered index and in the majority of cases is the way things are done, but it isn't always the best for your data. The culstered index is the order in which your data is physically stored on the disk whereas the primary key (which can be composite) designates the field to be a unique field and is beneficial for not inserting duplicate values and for foreign key lookups if you wish to do joins
(In summary for 2, yes you can add a primary key without a clustered index)

NOTE: sometimes if you want an identifiable row you can add a new field to the table to simply act as the primary key (not always advisable, but can sometimes be the a solution to improve performance)

1

Adding a primary key can change performance, however the only true way to know if it's going to improve performance is to test it, if you have a pre-live environment consider adding it to there and running your queries across it.

If you have queries that run as joins to this table on ID,AID,BID all together (Sorry I'm not 100% sure how these are all coming together) the Potentially create a composite primary key across all three which  means when anything wishes to get data with this table comparing all three of those it can find that row with ease. (Hope this makes sense)

3

Adding a Clustered Index completely depends on your data, once again a pre-live environment would be an ideal situation for testing.

A few things to consider when creating a clustered index, what data are you retrieving and what are you inserting

(this is a general example)If you're inserting and retrieving data that is the most recent data then a clustered index on the date field sounds the best idea, however if there is a LOT of data going in and out you will have very high contention on the most recent pages in your table, an alternative would be to have the clustered index around a category that those dates are on, eg client, this would mean that the data is grouped by a client which is more likely to have data gathered by, and spreads the read write load across the disk / disks

If the data retrieve is very random then a clustered index is quite pointless, if the data you get back has no real order to it then a Heap is completely acceptable.

Ultimately there is no be all answer to should I add a clustered index or a primary key because every situation is slightly different and will react in different ways.

If you have a pre-live environment (even a cut down version) can help make your decisions. Personally we have tables with primary clustered composite keys, and some tables that are simply heaps.

Hope this helps (and makes sense, sometimes I find I ramble)


How to search multiple values using LIKE I'm using MySQL and I have a table with these fields :

id
name
kode_fakultas
year

I want to create a form for searching based on user input. Example, if the user search is based on name, but the fields kode_fakultas and year are not filled with a value, then the form should retrieve the results based on name. And if the user wants to search based on kode_fakultas, but name and year are not filled  in, then the form would retrieve results based on kode_fakultas only.

My query seems wrong, because I don't get the results that I want.

select * from teacher
    where name like '%".$name."%' or year = '".$year."' or kode_fakultas = '".$fak."

Do not try to do it all in a single query.  But first, I will digress...

If you said AND instead of OR, that query needs

INDEX(year, kode_fakultas)  -- in either order

Indexing name is useless because of the leading wildcard on the LIKE expression.

But you have OR, so no indexing is useful; the entire table will be scanned, checking each row for 3 conditions to see if any match.

But that is not really what your UI is asking for.  To rephrase your requirements:

  The user will enter one of part of a name, the year, or kode_fakultas.

Your code must then build a query with only one of the tests in it, then execute it.  At that point, you would need

INDEX(year), INDEX(kode_fakultas)

but be stuck with a table scan if you need the leading wildcard on the LIKE.


How to improve poor performance with two joined CTE expressions on hierarchical data? I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched entries.
I am doing this with two CTEs: one that gets all recursive children from the specified parent, and another that selects matches and gets all the recursive parents of those matches. The main query then joins and groups these two CTEs.
However, the performance is not good. Running either CTE on its own (i.e., only getting enties that are descendants, or only getting path hierarchies) returns the results immediately, but both CTEs together (running on a table with millions of entries) can take minutes to hours, depending on the number of string matches, even a single match. It also won't return the first found entry until all are found.
Is it possible to improve the performance of the below query, or at the very least, make it so it starts returning found results immediately?
The table has this structure:
CREATE TABLE [Files]
(  [fileId]       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,  [name]         TEXT    NOT NULL
,  [parentFileId] INTEGER NULL
,  UNIQUE
   (  [name]
   ,  [parentFileId]
   )
);
CREATE INDEX [Files_parentFileId] ON [Files]([parentFileId]);
CREATE INDEX [Files_name]         ON [Files]([name]);

And the fastest query I've been able to produce is:
-- The [@Context] CTE lists all the fileIds that are descendants
--  of the input @rootFileId. The query should only return
--  matches that are in this list of fileIds
WITH           [@Context]
AS             (
   -- Starting with the ID of the root entry..
   SELECT      @rootFileId           [fileId]
   UNION
   -- ..build up all the IDs of the children
   SELECT      [Files].[fileId]
   FROM        [Files]
   JOIN        [@Context]
      ON       [Files].[parentFileId] = [@Context].[fileId]
               )
-- The [@FilePath] CTE selects each entry that matches the given
--  input @name and all entries that are its parents
,              [@FilePath]
AS             (
   -- Starting with the entries that match the given @name..
   SELECT      -1                    [depth]
   ,           [Files].[fileId]
   ,           [Files].[parentFileId]
   ,           [Files].[name]
   FROM        [Files]
   WHERE       [name]                LIKE @name
   UNION
   -- ..build up all the IDs of the parents
   SELECT      [@FilePath].[depth] - 1
   ,           [@FilePath].[fileId]  -- for grouping entries by the matched entry's id
   ,           [Files].[parentFileId]
   ,           [Files].[name]
   FROM        [Files]
   JOIN        [@FilePath]
      ON       [Files].[fileId]      = [@FilePath].[parentFileId]
               )
-- Group all the parent entries into one column with each found entry
--  and exclude any entry that wasn't on the [@Context] list
SELECT         [@FilePath].[fileId]
,              [@FilePath].[name]
, GROUP_CONCAT([@FilePath].[parentFileId]) [pathFileId]
, GROUP_CONCAT([@FilePath].[name], '\')    [pathname]
FROM           [@FilePath]
JOIN           [@Context]
   ON          [@Context].[fileId]   = [@FilePath].[fileId]
GROUP BY       [@FilePath].[fileId]
ORDER BY       [@FilePath].[depth];

Welcome to the forum, a good first question with included DDL that makes it easy to reproduce the scenario. For future posts, also include some sample data that one can use. You may even use DB<>Fiddle or a similar site. I have added a slightly modified version of your question at Your Fiddle. The site would not let me add more than ~90000 nodes in the tree, but the answer is returned within seconds for that amount of data at least.
I have removed quoted identifiers and changed them slightly. There are several reasons to avoid quotes, they make the code harder to read, and also make it case sensitive. Since SQL is case insensitive I also avoid camel-case. I also changed your index. Enough about that, here is a walkthrough of the solution:
CREATE TABLE Files
(  file_id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,  file_name         TEXT    NOT NULL
,  parent_file_id INTEGER NULL
,  UNIQUE
   (  file_name
   ,  parent_file_id
   )
);

CREATE UNIQUE INDEX files_ix1 ON Files (parent_file_id, file_id, file_name);

First step, get the descendants of a certain node. Similar to what you do in your query:
with rec (depth, file_id, parent_file_id, file_name) as (
    select 0, file_id, parent_file_id, file_name
    from files where file_id = 57
    union all
    select depth+1, f.file_id, r.file_id, f.file_name
    from files f
    join rec r
        on f.parent_file_id = r.file_id
)

Second, get the ancestors of the root from previous question. I have added a depth attribute that makes it easy to identify this node:
), root_path (depth, file_id, parent_file_id, file_name) as (
    select depth, file_id, parent_file_id, file_name
    from rec where depth = 0
    union all
    select r.depth-1, f.file_id, f.parent_file_id, f.file_name
    from root_path r
    join files f
        on r.parent_file_id = f.file_id
)

Your counter-part is likely to blame for performance issues since you traverse the whole tree, which you then have to join.
For the third step, we combine the ancestors with the descendants. I have used a union to get rid of the node that exists in both, you can probably increase efficiency by filtering one of the legs and use union all instead:
tree (depth, file_id, parent_file_id, file_name) as (
    select depth, file_id, parent_file_id, file_name from root_path
    union
    select depth, file_id, parent_file_id, file_name from rec
)

Now tree contains all relevant nodes, so we can recursively construct the transitive closure of the tree:
closure (file_id, parent_file_id, file_name, file_path) as (
    select file_id, parent_file_id, file_name, '/'||file_name
    from tree
    where depth = (select min(depth) from tree)
    union all
    select t.file_id, t.parent_file_id, t.file_name
         , c.file_path || '/' || t.file_name
    from tree t
    join closure c
        on t.parent_file_id = c.file_id
)
select * from closure

If traversing and aggregating information over the tree, is something you do a lot, you may consider some kind of Incremental Evaluation System (IES). The idea is that the recursive part above is already pre-calculated when you need it. There are basically 3 different versions of this that you can google for more info

Nested Sets, though not the inventor, Joe Celko has written a lot of articles and books about it. The basic idea is that you have an upper and lower bound on each node, any node that embraces another node is an ancestor of that node. The main criticism of the method is that small changes of the tree may be expensive since it can cause a renumbering of large parts of the tree.

Materialized path, you basically store the ancestor part in each node and use like predicate to find ancestors/descendants. Choose token separator wisely or you may end up with false results.

Use a separate ancestors table, often referred to as Transitive Closure (TC) table. You can maintain the ancestor's table with triggers on the tree table. I have used this for trees with millions of rows without problems. You need to be aware that the ancestor table typically is 5-10 times the cardinality of the tree table.

EDIT: FWIW I tried the query against my local Db2 11.5.5 with 9 million rows. The exact same query returns 29529 rows in ~20 seconds. If I reduce the result by choosing a start further down the tree I get:
file_id = 57      -> 29529 rows, 20 seconds
file_id = 5703    -> 1106 rows,  0.38 seconds
file_id = 57036   -> 132 rows,   0.15 seconds
file_id = 571036  -> 30 rows,    0.08 seconds


SQL Server 2014 Always on listener on named instance for default port This is my issue.
I've set up a test environment with SQL Server 2014, 2 named instances, WSFC and AG. They're set up like this.

 sqllab04\inst01 - sqllab05\inst01 - Listener VT-DB-LAB02 (port 1433)
 sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

Both listeners have it's own IP with DNS set up (dns name above).

INST01 on both 05 & 06 are set with port 1433 as per default in config manager.
INST02 on both 05 & 06 are set with port 1432 as per default in config manager.

Listen All is active on both and on INST01 it's set TCP Port 1433 on all and INST02 is 1432 on all.

The problem is following:
When we connect using ssms to VT-DB-LAB02 we can connect without any issues.
When we then connect to VT-DB-LAB03 we can connect but the connection goes to VT-DB-LAB02

From what I understand the issue is that inst01 is using port 1433 as default instance and is listening on ALL 1433 but what I don't know is how to get around that.

We still want to use port 1433 for inst01 and not change it to another port which I believe would solve it. I know that it's not the most secure way to set it up but I'm just testing right now.

Any suggestions? Let me know if anything is unclear or if I've missed some information.

This is happening because you have configured LAB03 listener to listen on port 1433 which is default port of default SQL Server instance, that is why when you connect using the listener it is connecting to SQL Server which is listening on port 1433. While Microsoft allows you to use default port 1433 for listener but this is not a correct thing to do specially when you have multiple instances and listeners. To get around this problem you have to change the port number of listener VT-DB-LAB03
sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

You can choose free port and configure listener to listen on this port after you do so the issue will resolve.
If you are choosing a non default port for listener you would have to use port number in connection string
Quoting from Docs.Microsoft

Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.
You can also designate a non-standard listener port; however this
means that you will also need to explicitly specify a target port in
your connection string whenever connecting to the availability group
listener. You will also need to open permission on the firewall for
the non-standard port.
If you use the default port of 1433 for availability group listener
VNNs, you will still need to ensure that no other services on the
cluster node are using this port; otherwise this would cause a port
conflict.

I suggest you also read AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.


databases cross references baselines We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues.

One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.

So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.

And for the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.

Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.

I know cross references between schemas/databases are commonly used in Oracle and now in MS-SQL as well :)

DBmaestro TeamWork (where I work) provides database enforced change management which consists of:

Database enforced version control – enforcing the version control process on the database objects.

Database baseline impact analysis – generating delta script (between environments) utilizing version control labels

and it does support cross references between schemas (Oracle) and databases (MS-SQL).

For more information on database enforced change management you can read our whitepaper


Permissions Dropped after Package Failed I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live and replicated to that instead so I must have set the job up incorrectly. The log shows that the table was created on live by the job.

For one or more reasons the copy job failed and the database it created was uloaded and apparently deleted (I can't find the .mdf or .log files) and this I could just about live with.

However after the failure of the job, all the object permissions were dropped from the live database so the accounts our application use to access the db were unable to connect.

After a restart the databases are running fine, but the permissions remain dropped.

I'd like to understand the reasons behind this behaviour and gather any dos and don't for the next time I have to do something like this.

Can anyone help?

The DB logins from live will overwrite your local DB logins. So, the local DB's logins, which contain the application user may be wiped out, unless it exists in the live DB also. Sometimes, the mappings between the DB user and the schema will get messed up. You can then recreate the user or use sp_change_users_login to fix the DB user mappings.


Postgres: ERROR: operator does not exist: point  point Using PostgreSQL 11 on Windows.

Both cube and earthdistance installed and verified by pg_available_extensions.

Restarted PostgreSQL.

[42883] ERROR: operator does not exist: point <@> point.

Setup:

CREATE EXTENSION IF NOT EXISTS cube SCHEMA temp;
CREATE EXTENSION IF NOT EXISTS earthdistance SCHEMA temp;

Tried the following code from StackOverflow

create table temp.lat_lon (
  city varchar(50) primary key,
  lat float8 not null,
  lon float8 not null
);

insert into temp.lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);

select
  (
  (select point(lon,lat) from temp.lat_lon where city = 'London, GB') <@>
  (select point(lon,lat) from temp.lat_lon where city = 'New York, NY')
  ) as distance_miles;

Throws:

[42883] ERROR: operator does not exist: point <@> point.

Extensions installed and Postgres was restarted.

select * from pg_available_extensions where name IN ('cube', 'earthdistance');

cube            1.4     data type for multidimensional cubes
earthdistance   1.1     calculate great-circle distances on the surface of the Earth

Is this because of PostgreSQL v11 on Windows? Yes, Lat/Long in correct order (long is first).

Note: Table F.6 Point-based Earthdistance Operators

Update in regards to: schema / search path:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

cube            temp
earthdistance   temp

SHOW search_path;

temp

Note: I CREATE EXTENSION hstore SCHMEA temp; and can use hstore and its operators. So doesn't seem to be all extensions.

Possible explanation: you installed the extension in a schema that's missing from your current search_path.

Diagnose with:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

SHOW search_path;

Is the schema of the extensions in your current search_path? If not, there is your explanation. Either install to a different schema or adapt your search_path.

And:

SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<@>';

Related:

Is it recommended to install extensions into pg_catalog schema?
How does the search_path influence identifier resolution and the “current schema”


CASE in WHERE clause in MySQL I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255))
    BEGIN

    SELECT * FROM `foo`
    WHERE
        CASE
            WHEN `column_selector` IS NULL THEN 1
            WHEN `column_selector` = 1 THEN `foo`.`column_1` = `value`
            WHEN `column_selector` = 2 THEN `foo`.`column_2` = `value`
        END
    ;
END $$
DELIMITER ;

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.

usually the answers are that it should use AND, OR instead or Dynamic SQL.

There is no dynamic SQL in your code.

  any difference in performance?

Of course the construction which you show causes fullscan and cannot be optimized. But the condition built using logical operators cannot be optimized normally too. To achieve highest performance use UNION ALL with 3 separate subqueries, one condition per subquery. Because constant-evaluated condition for two of them will give false only one of 3 subqueries will be executed in practice.

In SP this can be 3 separate queries wrapped with external CASE:

DELIMITER $$;
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255))
    BEGIN
    CASE WHEN `column_selector` IS NULL THEN
             SELECT * FROM `foo`;
         WHEN `column_selector` = 1 THEN
             SELECT * FROM `foo` WHERE `foo`.`column_1` = `value`;
         WHEN `column_selector` = 2 THEN
             SELECT * FROM `foo` WHERE `foo`.`column_2` = `value`;
    END CASE;
END $$;
DELIMITER ;

Flow Control Statements - CASE Syntax


What types of corruption can DBCC CheckDB miss? This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following:

BACKUP 'BrokenDatabase' detected an error on page (1:123456) in file ’BrokenDatabase.mdf'.
Error: 3043, Severity: 16, State: 1.

In the linked question and the backup I have ready for DBCC PAGE investigations, DBCC CHECKDB passed without error but corruption is evidently present.

What types of corruption can occur whereby CHECKDB will pass but a BACKUP WITH CHECKSUM will fail?

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.
First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:

You're right - if torn-page or checksum isn't turned on then there's nothing
that can be detected as far as page protection options are concerned.
CHECKDB may still pick up on corruptions that it finds from doing all the
consistency checks that it does - but it won't see corruptions in the middle
of data values, for instance.

Ha - that's the bummer about turning on page checksums - nothing happens
until a page is read in, changed, and written back out. The only way to
force pages to get checksums is to make them change - e.g. through
rebuilding all your indexes, whcih may be unpalatable - there's no 'touch'
tool out there at all.

The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.
BACKUP WITH CHECKSUM will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".
Now there is a second possibility for DBCC CHECKDB not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":

So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. Don't get confused between reserved and allocated - I explain that in the first misconceptions post here. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is allocated at the time a DBCC CHECKDB runs, but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt, but the second time it's not allocated, so it isn't consistency checked and won't be reported as corrupt. The corruption looks like it's mysteriously vanished. But it hasn't - it's just that the corrupt page is no longer allocated. There's nothing stopping SQL Server deallocating a corrupt page - in fact, that's what many of the DBCC CHECKDB repairs do - deallocate what's broken, and fix up all the links.

I don't have a final answer to your question, but as DBCC CHECKDB only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB.


Dropping columns from a table, truncating it and then re-inserting data as before into the table doesn't reduce space occupied I dropped a few (decimal) columns from my table. Truncated the table and then re-inserted the data as before, barring the dropped columns. However I don't notice any perceptible difference in the space occupied by the table. I had expected it to decrease. Why did it not?

Below is the query I'm using to determine the space occupied:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

PS. I deemed Rebuilding the table and its indexes unnecessary, as the table was truncated

The first thing I'd ask is are you sure the columns you dropped are decimals?

The reason why I ask is that with variable columns (which Decimal is not), then SQL Server won't automatically reduce space used when these are dropped. To resolve this you can run DBCC CLEANTABLE. You might want to try this anyway.

However, if the columns that you dropped are only decimals, then this probably won't have an effect.

Another possibility is that the decimal columns were just not taking up that much space - depending on the precision, a decimal column can be quite small. You say that there is no perceptible difference, is it just a case that the decimal columns were taking up relatively little space compared to other columns in the table (and rest of the database)?

Check the space used of the actual table before and after the change. I used this query to check table spaced used

SELECT
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
 sys.tables t
INNER JOIN
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND
 i.index_id <= 1
GROUP BY
 t.NAME, i.object_id, i.index_id, i.name
ORDER BY
 OBJECT_NAME(i.object_id)

Reference: I got this originally from this SO post.

To note/warning:
I'm assuming you're doing this in some sort of development or test environment, doing this sort of thing in production without testing is really not advisable. This earlier post is similar to yours (but is specifically about variable column lengths).

SpaceUsed
Your query to find space used looks fine, but there is an inbuilt stored procedure sp_spaceused that you might find easier (but probably doesn't make much of a difference.


SQL Server Management Studio Express 2012 "ONLY" I have installed SQL Server Express(SSMS) 2012 with management tools. After a few months, the Management studio has stopped working. The error is:

««
Evaluation period has expired. For more on how to upgrade your evaluation software please go to
http://www.microsoft.com/howtobuy
»»

I dont know why this happened with the express edition. Maybe the Management studio is not released as express edition. What should I do now to get SSMS work with SQL Server 2012?
The earlier releases of SSMS do not work with SQL Server 2012 and I must use SSMS 2012.

It's pretty likely you were using RC0, which expired a few days ago. I would imagine that if you start and stop the 2012 engine service in the services control panel, then it will refuse to start again.

When you have reinstalled Express it will be fine.


Why MySQL pick a different index for the same query structure but different values? Right now i have some issues understanding how MySQL picks the index to use on a query. I am executing a "COUNT QUERY", but everytime i use a different foreign key for the query, MySQL picks a different index to use.
Let me elaborate and explain my situation. On our DB table we have around 40 millions rows. I am using two "Count querys" with range filters to get the number of rows.  The two clients i use to test this behavior have this values:

Cliente_id 1 has around 8 millions rows on reports table.
Cliente_id 2 has around 1.5 millions rows on reports table.

Now i execute the following querys:
Count query of client with id 1 (finish in 5.6 seg)
Select count(*) From reports where cliente_id = 1 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

Count query of client with id 2 (finish in 58 seg)
Select count(*) From reports where cliente_id = 2 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

As you can see the second query of the client with less records, takes too long to execute. This seems odd, so when i try to use "EXPLAIN" to see which index is been use i got this:
EXPLAIN OF query from cliente_id 1 (Use created_at index.)
1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_created_at    63      5534206 Using index condition; Using where

EXPLAIN OF query from cliente_id 2 (Use cliente_id index.)
1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_clienteid 63      5534206 Using index condition; Using where

The second Count query is using a different INDEX. I been reading a lot of Mysql doc, but with not luck.  After a while i found a way to force the use of a specific index on a Query. I try this:
Selec Count with Use Index
SELECT COUNT(*) FROM reports USE INDEX(index_reports_created_at)  WHERE (company_id = '2' AND (created_at >= '2020-01-01T00:00:00' AND created_at <= '2020-01-31T0:0:00' ))

When i force the use of index on the query it only took around 6 seconds to finish. In theory the most optimal index to use is created_at, but on this situation MySQL picks  "cliente_id index".
What is the criteria here to pick cliente_id over created_at? I just don't understand this behavior.  I will really appreciated if someone can help me with this situation.
MYSQL VERSION is 5.7.32 and i am using innoDB engine
NOTE. I can hardcode the use index on my querys but i dont think is the right think to do. So i will really like to avoid use "USE INDEX" on my querys.
Note2. Sorry for my poor english. I try my best to explain.
Thanks for the help.

Per your testing based on my comments, it does seem like it's related to the cardinality of the data. I.e. MySQL's Engine thinks a larger date range predicate in your WHERE clause will result in enough data to be returned that the cliente_id is a better index to use when searching for that data. (It may even be doing a Full Index Scan instead of seeking through the index, but this I can't say for sure without seeing more analysis of the execution plan.)
Normally there's a cause behind cardinality issues (e.g. non-SARGable predicates, bad statistics, etc), but unfortunately I'm not an expert on MySQL and from looking at your query it appears to be pretty benevolent. Without any additional information my only guess is it's not any particular root issue, rather it's just a shortcoming of the MySQL Engine in this particular case (which can happen occasionally with larger datasets).
Normally my suggestion would be to cautiously use an index hint (as you already have tried) and to keep an eye on future performance of that query.


Add data directory on PostgreSQL 10 I have a postgresql database on / root partition (61GB used out of 63GB), so I asked the VPS provider to add more disk (50GB to /mnt/vdb1)

How to add another data directory to that new partition (using symlink or something)?

So that newly created tables does not use /var/lib/postgresql/10/main/base but use /mnt/vdb1 instead. I've been looking but it seems multiple data directory only available on old PostgreSQL 6/7?

As you noted, you can do this with tablespaces to split the data over multiple drives.  But using tablespaces makes things like restoration and migration more difficult.  Also, you have to actually move some objects to this space, or it won't accomplish anything.  You set it as the default, but that only applies to new objects, it won't automatically spill storage for existing objects to the new tablespace.  Any single object can not be split.

Is it really necessary?  Of the 63GB main drive, how much of that is used by the database itself versus other things?  Rather than using a tablespace, it would be cleaner just to move the entire database to the new drive, assuming it has enough space to hold it all on its own.  If most of that 61GB is for this database, then of course that wouldn't work.
In that case, to make things easier to manage, I'd probably drop the tablespace, and ask them to create for you a single new drive large enough to hold the entire database single-handedly, then move the whole thing to it.


How do I enable both PowerShell Remoting and SPN for SQL Server Reporting? I'm struggling with remoting servers with SQL Server Reporting Services running. My Reporting Services are running with individual domain-accounts, and I have set up SPNs for them (HTTP/<Machine> <domain>\<user>). As far as I can see, this effectively disables using PowerShell remoting, since the SPN which WinRM should use points to the domain account used by Reporting Services.

I have no problem running e.g. Get-Service -ComputerName <Machine>, but if I try Get-CimInstance Win32_Service -ComputerName <machine> or Enter-PsSession <machine> I get an error similar to this:

  Get-CimInstance : WinRM cannot process the request. The following
  error with errorcode 0x80090322 occurred while using Kerberos
  authentication: An unknown security error occurred. Possible causes
  are:
  -The user name or password specified are invalid.
  -Kerberos is used when no authentication method and no user name are specified.
  -Kerberos accepts domain user names, but not local user names.
  -The Service Principal Name (SPN) for the remote computer name and port does not exist.
  -The client and remote computers are in different domains and there is no trust between the two domains.

  After checking for the above issues, try the following:
  -Check the Event Viewer for events related to authentication.
  -Change the authentication method; add the destination computer to the WinRM TrustedHosts configuration setting or use HTTPS transport.

  Note that computers in the TrustedHosts list might not be
  authenticated.
  -For more information about WinRM configuration, run the following command: winrm help config.  At line:1 char:1    + Get-CimInstance
  win32_service -ComputerName <machine>    +
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    +
  CategoryInfo: AuthenticationError: (root\cimv2:win32_service:String)
  [Get-CimInstance], CimException    + FullyQualifiedErrorId : HRESULT
  0x8033809d,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand
  + PSComputerName        : <machine>

If I delete the SPN on one of my servers, then after a few seconds (a bit fast for AD replication?) I can use the above commands, but If I then reset the SPN the commands fail again after a while.

Some of my Reporting Services need to be able to forward credentials, so I hope someone is able to help me solve this dilemma.

I believe we have found the solution. To avoid Reporting Services and WinRM fighting over the HTTP SPN, you can set a port-specific SPN for the WinRM like this:

setspn -S HTTP/<Machine>:<port> <Machine>

It's a good idea to create SPNs for both short machine name and the FQDN.
The default port is 5985 for HTTP and 5986 for HTTPS, but I believe it can be set up to use different ports.

When using WinRM, I just set up a session like this:

$CimSessionOption = New-CimSessionOption -EncodePortInServicePrincipalName
$CimSession = New-CimSession -Name ServiceSession -SessionOption $CimSessionOption -ComputerName <Machine>
Get-CimInstance Win32_Service -CimSession $CimSession


Date time cast preferred method What would be the preferred method for speed and less database stress using a between or less than?
SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) BETWEEN '2019-01-01' AND '2019-12-31'

Or, would it be better to do this?
SET @StartLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000')

SET @EndLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate < '2019-12-31T00:00:00.000'
ORDER BY LogDate DESC);

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE mlog.LogId >= @StartLogId
    AND mlog.LogId <= @EndLogId;
```

My personal preference is to never use BETWEEN since its not abundantly clear, from the code itself, whether the range includes 2019-01-01 and 2019-12-31.  I realize one can easily check the manual to see how BETWEEN operates, but in my opinion, this is far more easily read, without any chance for confusion:

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) >= '2019-01-01'
    AND cast(LogDate as Date) <= '2019-12-31'

Be aware, though, that cast(LogDate as date) is not an optimal way to perform this query.  The far better solution would be:

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000'
    AND LogDate < '2020-01-01T00:00:00.000'

The above statement is SARGable, and will also ensure you get the range desired.  Comparing <= '2019-12-31' actually means before December 31st, 2019, when you probably actually mean up until the end-of-day on December 31st, 2019.  Looking for dates less than the start of the next day ensures you get all the rows between the start of the day on 2019-01-01 and the end of the day on 2019-12-31.

Note, I'm including a T between the date and time components; this indicates to SQL Server to treat the value as an ISO8601 date, which will prevent possible errors that might be introduced under non-US English language installations.


non-clustered unique indexes on sql server partitions SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.

I'm wondering why.

Short answer:

If the clustering key would not be part of the key set then the index uniqueness across all partitions could not be guaranteed.

The long story:

The only way to guarantee uniqueness is by enforcing uniqueness via a B-Tree structure. With a B-Tree is really easy to enforce uniqueness, since any key has a deterministic logical position in the B-Tree (between its left neighbor, that is smaller key, and the right neighbor, which is a larger key). Since each key value can be inserted in a single position in the tree then uniqueness is easy to guarantee: simply check during insert if the position is already occupied by an existing row.

With partitioning uniqueness across all partitions is trickier, since there are many B-Tree structures involved (one for each partition). Each can guarantee uniqueness, within a partition, but together they cannot guarantee global uniqueness across all partitions: a key in partition X may also appear in partition Y. Therefore, in order to preserve the semantics of 'unique index', the clustering column must be part of the key. This way each key value deterministically bound to a specific partition: a key cannot appear in partition X and Y because the partitioning column is part of the key, therefore the key can belong to exactly partition X or Y.

@MarkStoreySmith asked: So why doesn't SQL Server check for uniqueness by navigating each of the B-tree structures, acquiring range locks where the value being tested is not present, and holding until each B-tree has been traversed? Seeking each B-Tree would be a performance hog (think 15,000 partitions to be checked at each insert). And also the locking complications (hopping from one B-Tree to the next) are significant, with huge potential for deadlocks.


Is the Advanced Security Pack required for windows authenticaion? Can anyone tell me if the Oracle Advanced Security Pack is required for single sign in under Windows?

I've been told we are not licensed for the "Oracle Advanced Security Pack" and need to remove it, which appears to disable our existing externally identified logins.

Our version of Oracle is

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Yes, you will need Oracle Advanced Security to be installed. See http://docs.oracle.com/cd/E25054_01/network.1111/e10746/asointro.htm#i1008433

However a license for Oracle Advanced Security is no longer needed with 11g Release 2, but you will apparently need a license for "Oracle Identity Management Directory Services Plus".

See PDF page 10 of https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf

  Enterprise User Security is an EE feature for directory-based
  management of database users. It does require a corresponding Oracle
  Identity Management Directory Services Plus to be licensed. Usage of
  Enterprise User Security with Oracle Database strong authentication
  (PKI, Kerberos) no longer requires Oracle Advanced Security to be
  licensed.


what are the best ways to sync two databases in sql server or azure sql db? I have a question like syncing two different databases available on two different servers/instances.

lets say Source be the source server and sourcedb is the source database.
destinationdb is the destination database available in the destination server(destination).

Sourcedb will be refreshed for every 15mins so data will keep on updating.
*

  Note: the source db is huge database, might be 3tb in size

  Note: There are some 100 tables which should be in sync with Sourcedb and destinationdb

*

The process which i though

Process1:

take a full backup and restore in off business hours
take the transaction replication for those 100 tables b/w sourcedb and destinationdb

Limitations:
Full backup restoring is fine but when comes to replication it made me cry. Sourcedb act as publisher and destinationdb act as subscriber and distributer.
So whenever destinationdb restored with Sourcedb, the replication setting will be vanished.
**

  is there any other way to overcome this problem?

**

Process2:

Using Copy database command:

While using this setting will my destination database will be in
online? because both of my databases should be in online.

Process3

Old fashioned way:
Taking full backups,differential backups and restore in the destinationdb.

  while restoring the destination db will goes offline.

Please advice.

You seem to want to do data replication in almost real-time. Why haven’t you considered SQL Server data replication or even better, an availability group?
Our data warehouse is setup as a cluster-less availability group and the TB sized DBs are kept in sync.


Kill infinite loop plperl How can I kill session with infinite looped plperl function?

Both pg_terminate_backend and pg_cancel_backend had no effect. kill -1 pid did nothing as well and kill -9 pid crashed server.

You are correct,

CREATE FUNCTION foo()
RETURNS void
AS $$
  while(1) {}
$$ LANGUAGE plperl;

SELECT * FROM foo();

Having called pg_terminate_backend and pg_cancel_backend that plperl backend persists. Seems to be a bug that they already know about, from the lists  Tom Lane says,

  Even assuming that we can fix the garden-variety bugs like these, there's still a fundamental problem that an uncooperative user function
  (particularly one in plperl/pltcl/plpython) can indefinitely delay
  response to pg_terminate_backend.  Maybe that's okay, seeing that it can similarly hold off or disregard QueryCancel, but I'm not sure the people asking for this are really gonna be satisfied.  (One thing we
  should consider is making ERRCODE_ADMIN_SHUTDOWN unconditionally untrappable by plpgsql exception blocks, which'd at least fix the issue for plpgsql functions.)

I am not sure they have a solution to this or not. From a Craig Ringer,

  In general it's safe to use pg_terminate_backend as a "bigger hammer". SIGTERM as sent by  pg_terminate_backend() will often, but not always, cause a backend that can't respond to a cancel to exit.

He even goes into explaining some of the options that can cause the hang.

Anyway, let's try something

CREATE FUNCTION foo()
RETURNS void
AS $$
  $SIG{INT} = $SIG{TERM} = sub { die; };
  while(1) {}
$$ LANGUAGE plperl;

Then I ran

SESSION 1                           SESSION 2

SELECT pg_backend_pid();
 pg_backend_pid
----------------
          20465

SELECT * FROM foo();
-- LOOPING

                                    pg_terminate_backend(20465);

ERROR:  Died at line 2.
CONTEXT:  PL/Perl function "foo"

That seems to work, if it's an option.


How to join two table without getting duplicate from right table in Oracle Tables Details:
CREATE TABLE Test2 (
  ID INT,
  Value INT,
  other INT);

CREATE TABLE Test1 (
  ID INT,
  TYPE INT,
  other INT);

INSERT INTO Test2 VALUES (123456, 5, 12);

INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);

INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);

INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);

This is the Query i m using to avoid duplicate from table Test
SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1.TYPE, t1.other, t2.value
ORDER BY t1.ID ASC;

Query Result:
ID  TYPE    other   value
123456  0   2   5
123456  1   6   5
123456  2   4   5

Description:
Expected Result is , when i m fetching match records from tables. Should get all the records from left table(Test1) and avoid duplicate record from right table (Test2).
Expected Query Result are,
ID  TYPE    other   value
123456  0   2   5
123456  1   6
123456  2   4

SQL Fiddle Link
http://sqlfiddle.com/#!9/8953fc/29
Criteria
When there is ID match should get all matched records from left table (Test1) and from right table (Test2) should get distinct records.

Doing it on client side (sqlplus):
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> break on value
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4

In SQL:
SQL> clear breaks
breaks cleared
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, case when row_number() over (partition by t1.id order by t1.id) = 1 then t2.value end value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4


SQL: Multiple Columns Generally, a table design with repeated ambiguous columns is bad design:

CREATE TABLE persons(
    personid int primary key,
--  …,
    phone1 char(10),
    phone2 char(10),
    phone3 char(10)
);

while a table with distinct columns is better design

CREATE TABLE persons(
    personid int primary key,
--  …,
    mobile char(10),
    homephone char(10),
    fax char(10)
);

Understood and Agreed.

The problem is that as far as I can tell, the first design scrapes through the most definitions of normalisation, since technically the columns have different names. Databases neither know nor care what the names mean, and it has always up to human interpretation as to what to put where.

Clearly, he problems with the first design include:

the arbitrary number of columns is often too many or to few for some rows; this may even require altering the table to accommodate.
because the names are ambiguous

it is possible that some of the earlier columns are empty while the latter ones are populated
it makes searching difficult since you don’t know which column to search

I also know that if there are genuinely multiple values then the data should have appeared in a separate table.

The question is:

is there a correct name in the literature for the anomalies caused by the first design?
is there a “normal” form which precludes the first design?

I'm not sure on what the anomalies caused by the first design are called, but the below is the standard solution for that type of problem.  Or at least, it's the standard solution that I use.

Thea answer is to create another table to hold just phone numbers with a foreign key reference to the person table.  The only sticking point may be the PhoneTypeDescription field which should probably be yet another lookup table, depending on how many phone number types you think you might store.  But possible values could be Work, Cell, Home, Fax.  The use of the "IsPrimaryPhone" is also optional but probably warranted.

CREATE TABLE dbo.Person
    (
    PersonID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , FirstName NVARCHAR(100) NOT NULL
    , LastName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PhoneNumbers
    (
    PhoneNumberID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person(PersonID)
    , PhoneTypeDescription NVARCHAR(10) NOT NULL
    , PHoneNumber NVARCHAR(20) NOT NULL
    , IsPrimaryPhone BIT NOT NULL DEFAULT 0
    )

CREATE NONCLUSTERED INDEX IDX_PhoneNumber_PersonID ON dbo.PhoneNumbers (PersonID, PhoneNumberID)


Mysql row-based binary logging behaving different in some versions of mysql (rows grouping) Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.

Let's assume the following statement update three rows:

UPDATE table_name SET a=1 WHERE id IN (1, 2, 3);

In mysql 5.7.21: The binary log receive only one log containing the 3 rows changes. (Which is my desired behaviour)

However, when tested in mysql 5.7.11 and mysql 5.7.26, the same statement produces three separated logs. Each one of them containing each changed row.

I have checked the system variables and, at least, the ones who are related to the binary log, seem to be exactly the same on 5.7.21 and 5.7.26.

We have checked the startup variable --binlog-row-event-max-size, which could be causing the problem. Since the rows in our tables could be bigger than the default 8KB. But, we are not able to set this variable. Since we are working in Amazon RDS. (How can we do it?)

I need that the rows that are changed on the same statement are always batched in the binary log. Is there some variable which could be causing this behaviour?

Notes: There is no replication involved in our setup.

The setting is a "soft" max.  If a row exceeds the given value, the entry will be bigger than the max.  That is, "no problem".
If your desire is to avoid them ever being batched, you probably need the value to be less than 8K.
From the 8.0.14 Changelog:

The startup option --binlog-row-event-max-size now has a corresponding system variable binlog_row_event_max_size. The startup option and system variable set a soft limit on the maximum size of a row-based binary log event, with a default setting of 8192 bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded.
The binlog_row_event_max_size global system variable is read-only and can be set only at server startup.

As for whether, or when, Amazon will provide a way to set it, I don't know.  File a bug report with them.
I would suggest that the value of the variable is only a minor optimization.
----- 2017-04-10 8.0.1 Development Milestone & 2017-04-10 5.7.18 General Availability -- Bugs Fixed -- Replication -----
Binlog_sender, which writes events from the binary log to a packet buffer and then sends the packet to the slave, did not reduce the size of the send buffer as expected. (Bug #24643036)


Reduce number of rows with same id repeated based on status I have the following table:

-- id, location_id, status, posted_year, posted_quarter
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1 ,12,'active'  ,2014,3),
(2 ,12,'inactive',2014,3),
(3 ,12,'active'  ,2014,3),
(4 ,12,'active'  ,2014,4),
(5 ,12,'inactive',2014,4),
(6 ,13,'active'  ,2015,1),
(7 ,13,'active'  ,2015,1),
(8 ,13,'inactive',2015,1),
(9 ,13,'active'  ,2015,2),
(10,13,'active'  ,2015,2),
(11,13,'inactive',2015,3),
(12,13,'active'  ,2015,4),
(13,13,'active'  ,2015,4),
(14,13,'inactive',2015,4),
(15,12,'active'  ,2015,1),
(16,13,'active'  ,2015,1),
(17,12,'inactive',2015,1),
(18,12,'active'  ,2015,2)
) AS t(id,location_id,status,posted_year,posted_quarter);

I want to recreate this table but have only one quarter per year from each location.

We might have more than one record in a year for a quarter, and in this case we need to check the status based on:

If one status is active for a year in selected quarter, the status will be active, otherwise the status will be inactive.

Examples:

location_id 12 for year 2014 and quarter 3 will have one record in the new table with status active.
location_id 12 for year 2015 and quarter 1 will have one record in the new table with status inactive.

How to write this query?

The only trick here is to create something you can group by that involves the year, and the quarter. This isn't the only way to do it, but you can do this...

make_timestamp(posted_year,1,1,0,0,0)::date
+ posted_quarter*3*'1 month'::interval

Remember, three months in a quarter-year. You can also do posted_year + posted_quater*0.25 but all the same.

From there, it's pretty basic. I'll use a CTE to separate formatting from calculation (feel free to ditch it for speed).

DISTINCT ON()

WITH t AS (
  SELECT (make_timestamp(posted_year,1,1,0,0,0)::date + posted_quarter*3*'1 month'::interval)::date AS qtr, *
  FROM foo
)
SELECT DISTINCT ON ( location_id, qtr ) location_id, qtr, status
FROM t
ORDER BY location_id, qtr, status='active' DESC;

GROUP BY ... bool_or()

Or, alternatively (and maybe faster),

WITH t AS (
  SELECT (make_timestamp(posted_year,1,1,0,0,0)::date + posted_quarter*3*'1 month'::interval)::date AS qtr, *
  FROM foo
)
SELECT location_id,
  qtr,
  CASE WHEN bool_or(status='active') THEN 'active' ELSE 'inactive' END
FROM t
GROUP BY location_id, qtr
ORDER BY location_id, qtr;


Query in SQL SERVER2014 for a report (I need the last ROW of a table) I'm using SQL Server 2014 and I have a problem with a query.

[MY TABLES]1

I want to have in my report, ALL the items of the order with ID_Order = 9 that have been delivered. And for the items that have been delivered at two times (Item Code = Art3 for example), I just want to have the last row, that means the last delivery of this Item, with NO repetition.

I already tried these two queries without success:

Attempt #1: DISTINCT

SELECT DISTINCT
    Order.ItemCode, Delivery. Qty, Delivery.ID_Delivery,
    Order.ID_Order
FROM
    Delivery
INNER JOIN
    Order ON Order.ID_Order = Delivery.ID_Order
WHERE
    Order.ID_Order = '9'

Attempt #2: subquery

SELECT *
FROM
    (SELECT
         Order.ItemCode, Delivery.Qty,
     FROM
         Delivery
     INNER JOIN
         Order ON Order.ID_Order = Delivery.ID_Order
     WHERE
         Order.ID_Order = '9')
GROUP BY
    a.ItemCode, a.Qty

As with most things SQL-related, there is more than one answer in this case. Here is one solution that might work for you. It relies on a lesser known function called "row_number" (Microsoft documentation here) which, in this case, can help you avoid making multiple passes through temp tables (though that would also work for your needs)

;WITH deliveries_report AS (
    SELECT
        id_order ,
        id_Delivery ,
        itemcode ,
        qty,
        ROW_NUMBER()  OVER (PARTITION BY itemcode ORDER BY id_Delivery DESC) AS row_num
    FROM
        #deliveries
    WHERE
        id_order=9
)

SELECT
    id_order ,
    id_Delivery ,
    itemcode ,
    qty
FROM
    deliveries_report
WHERE
    row_num=1
ORDER BY
    id_Delivery DESC

Just replace #deliveries with the name of your deliveries-table. Good luck!


Are Delete operations written to AppendOnlyFile in Redis? AppendOnlyFile in Redis logs every write operation done to the redis database.

My Question is that when we delete data from the redis database, are those operations logged in the AppendOnlyFile ? And the data which is deleted from the database, is the record corresponding to that data in the AppendOnlyFile is also deleted?

For example,

I set a key,

127.0.0.1:6379 > set a apple

This will be logged in the AppendOnlyFile, suppose as "Log:set:a:apple"

Now, If I do,

127.0.0.1:6379 > del a

What will this do?
Will a log corresponding to this be made, like "Log:del:a" ?
And once we delete the key, will the log "Log:set:a:apple" also get deleted ?

Yes. Every modification (also delete) of data gets written to AOF. See Redis persistence demystified blog posting.

  Append only file The Append Only File, usually called simply AOF, is
  the main Redis persistence option. The way it works is extremely
  simple: every time a write operation that modifies the dataset in
  memory is performed, the operation gets logged. The log is produced
  exactly in the same format used by clients to communicate with Redis,
  so the AOF can be even piped via netcat to another instance, or easily
  parsed if needed. At restart Redis re-plays all the operations to
  reconstruct the dataset.

  To show how the AOF works in practice I'll do a simple experiment,
  setting up a new Redis 2.6 instance with append only file enabled:

./redis-server --appendonly yes

  Now it's time to send a few write commands to the instance:

redis 127.0.0.1:6379> set key1 Hello
OK
redis 127.0.0.1:6379> append key1 " World!"
(integer) 12
redis 127.0.0.1:6379> del key1
(integer) 1
redis 127.0.0.1:6379> del non_existing_key
(integer) 0

  The first three operations actually modified the dataset, the fourth
  did not, because there was no key with the specified name. This is how
  our append only file looks like:

$ cat appendonly.aof
*2
$6
SELECT
$1
0
*3
$3
set
$4
key1
$5
Hello
*3
$6
append
$4
key1
$7
 World!
*2
$3
del
$4
key1

  As you can see the final DEL is missing, because it did not produced
  any modification to the dataset.


How do I collapse schemas in SQL Server Management Studio? I am not sure if this is possible, but I am hoping it is - I am currently running SSMS and have a database that has it's data all "housed" in the DBO schema.  I have received advice (and completely agree) that best practice will be to create additional schema's that match our business functions, and I will be moving ahead with that.

I would like to know if there is any way - even if it is by install an add-in of some kind, to have the schema collapse so that I can have all the DBO, and RPT objects hidden until I expand the schema - much like the tables and other folders are collapsed.

I apologise if this question is not quite "on topic" for this site - but I think this is where the answer is most likely to be known.

In my screenshot below - I am looking for the bit outlined in red to be collapsedand there just to be 2 lines the DBO that is there now - and the RPT which I currently have the request in to create:

I did a bit of hunting after @LowlyDBA gave his answer, and came across an extension to SSMS that does exactly what I was looking for!

SSMS Schema Folders


PostgreSQL autoincrement up to certain number then reset for sharding I've was recently reading an article around how Instgram used to shard their IDs and they use a unique function to generate ids on their servers https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

But it got me curious around whether if you could just assign a shard to a particular user rather than every item and how performant it would be. Especially in a large scale website.

So would a function similar to the following be a valid approach.

Assuming a users table on a dedicated database purely for users.

User
   - UID
   - name
   - shardId

Where shardId is calculated via a function that starts at 1, and for every new insert it would increment - upto a max of n (say 2048) for this example. Then reset to 1. Almost acting as a round robin to distribute users data among a shards.

Would there be serious performance implications to such a function, especially during high load? As serial columns simply select a nextval from a sequence do they not? It's just an extra step of reseting a sequence once it reaches n. Or am I barking up the wrong tree?

As serial columns simply select a nextval from a sequence do they not?
  It's just an extra step of reseting a sequence once it reaches n

Not even an extra step because cycling is a built-in functionality of sequences,
See CREATE SEQUENCE.

Example:

=> create sequence s maxvalue 5 cycle;

=> select nextval('s') from generate_series(1,7);
 nextval
---------
       1
       2
       3
       4
       5
       1
       2


Restricting the # of Rows a User can return in a single query A concern has come up recently about employees who have read access to our internal databases being able to download large amounts of data for nefarious purposes.

My question is whether it is possible to restrict the number of rows that a user can return in a query? None of the users who have read access to the db server ever need to return more than a few dozen rows at a time.

Alternatively, how do others here deal with security concerns about the people in their organization who have direct access to run queries on their databases?

Always just give the grants necessary. If you want to restrict on the row number, you have to create views like

CREATE VIEW view_name AS
SELECT whatever FROM a_table ORDER BY whatever LIMIT 10;

Then just grant select on this view, not the table the view is reading from. Read more about views here. With DEFINER and SQL SECURITY you can also select on things, that the user usually has no right to. Whatever you need.

As a last note, I included ORDER BY in the query, because a LIMIT without ORDER BY usually doesn't make much sense, cause there's no order in a relational database unless you specify it with said ORDER BY.


How to tell different types of Logins apart and how to see the users? I have a novice question: I could see different (presumably) AD Groups and AD accounts under the SQL Server Server Logins. However, I am unable to tell whether some of these users are AD users vs AD Groups. Someone mentioned you could look at the LOGO and if it's a multiple person it's a group, single person is a user. Is this the best way to tell? That doesn't seem reliable.

Also, how can I see the USERS within an AD group? I can see the group, but I need to see the users. I do NOT have access to the AD Server.

Thank you so much.

Yes the icon changes if it's a group vs a user. You can also query sys.server_principals.

To see the users within an AD group, you can use xp_logininfo. As long as the AD group is a login on the SQL Server, you can query it using xp_logininfo. Here's a really old blog post I wrote on it.

Syntax:

xp_logininfo [ [ @acctname = ] 'account_name' ]
     [ , [ @option = ] 'all' | 'members' ]
     [ , [ @privilege = ] variable_name OUTPUT]

Example:

EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'


Database design for job board based on neighbouring cities I'm trying to implement the relationships for the data represented below.

I have a microjob board, users sees job listings in their city and neighbouring cities

user 1 lives in city1
user 2 lives in city2 and that city is neighbour of city3 and city4
user 3 lives in city3
user 4 lives in city5

user 1 posts a job that needs to be done in city3

After that;

user 2 sees this job at job listings as he lives in city2 and that
city is neighbour of city3 - can offer help
user 3 sees this job at job listings as he lives in city3 - can offer help
user 4 can't see this job as he neither lives in city3 nor is his city a    neighbour of it

How can I accomplish this database schema with relationships?

There should be one user table. (user_id, username, etc)

There should be one city table. (city_id, city_name, etc)

There should be one ref_user_city table. (ref_id, user_id, city_id, etc)

There should be one Job table. Job can have only one city where job can be done. (job_id, city_id)

Users can select multiple cities at the time of profile creation or update. Which will be stored in ref_user_city. These cities are where the user lives or user can provide job service.

Now you can easily find which user can do the job in a specific city.

Select user_id from ref_user_city where city_id = 1;

now users who can do a job for city_id 1 you can get data of those users from user table.

Let's say if you want to get jobs for user_id = 1, user 1 lives in city 1 & can provide job service in city 2,3 & 4.

Select job_id from job where city_id in (1,2,3,4);


Preventing SQL Server Data Tools from Inserting Spaces in Object Names Is there any way to prevent SQL Server Data Tools and Visual Studio from introducing spaces into object names when creating dimensions, mining structures and the like from data source views (DSVs)?

For example, if you happen to have columns named WordCount and IsList defined in your DSV, the SSDT/Visual Studio GUIs will automatically change the names to "Word Count" and "Is List." Although this may be more "end-user-friendly," it can cause a lot of problems if your goal is to tightly synchronize object names with the relational objects the DSV columns are derived from; for example, scripts that try to make some kind of statistical comparison between the relational column and data calculated upon it in a cube may fail because the object names are different. It can also cause difficulties in synchronizing report writing and documentation generation. It's cumbersome to have to manually remove the spaces that SSDT/Visual Studio introduce (as well as risky, given that these tools are notoriously temperamental when it comes to juggling metadata, leading to all kinds of obscure SSAS errors) so I'd like to shut off this "feature" permanently if possible. Thanks in advance.

Note that I did not indicate an edition of SSDT or Visual Studio because I have yet to see one that doesn't exhibit this behavior.

I don't think it's possible to turn the "feature" off, but the way it works is that it interprets caps and underscores as a new word so a field in your datasource MyAttribute or my_attribute becomes a member named My Attribute.

The only way I can think of to prevent this is to have your column names all lower case without underscores in your datasource so myattribute as a column name will stay myattribute as your member name.

I'm not sure how your comparison scripts work and how you generate them, but I think you'll have to resort to either removing the space when you generate an SQL query or parsing and adding a space according to the same logic when generating your MDX. In powershell that should be fairly easy.


How can I run a pgAgent job every 10 second? I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with precision of second.

Is it possible to run job created by pgAgent faster than every minute?

As it is using a cron-style schedule definition it is unlikely to support sub-minute granularity as most (all?) cron implementations don't.

A hack I've used to run a task more often than every minute with cron is a script that cron calls once per minute, which performs its task, sleeps a bit, then repeats. For example to run for times per minute: dotask; sleep 15; dotask; sleep 15; dotask; sleep 15; dotask;. You could do something similar with pg_sleep. There are a few caveats to watch out for including:

Your task may run a few more times after you stop it via your schedule configuration.
If the task takes an appreciable amount of time you need to adjust for this in your sleep durations. If the time it takes is variable the you need to be brighter than a simple static sleep call: for the 4/minute example your first sleep would be for "15-minus-current-seconds-on-wall-clock seconds", the second for "30-minus-current-seconds-on-wall-clock seconds".
If the task ever takes longer than your target timing granularity to complete, you have the potential problem of two instances trying to run at the same time which might cause locking issues and/or bog your database down.

Another option is to define multiple tasks all set to go off on the minute. The first runs immediately, the second sleeps for  seconds before running the actual work, the second sleeps for *2, etc. So for running the task four times per minute via cron you'd have:

* *    * * *    dotask.sh
* *    * * *    sleep 15 && dotask.sh
* *    * * *    sleep 30 && dotask.sh
* *    * * *    sleep 45 && dotask.sh

(where 5 *s means "run every minute" in cron's config files)
This removes the exactly-how-long-to-sleep-for problem (the second issue above), but potentially makes the multiple-instaces-running-at-once matter (issue three above) more of a concern. Both options are somewhat hacky...


Subquery or multiple left joins, or both? I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same.  Each table has quite a few records, but during a complicated form process, the user is presented with every record from table1, but depending on their choice in the form (done by radio, with id as the value) they may not have every result from table2 listed on the next part of the form.  This is based on a 3rd table, table3, with id as primary key, table1_id, table2_id as records, which lists "invalid combinations" from table1 / table2.

So, I'm trying to generate the sql for showing table2, which needs to NOT have any records that match the choice from table1, that are listed in table3.

Subquery Table3, with a where statement of the choice made on table1, and use that as a NOT NULL Left join on table2?  Every time I try to write the SQL I mess something up.

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;


Migrating from NTEXT to NVARCHAR(MAX) I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants to migrate the column type to NVARCHAR(MAX).

While doing so, what all potential threat should I be careful about?

Is there any possibility of data loss for the NVARCHAR(MAX) limitations?

"peculiar reason"? Only that ntext is deprecated and damn difficult to work with?

It's a lossless conversion. If you are unsure, use the SSMS GUI designer to generate scripts for you.


SQL 2008 R2 Merge Replication - Clearing Skipped Schema Changes A developer made a change to our production database that is using merge replication to a table (Activity).  That table has a Foreign constraint that points to a table that is not part of replication and does not exist at the replicated database. This broke replication.  To get replication up and running, I used the sp_markpendingschemachange procedure to skip the schema changes.  This worked, but now I am left with the records still in the sysmergeschemaarticles table, just marked skipped.  At this point, I want to get rid of the records so they get executed in the future.

My solution is to perform a snapshot (at a less busy time) assuming that the this will clear the sysmergeschemaarticles table of any ddl changes but have not found any supporting documentation to support this.  Anyone have an experience with this type of situation that can support this solution or propose a new better one?

Thanks!

Luckily the Network Admin found some space for a couple of VM to test the solution. I tested the solution and it worked perfectly.


Does Service pack have all the changes in a cumulative update I have always thought that all the changes in the CU's are rolled up into the next services pack.  I recently was looking at changes in SQL 2016 SP2, and SQL 2016 CU15 for SP1.

I was surprised to see that none of the KB in SP1CU15 was mentioned in the SP2.  What am I missing?

According to SQLServerBuilds SP1 CU15 was released after SP2.

SP1 CU15 was released on 2019-05-16, SP2 was released on 2018-04-24.

Given that from SQL 2017 there will no longer be any service packs, this is no longer relevant moving forwards, but in the past Microsoft would release fixes in parallel for supported service packs. As an example this kb article describes an issue that had a fix that was included in two separate CU's, CU15 for SP1 and CU7 for SP2.

For confirmation of the data on SQLServerBuilds we can check the file details on both SP2 and CU15 SP1

In addition to this as pointed out by David Browne, there is a development and test overhead for all new SP's, meaning an SP does not necessarily contain all fixes in previous CU's by date - this is due to the time taken for finalising and testing the SP.