texts:
I have to make Movies database with the following tables:

•   Directors (Id, DirectorName, Notes)

•   Genres (Id, GenreName, Notes)

•   Categories (Id, CategoryName, Notes)

•   Movies (Id, Title, DirectorId, CopyrightYear, Length, GenreId, CategoryId, Rating, Notes)

Let me give you my query for Directors database:

CREATE TABLE Directors (
Id INT PRIMARY KEY IDENTITY,
DirectorName NVARCHAR(60) NOT NULL,
Notes NVARCHAR(MAX)
)

Genres and Categories tables are almost the same. I have only changed the size of NVARCHAR.

I am having doubts about Movies table. I don't have experience with databases and I am not sure if I chose the right data types for each column. I would be very grateful if someone with more experience can help me and see what I've done.

CREATE TABLE Movies (
Id INT PRIMARY KEY IDENTITY,
Title NVARCHAR(60) NOT NULL,
DirectorId INT FOREIGN KEY REFERENCES Directors(Id),
CopyrightYear SMALLINT NOT NULL,
[Length] FLOAT(1) NOT NULL,
GenreId INT FOREIGN KEY REFERENCES Genres(Id),
CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
Rating FLOAT(1) NOT NULL,
Notes NVARCHAR(MAX)
)


texts: Wrong sort order using utf8_unicode_ci and swedish characters like ö

texts:
I am confused about what encoding and collation I should use so my queries returns the correct sort order.

Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and lastname.

And the sort order would be something like this:

firstname  lastname
aaa        aaa
ööö        ööö
ooo        ooo
ppp        ppp
qqq        qqq

ööö ööö should be after qqq qqq

Changing collation to utf8_swedish_ci works correctly:

firstname  lastname
aaa        aaa
ooo        ooo
ppp        ppp
qqq        qqq
ööö        ööö

But utf8_unicode_520_ci give me the same result as utf8_unicode_ci, shouldn't it work too?

The funny part is if I only change firstname collation to utf8_unicode_520_ci it seems to work.


texts: How should I setup a table of temporary/transactional items that I only need for a few days or weeks?

texts:
We have created a table in Sql Server 2012 that we use to store temporary records of transactions with our integration partners. These records can be text, XML or BLOBs. They're not actually used for anything by our software but are for debugging, recovery and investigation purposes. After a certain age they are deleted.

As volume has increased recently, this table has grown immensely in size (50GB at the moment, individual rows are as much as 25MB). This is not a problem in itself. However, the deletion scripts that clear out old records are no longer running to completion in reasonable amounts of time.

Is there a better way to store and clean up this data? Would partitioning be useful for this? Is there some other technology that would help manage a rolling date range of large data?


texts: Does SQL Server Compact Edition 4 finally support Views?

texts:
Just read about the release of SQL Server CE 4 and then searched all through their product website, I still cannot find a feature overview.

Previous versions of SQL Server CE didn't support views, I was hoping that they removed the limitations.

My question is:

Is there a page which lists the features and limitations of SQL Server CE 4?

(Edit: I found this feature comparison for 3.5, but not for 4.0)


texts: MSSQL how to open 'Edit' window when databases has been hidden

texts:
I hope that someone in here have had this issue before.

THE ISSUE:
- Because 3rd party consultants are doing work to specific customer databases and should not see customer databases to which they have no relation, we have hidden these databases. However, this makes it more cumbersome for the 3rd party consultants, especially the less T-SQL skilled ones.
Specifically I would like to know if it is possible to open the: db --> tables --> right click the specific table --> click 'edit' window....in some other way. Is that possible?

I hope I have made myself clear and I'm looking forward to hear from you.

Thank you in advance.

resulting in the following window in SQL Management Studio:


texts: Does MySQLDump Verify the backup as restorable

texts:
I have written a PowerShell script to use MySQL dump to perform full backups of a specific database at a specific time.
I am wondering if I need to confirm the backup is restorable via my script or will MySQLDump fail if there is corruption in the database?

The command I am currently using:

.\mysqldump.exe --user=old --password=mate --log-error=$errorLog  --result-file=$backupfile  --databases $database


texts: sql query to calculate a difference between cols in two different tables, grouped by common identifiers on the row

texts:
This is probably an amazingly dumb question, but hopefully someone kind can help out while laughing :D
I'm doing this on postgres 9.5

I have two tables, all data types can int in this example
table1 has:

id     val

1      10

2      20

3      30

table2 has:

id     value

1      15

3      15

5      50

I would very much like a query that calculates the diff between the two tables (table2.val - table1.val) for each id, so that the result is:

id     diff

1      5

2      -20

3      -15

5      50

My difficulty is in figuring out how to do it when some ID's only exist in one table...

Please help :)

So to create my best attempt:

create table table1 (id int, value int);
create table table2 (id int, value int);
insert into table1 values
(1,10),
(2,20),
(3,30);
insert into table2 values
(1,15),
(3,15),
(5,50);

select table1.id, table2.value - table1.value as diff
from table1
inner join table2 on table1.id=table2.id
group by table1.id, diff;

However this leads to :

id      diff
3       -15
1       5

If I try a full outer join I get:

select table1.id, table2.value - table1.value as diff
from table1
full outer join table2 on table1.id=table2.id
group by table1.id, diff;

which gives

id      diff
null    null
2       null
3       -15
1       5


texts: Problem restoring a database in SQL Server

texts:
I am trying to follow the instructions on the following post:

https://stackoverflow.com/questions/10299811/creating-new-database-from-a-backup-of-another-database-on-the-same-server

To summarize; I am building the logical file list then restoring the database:

RESTORE FILELISTONLY
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK';

RESTORE DATABASE KeltieCochrane_v2
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2.mdf',
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2_1.ldf';

I have no .MDF or .LDF in the target folder and understand that they are contained in the .BAK backup file.

When I restore the database into an empty, new database, it states:

The backup set holds a backup of a database other than the existing 'KCDB'.

When I try to restore using the actual database name in the script (KeltieCochrane_v2) I get There are conflicting file locations specified for the file.

If I try to simply restore the database I get a standard 15105 error and the operating system cannot get the text for this error.

What am I doing wrong?


texts: How can I count different types in a column in sql?


texts: I am using Percona xtrabackup for creating backup of mysql server. Does backup file includes all the procedures, functions and triggers ? Does it get restored when i restore it ?


texts: MySQL - Migrating from Master-Slave replication to Master-Master

texts:
We currently run 2 large mysql master-slave replication trees.

Each tree is built with one master, several 'middle' slaves replicating from the master, and 'end' slaves replicating from the 'middle' slaves. For example:

                              - end slave
            - middle slave  - - end slave

-  master - - middle slave  - - end slave

            - middle slave  - - end slave
                              - end slave

I would like, if possible to get rid of the master, and promote the 'middle' slaves to be the masters in master-master replication. Example:

                        - end slave
              master  - - end slave

              master  - - end slave

              master  - - end slave
                        - end slave

Is this possible? What would be the process I should follow? What sort of downtime should I expect?

Thank you very much in advance!


texts: Calculate Employees' Total Working hours using SQL Server

texts:
Below is the DDL of my table:

CREATE TABLE [dbo].[Logs](
    [ID] [int],
    [empid] [int],
    [time] [datetime],
    [In_Out] [varchar](3)
) ON [PRIMARY]
GO

I need to calculate the total working hours of an employee per day.

Shifts do not matter. Meaning, if an employee clocks in at 11:00 PM and clocks out at 7:00 AM the next day. His total working hours would be 1 hour the first day and 7 hours the next.

There is an In_Out flag to represent if the badge swipe was In/Out. And there can be n number of breaks in between. We need to account for the breaks when calculating the total sum though.

Could someone help out here please?


texts: Should i separate ContactInfo (sub-entity) from MyTable (main-entity) or Not?

texts:
I am designing a database of a school management system. I have one to one relationship of MyTable with ContactInfo table. I am confused whether I separate contactinfo from MyTable or not. ContactInfo record will be single against each MyTable record, but ContactInfo can be updated many times.

Should i merge ContactInfo as

or develop a separate ContactInfo as given below

Please justified your help with the solid arguments or rule.
Thanks for your help.
Note: ignore the blur fields.


texts: What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

texts:
Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following?

When I run it, I receive the following error:

Cannot find the object "#test" because it does not exist or you do not have permissions.

IF NOT EXISTS
(
SELECT
    name
FROM sys.server_principals
WHERE
    name = 'testlimiteduser'
)
BEGIN
    CREATE LOGIN [testlimiteduser] WITH PASSWORD=N'apassword', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END

IF NOT EXISTS
(
SELECT
    name
FROM sys.database_principals
WHERE
    name = 'testlimiteduser'
)
BEGIN
    CREATE USER [testlimiteduser] FOR LOGIN [testlimiteduser] WITH DEFAULT_SCHEMA=[dbo]
END

IF NOT EXISTS
(
    SELECT 1
    FROM sys.database_principals AS p
    WHERE
        p.name like 'testlimiteduser_app'
)
BEGIN
    CREATE ROLE testlimiteduser_app
    GRANT EXECUTE TO testlimiteduser_app
    ALTER ROLE [db_datareader] ADD MEMBER testlimiteduser_app
    ALTER ROLE [db_datawriter] ADD MEMBER testlimiteduser_app
    ALTER ROLE testlimiteduser_app ADD MEMBER [testlimiteduser]
    ALTER ROLE db_owner ADD MEMBER testlimiteduser  --   https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA
END

GO

exec as user='testlimiteduser'

drop table if exists #test
create table #test (id int )
exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000')
select * from #test
revert;

Checking the documentation in https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA are fine, but it doesnt even appear db_owner works in my testing.


texts:
In MS SQL Server nolock can be used for the purpose.

Why can't we use it in Oracle and plsql?


texts: Postgres: ODBC: ERROR: SSL connection has been closed unexpectedly; Error while executing the query

texts:
I'm currently in Australia and so is the database.
Staff attempting to connect to the database from overseas (USA, UK) consistently run into the error: ERROR: SSL connection has been closed unexpectedly; Error while executing the query, when they try to connect.  People here can connect fine without that error but anyone overseas will receive that error if their query lasts longer than say 1 minute?
The database is a postgres RDS hosted in AWS Sydney region.
What is the cause of an SSL connection closing like this? Is it the server or is it just caused by the latency of someone overseas trying to connect to the database?  Is there any way to combat this?
This error was from an ODBC connection using Power BI or Tableau
We find a similar error when querying from Python or DBeaver as well.


texts: MySQL - Export two columns from large table without causing a lock

texts:
I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I want to export two small columns, one of which is an INT(11), and the other of which is a VARCHAR(20) from that table to a CSV or .SQL file (either is fine, I'll write a crosswalk for the next step no problem).

We can call the columns ColumnA and ColumnB and the table SampleTable.

MySQLdump is not the right tool for this because I can't specify the columns and I don't need to export a massive massive table just got two tiny columns.

I know I can do a SELECT INTO statement (either to create a new table with just the columns or to do a SELECT INTO OUTFILE to skip the intermediate step), but I am concerned that this will cause a table level lock on a production server. The table is InnoDB.

What's my best bet to avoid inconveniencing any live traffic on the server or locking anything?

Thanks


texts: MaridDB 10.3.29 prevents me from deleting a foreign key

texts:
This is a minimal example that triggers the error
CREATE TABLE A (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE B (
  what tinyint NOT NULL,

  a bigint NOT NULL,

  CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
  CONSTRAINT y CHECK (what > 0)
);

SELECT b.a FROM B b;

ALTER TABLE B DROP CONSTRAINT x;

When i run this in a fresh MariaDB instance (docker mariadb:10.3.28) everything works fine.
When MariaDB 10.3.29 was released the query suddenly broke. So, when i run this in a fresh MariaDB instance (docker mariadb:10.3.29) i get the following error message for the last statement:
[42S22][1054] (conn=8) Unknown column '`db`.`b`.`what`' in 'CHECK'

Some weird observations:

if i delete the y check and add it after dropping x everything works
if i remove the SELECT everything works
the b from the error message changes with the table alias from the SELECT statement
running the last statement again shows the same error
the error message makes no sense at all

So my question: what's wrong with my SQL?


texts: MySQL: update field with “x hours xx minutes”using int from another field

texts:
We have two fields that hold time data

totalTime (int): number of minutes (eg 243)
totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes')

We have a lot of different formats in totalTimeDesc ('5 min', '5m', '5 minutes', etc) and want to calculate a uniform string using the totalTime field and save that in totalTimeDesc.

Can I do this inside mysql, or do I need to write a script to make those changes?

I haven't written stored procedures before, but this might be a good use case for them.  Here is a snippet of JS that we used to do the calculation/formatting in a similar case:

// get value & calculate time/format
var mins = $(this).val();
var calcMin = mins % 60;
var calcMinText = (calcMin > 1) ? "Minutes" : "Minute";
var calcHrs = Math.floor(mins / 60);
var calcHrsText = (calcHrs > 1) ? "Hours" : "Hour";

// set time/format as calcDesc
var calcDesc = "";
if(calcHrs > 0) { calcDesc = calcHrs+" "+calcHrsText+" "; }
if(calcMin > 0) { calcDesc += calcMin+" "+calcMinText; }


texts: Understanding & Resolving Deadlock in SQL Server 2008 R2

texts:
I am trying hard to figure out what may be going wrong for below shown deadlock graph:

I am receiving alerts for deadlocks similar to above for almost every 10 minutes, it's just the query changes, action of deadlock remains the same:

**Wait Resource Page:11:11:...2820**
Lock Type:  Page
Own/Wait: Own
Mode: X
SPID: 1568
ECID: 0
Sql:
    update access
    set accessdate=accessdate  where userid= 'ABC.DEF' and companyid= '12'
    and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
    and sessioncntr=12 and tstamp <= 0x0000000000sw123
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1120
ECID: 0
Sql:
    delete access where userid= 'ADAm.Smith' and companyid= '23'
    and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
    and sessioncntr=23

**Wait Resource Page:11:11:...2830**
Lock Type: Page
Own/Wait: Own
Mode: X
SPID: 1120
ECID: 0
Sql:
    delete access where userid= 'ADAm.Smith' and companyid= '23'
    and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
    and sessioncntr=23
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1568
ECID: 0
Sql:
    update access
    set accessdate=accessdate  where userid= 'ABC.DEF' and companyid= '12'
    and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
    and sessioncntr=12 and tstamp <= 0x0000000000sw123

I've looked at fragmentation & any missing indexes , but that's all good for the database xyz as mentioned in queries above!

Note: DBID mentioned in deadlock graph says dbid 11, which is different than on e in queries that is XYZ, so dbid 11 is actually another database 'DBCapse'

Please provide any inputs that can help me in resolving and understanding this deadlock situation.


texts: Create a single-server replica set only for watching on change streams

texts:
I have a MongoDB database which is of relatively low importance. I shovel data from home automation stuff into it (mostly sensor data, sunlight, switch states and so on) and don't need any replication.

I want to subscribe to change streams on that DB, and for that they must be members of a replica set. Is it OK to just add the --replSet [name] flag to the already existing server and then call rs.initiate() in it without rs.add()-ing any secondary members?

What drawbacks will this have (database size, performance, ...)?

Basically I want devices/software to insert data directly into the database, and then have some middleware watching on change streams to then publish the data via RabbitMQ.

Currently I'm inserting into RabbitMQ and MongoDB in parallel. Inserting into RabbitMQ and having a middleware insert the data into MongoDB is also an option, but I want to check both ways of doing it.

I'm not that much of a friend of replica sets as I have an important Database which holds about 80-90MB of documents (total size when exporting all collections to disc), but each member consumes around 1.5 GB of space (--logpath data excluded).


texts:
We are having table that contains 6,616,711 (6.6 million) records in it. I want to know, How to manage this table? If there is SO question similar to this please give me link. Having following concern, too.

How to manage large database?
What is best system/hardware configuration for this type of scenario?
Partitioning this table will help us?


texts: Yet Another "Connection Timeout Expired pre-handshake acknowledgement" problem

texts:
This is SQL Server 2012SP4 on Windows 2012R2, with .NET Framework 4.7.02053.

I've RDPed into the database VM and open SSMS.

It times out with this error the first time I try to connect.
If I try again, it works.
If I increase the connection timeout, it works.

But why is it happening locally?  This migrated from SQL Server 2008 and Windows 2008R2 and did not have this problem.  Since a canned application that can't specify the connection timeout is failing, we need to solve this.

IPv6 is disabled both at the Windows level and in SQL Server Config Manager.

Thanks


texts: Renaming Sql Server database unusual result

texts:
This is some behaviour I observed from renaming databases. What is the cause of this? The example is simplified for the purposes of this question.

CREATE DATABASE [Database_A]
GO
CREATE DATABASE [Database_B]
GO
CREATE DATABASE [Database_B_Copy]
GO
-- create a table for testing purposes
USE [Database_B]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B].[dbo].[Table_1] (Column1) values ('[Database_B]')
GO
-- create an copy of the table with the same schema
USE [Database_B_Copy]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B_Copy].[dbo].[Table_1] (Column1) values ('[Database_B_Copy]')
GO
-- create a stored procedure to demonstrate the problem
USE [Database_A]
GO
CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SELECT * FROM Database_B.dbo.Table_1
END
GO

[1]. when I run the stored procedure TestSP the result as expected is the contents of DatabaseB Table_1.

ID      Column1
1       [Database_B]

[2]. Renaming Database_B to Database_B_Original will cause this error as would be expected:

Msg 208, Level 16, State 1, Procedure TestSP, Line 4
Invalid object name 'Database_B.dbo.Table_1'.

[3]. Taking Database_B_Original offline, the error from the stored procedure changes to:

Msg 942, Level 14, State 4, Procedure TestSP, Line 4
Database 'Database_B_Original' cannot be opened because it is offline.

[4]. I then renamed Database_B_Copy to Database_B. I would have expected the stored procedure to work since the a database named Database_B was available. However the error remains the same.

Finally if I restart Sql Server this magically fixes the problem and the output is, as expected:

ID      Column1
1       [Database_B_Copy]

Is SQL Server caching the tables and database name information incorrectly? Is there a way to force Sql Server to refresh the information?

I would like to gain a better understanding of what the underlying cause is for this issue and whether it's by-design or an error with sql server.


texts: Column Relationship between DBA_AUDIT_TRAIL and DBA_HIST_ACTIVE_SESS_HISTORY

texts:
I have been trying to find out which column in DBA_AUDIT_TRAIL links to DBA_HIST_ACTIVE_SESS_HISTORY but haven't succeeded up to this point.

My goal is to get the sql statements for each session in DBA_AUDIT_TRAIL using sql_id in DBA_HIST_ACTIVE_SESS_HISTORY,

Any help pointing me in the right direction will be appreciated.


texts: ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

texts:
Consider these two functions:

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

As far as I understand, they produce exactly the same result.
In other words, the order in which you list the columns in the PARTITION BY clause doesn't matter.

If there is an index on (A,B,C) I expected the optimiser to use this index in both variants.

But, surprisingly, optimiser decided to do an extra explicit Sort in the second variant.

I've seen it on SQL Server 2008 Standard and SQL Server 2014 Express.

Here is a full script that I used to reproduce it.

Tried on Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

and Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)
    May 27 2016 15:33:17
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

with both old and new Cardinality Estimator by using OPTION (QUERYTRACEON 9481) and OPTION (QUERYTRACEON 2312).

Set up table, index, sample data

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

Queries

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

Execution plans

PARTITION BY A,B

PARTITION BY B,A

Both

As you can see, the second plan has an extra Sort. It orders by B,A,C.
The optimizer, apparently, is not smart enough to realise that PARTITION BY B,A is the same as PARTITION BY A,B and re-sorts the data.

Interestingly, the third query has both variants of ROW_NUMBER in it and there is no extra Sort! The plan is the same as for the first query.
(The Sequence Project has extra expression in the Output List for the extra column, but no extra Sort). So, in this more complicated case the optimiser appeared to be smart enough to realise that PARTITION BY B,A is the same as PARTITION BY A,B.

In the first and third queries the Index Scan operator has property Ordered:True, in the second query it is False.

Even more interesting, if I re-write the third query like this (swap two columns):

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

then the extra Sort appears again!

Could somebody shed some light? What is going on in the optimiser here?


texts: Query with ROWNUM returns differents rows on every execution

texts:
I have a query that is used to search for a list of sub divisions in our network. When the query is executed without any filter, it returns a different amount of rows on every execution

SELECT DISTINCT
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM <= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME

Execution 1

ZRESUBDIV    SUBDIV_NAME
------------ ------------------------------------------------------------
262          ALEXANDRIA
400          ALLANWATER
671          ASHCROFT
372          BALA
277          BEACHBURG
200          BRIDGE
374          CARAMAT
543          CENTRAL BUTTE
208          CHAPAIS
324          CHATHAM
340          CHICAGO
...

Execution 2

ZRESUBDIV    SUBDIV_NAME
------------ ------------------------------------------------------------
670          ALBREDA
262          ALEXANDRIA
400          ALLANWATER
671          ASHCROFT
372          BALA
277          BEACHBURG
604          BLACKFOOT
200          BRIDGE
607          CAMROSE
354          CASO
543          CENTRAL BUTTE
...

There is also missing data: the subdivision Baton Rouge never appears although it is in the data.

If I remove the ROWNUM, all data is correctly returned. I need to leave ROWNUM since the query support a WHERE clause with a filter on the subdivision name.

What is the cause of this?


texts:
I'm starting to using SQL statements in my Oracle database XE (tables and relations made by me) and I'm having a hard time to understand how can I put a column from another table.

The example in this case is:

I have two tables that are interconnected, ORDERS and PRODUCT.

In ORDERS there are the following attributes:

ORDERID (defined as Primary Key), PRODUCT_NAME (a Foreign Key from the PRODUCT table) and QUANTITY.

In PRODUCT there are:

NAME (defined as Primary Key) and PRICE.

I want to add a column that gets the PRICE from the PRODUCT table and insert it to the ORDERS table according to the PRODUCT_NAME, obviously.

How can I do it?
Are there any workarounds?

Please, keep in mind that I'm a newbie and my knowledge is very limited.


texts: Migrate existing SQL Server database file to smaller files

texts:
I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple smaller files with file size limited at just below 512GB each.

Ive been doing some research online. It is not clear to me whether I should add filegroups or just add files to my primary filegroup.

How do I move data from the existing file (1TB) to the smaller files (512GB) safetly?

Has anyone done anything similar?


texts: How to get only month from MySQL date/time?

texts:
In my MySQL server, I have rows that have the date of my created content.
Example: 2013-03-17 02:53:47

For now, I am using:

$date = $fetch[thedaterownamegoeshere];

<?php echo $date; ?>

But instead of getting the full content of the date (2013-03-17 02:53:47), I'd like to get only the month – in this example, I wish to have the number "03" as the result. How can I do that?


texts: Is Transaction Log Filling up Because Log Truncation is Delayed?

texts:
The volume that holds my transaction log seems to be constantly at 97% full, so I am trying to identify the cause.

DBCC SQLPERF(logspace)

Yields the following results:

There don't seem to be any old transactions that are holding up the transaction log truncation:

SELECT
    database_transaction_begin_time,
    database_transaction_state,
    database_transaction_begin_lsn
FROM sys.dm_tran_database_transactions;

I suspect SQL Server is unable to truncate the transaction log because it cannot take a backup.
I run the following query on my database:

SELECT
    database_id,
    recovery_model_desc,
    log_reuse_wait,
    log_reuse_wait_desc
FROM sys.databases;

The result says that "model" (database_id=3) has log_reuse_wait_desc=LOG_BACKUP and recovery_model_desc=FULL.
This tells me that a log backup is required before the transaction log can be truncated (https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017#Truncation).

Next, I run this query to determine when the last log backup was taken (see https://stackoverflow.com/questions/40050221/sql-server-what-exactly-is-the-backup-finish-date-in-master-sys-databases):

SELECT
    d.dbid,
    b.type,
    MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.sysdatabases d
    LEFT OUTER JOIN msdb..backupset b
         ON b.database_name = d.name
            AND b.type = 'L'
GROUP BY d.dbid, b.type
ORDER BY backup_finish_date DESC;

backup_finish_date=null, which tells me that model has never had a log backup.

These queries lead me to conclude

The transaction log cannot be truncated because a backup needs to be taken of "model".
A backup of "model" has never been taken.

Am I interpreting these results correctly?

I am confused because "model" is a System database, and it doesn't seem like I should have get involved with its log backups or truncation.

Can anyone recommend a way to free up space in the transaction log?


texts: How to create an additional boolean column in a view whose value depends on a subquery?

texts:
Following is a view that I have created

CREATE VIEW [ForMasterTable]
WITH SCHEMABINDING AS
SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData]

FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]

I want to add an additional boolean column to this view the value of which will depend on the result of a condition on a subquery

CASE
WHEN EXISTS (
     SELECT ErrorID
     FROM [ErrorTable]
          WHERE DetailID IN
                (SELECT [DetailID]
                 FROM [DetailTable]
                      WHERE MasterID = [MasterTable].[MasterID])
            )
THEN
     1
ELSE
     0
END

How do I something like this in my view? Also I am assuming that a nested query would exponentially degrade the performance as the number of rows go up. Any help with this would be greatly appreciated.


texts: Benefit of filegroups and setting filegroups to read-only

texts:
Can someone cite me a real world scenario on when changing multiple filegroups to read-only is a good option and when to use them? What benefits are there if you set it to read-only?

On a database with multiple filegroups, do you have to do backups for the whole database and also backup each of the files of that filegroup? Can you also give me an example of when filegroup backups will be used? I don't see why it would be beneficial to backup a filegroup when you can just backup the whole database. Hoping I could get a real world experience where this filegroup backup would be ideal


texts: Where to put utility (not administration) stored procedures in SQL Server?

texts:
Possible Duplicate:
  Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I have some simple user-defined functions for string manipulations. Most of them are actually CLR functions but not all of them are. I use them from multiple databases on the same server. What is the proper place to put these?

I've been placing them in the master DB so far. Is that a bad idea? If so, why? Is it standard practice to have a utility database that has no data but only progammability objects?


texts: While logged in as root user, how can I check if any of the tables are crashed in all of the databases?

texts:
Long story short, the server experienced an unscheduled restart, while all the databases were in use. This caused some of the tables in some of the databases to be marked as crashed. Since there's a couple of hundred of them, I was wondering if there's a better way of checking the status, than simply going through individual tables.
I know that I can use SHOW TABLE STATUS WHERE comment LIKE '%crash%' for one database. Is it possible to do that for all of the databases, if I'm logged in as a root user (command line or phpMyAdmin)?
I've also looked at SELECT * FROM INFORMATION_SCHEMA.TABLES, but as far as I could tell, the info I need can not be found there.


texts: Postgresql: how to remove a GRANT on multiple tables?

texts:
I have  a big database with 40 schemas and plenty of tables inside each one. Some tables have a GRANT SELECT ON TABLE schema1.table1 TO user1grp. I no longer need user1grp group role.

If I delete this group role, are the related GRANT going to be removed from related tables?

Or do I need to remove the GRANT on each table before deleting the group role? If this is the good process, is there a way to list all tables related to this group role?


texts: What code underlies the PG_GET_LATE_BINDING_VIEW_COLS sys information function?

texts: We want to transition to late-binding views, but late-binding view metadata is not presented by some clients (workbench/J, Toad, pgcli) which makes it hard to discover column names in these views. AWS provides a function, PG_GET_LATE_BINDING_VIEW_COLS, to get the metadata, but we can't seem to load this into a redshift table. Are there PG sys tables storing the late-binding view columns, or what underlies this function?


texts: Query that ranks students order by marks and give the position of student even when there are students with equal marks

texts:
I need an sql that will rank students order by marks scored in a specific examtyp e.g CAT1 and give exact position even if the students have equal marks e.g in this sample data admNo 2525 and admNo 4093 scored equal marks in CAT1, so i need an sql that will fetch the positions as the same and skip give the next student number 3. the following sql that i tried does not consider a scenario like this.

$sql = "SELECT q1.rownum
FROM
    (
        SELECT *, @rownum:=@rownum + 1 AS rownum
        FROM $table t, (SELECT @rownum:=0) r
        WHERE examtyp =? AND termId=? AND year=?
        ORDER BY points DESC
    ) q1
WHERE q1.admNo=$admNo";

resid  admNo streamIdexamtyptermIdmarks gradepointyear
1       2129    0   CAT1    1   525     C   62  2013
2       4093    0   CAT1    1   569     B+  69  2013
3       2129    0   CAT2    1   550     B+  67  2013
4       4093    0   CAT2    1   556     B+  68  2013
6       2129    0   FINAL   1   559     B+  68  2013
7       2129    0   AVERAGE 1   545     B   66  2013
7       4093    0   FINAL   1   581     B+  70  2013
8       4093    0   AVERAGE 1   569     B+  69  2013
9       2525    9   CAT1    1   569     B+  69  2013


texts: Spatial Loader DB2 giving error while importing shape file

texts:
While I am trying to import a shapefile using db2se import_shape file utility,

db2se import_shape <dbname> -userId <userid> -pw <password> -fileName U:\ShapeFiles\ShapeFilesMarch2014\SubLimits\Africa_Criteria_Data\AF-CIV.shp -inputAttrColumns N(FIPS_CNTRY) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\AF-CIV.txt -client 1

GSE0206N  A SELECT statement failed.  SQLERROR = "SQL0805N  Package    "DB2GSE.GSEIEIMP 0X4141414141644459" was not found.  SQLSTATE=51002".

Any idea why this error happens? I tried in all forums and not able to debug it.


texts: Nightly Excel Spreadheet import into mssql database

texts:
I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and pulling the Excel file from another server. I am trying to figure out how I can loop through the columns and pull out the data I need. If I could rearrange the data, I would but am currently stuck with what I have.

In my current table tblHW I have Columns such as PmpCount, , NumberStages, Pmpmodel_pmp1, serialnum_Pmp1, pmpModel_pmp2, Pmpmodel_pmp2, serialnum_pmp2, partnum_motor1, serialnumberMotor1, etc…. I apologize in advance for not being able to post a real table or a picture.

Example:

|Name      | PmpCount| numstages| pmpmodel_pmp1| stages_pmp1| Sn_pmp1|

|AN 91-23G |        4|       500|        FX2347|         250|  354197|

|BR DN 895R|        5|       521|         D2442|          45|  875164|

|ALN 1-60J |        5|       521|        H21342|          95|  594126|

--

|pmpmodel_pmp2| stages_pmp2| sn_pmp2| Partnum_mtr1| sn_mtr1|

|FX2347       |         250|  354198|         NULL|    NULL|

|FX17500      |         143|  102547|        M7544| 4512241|

|FX17500      |         143|  458790|        M7544| 4512364|

The information I want to move into tblHW comes from the tbl Pull_Down. Here is the setup:

|Name      | Run_ID               | Part1| SN1     |    Attribute1_7|

|AN 21-919G| Oct 08, 2013 / 100845|   BOD|       NA|           3RD U|

|FR 55-013A| Oct 17, 2013 / 100853|   Pmp| 2EA3A022|              78|

|FR 55-013A| Oct 01, 2014 / 101383|   Cbl|      N/A|        REDALEAD|

|FR 43-223J| Apr 03, 2013 / 100594|   BOD|       NA|           3RD U|

|VH 204    | May 17, 2014 / 101145|   BOD|    3RD U|

|Part2| SN2      | Attribute2_7| Part3   | SN3     | Attribute3_7|

|Pmp  |  2EA3F379|           78|      Pmp| 2EA3N380|          117|

|Pmp  |  2EA3C020|          117|      Pmp| 2EA3Y021|          117|

|MLE  | J14312161|          120|      BOD|      N/A|        3RD U|

|Other|        NA|          Pmp| 2EA2X774|       78|

|BOD  |      NULL|          Pmp| 2EA4F075|       38|

A bit more information. I am receiving this information in the form of five excel spreadhsheets each with over 400 columns. The columns giving me the biggest headache are the 20 part columns that I need to place into the SQL table.

I need to somehow move each row into the tblHW but need to do something like this:

The first row AN 21-919G needs to have SN1 to be inserted into sn_mtr1 since it is a BOD, SN2 into SN_pmp1 since it is a PMP, and SN3 into sn_pmp2 since it is the second PMP here. I also need to get the pmp count, in this case 2 and then add the attribute1_7 and attribute2_7 to put into numstages when the prts are PMP.


texts:
I am using mysql 5.6 and want to remove all characters from json output of db column and after that required sum of numeric values

Input:

  {"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"}
  {"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"}
  {"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}

Output:

0+0+0+50+0+0 = 50
0+10+40+10+0+0 = 60
0+40+35+22+10+0 107


texts: Cannot truncate a table referenced in a foreign key constraint even with no child data?

texts: With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is actually empty you still cannot TRUNCATE the parent table?


texts:
I bumped into a use case where I wanted to write a SQL query to retrieve a row based on 2 MAX values of the table. I tried a couple of combinations but no luck as of yet. Here's a sample table and the requirement:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/1/1    1122        $12
2       sf-001      2018/2/19   7788        $4
3       sf-002      2018/1/2    2233        $56
4       sf-003      2018/3/14   3344        $77
5       sf-003      2018/3/14   4455        $51
6       sf-003      2018/3/2    6677        $88
7       sf-004      2018/11/11  8880        $73

The SQL query should return the rows with MAX updateDate per cardCode. E.g. for "sf-001" it should return row 2 (unitValue of $4). If it has two or more identical updateDates then it should check for the MAX updateTS. E.g. for "sf-003" it should return row 5 (unitValue of $51). So the query should result in the following output:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/2/19   7788        $4
2       sf-002      2018/1/2    2233        $56
3       sf-003      2018/3/14   4455        $51
4       sf-004      2018/11/11  8880        $73


texts: Percentage calculation with case not working as expected

texts:
I've been racking my brain on this since yesterday afternoon, I figure it is time to call on those smarter than I. I have some data represented by the following. Sorry I have to use an image, I cannot figure out how to format to look like a table.

create table fico_scores
(
    fundingyear int,
    fico int,
    Correspondent varchar(50)
)

insert into fico_scores (fundingyear,fico,Correspondent)
        values (2017,593,'Amer Equit Mtg'),(2017,703,'Amer Equit Mtg'),
(2018,684,'Amer Equit Mtg'),
               (2017,691,'Amer Equit Mtg'),(2017,650,'Amer Equit Mtg'),(2017,697,'Am Corp'),
               (2017,652,'Am Corp'),(2017,792,'Am Corp'),(2018,0,'Am Corp'),
               (2018,796,'Am Corp'),(2017,729,'Appr Fun Corp'),(2017,748,'Appr Fun Corp'),
               (2017,780,'Appr Fun Corp'),(2018,770,'Appr Fun Corp'),(2017,663,'Appr Fun Corp'),
               (2018,752,'Appr Fun Corp'),(2018,715,'Cacl Risk Analytics'),(2018,683,'Cacl Risk Analytics'),
               (2018,735,'Cacl Risk Analytics'),(2017,693,'Cap Mtg LLC'),(2017,624,'Cap Mtg LLC'),(2018,746,'Cap Mtg LLC'),
               (2018,666,'Cap Mtg LLC'),(2018,669,'Cap Mtg LLC')

I Have the following code, and when running it looking at only one Correspondent everything looks fine as you can see below the code.

select fundingyear,case
                when fico = 1 then 'Less than 620'
                when fico = 2 then '620-679'
                when fico = 3 then '680-719'
                when fico = 4 then '720+' end as fico,
                sum(countfico) as count_Fico, count(countfico) * 1.0 /
           SUM(count(countfico)) OVER (PARTITION BY fundingyear) as fico_perc
FROM
(
    select fundingyear, case
                    when fico < 620 then 1
                    when fico >= 620 and fico <= 679 then 2
                    when fico >= 680 and fico <= 719 then 3
                    when fico >= 720 then 4
                    end as fico, count(fico) as countfico
    from vw_LoanRpt
    where Correspondent = 'Am Corp'
    group by fico,FundingYear
) t
group by FundingYear,fico

As you can see the percentages line up with the counts correctly.
But when I run the code to view all correspondents it looks really different.

My question is, what am I doing incorrectly that is causing such a skew in the calculations?

Again sorry for using images so much. I will learn this formatting stuff eventually.

TYIA!

                    2017          2018
Less Than 620      521 | 14%    499 | 13%
620 - 679         2027 | 28%   2619 | 31%
680 - 719         1260 | 21%   1661 | 20%
720+              2137 | 37%   3320 | 35%


texts: MySQL not responding, Writing to net status

texts:
I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB.

Here's the show processlist before I rebooted it:

3629079 user    ip:4815 web Query   2324    Writing to net  SELECT * FROM web.files order by file_name_eng desc
3632228 user    ip:4817 null    Sleep   2323        null
3632453 user    ip:4818 null    Sleep   2323        null
3649490 user2   127.0.0.1:28050 null    Query   1967    Locked  UPDATE web.files SET date_publish_test = NOW(), uuser_id = 2 WHERE file_id = 20454

That is a simple select, here's the explain of it (35K records in total, type MyISAM):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  files   ALL null    null    null    null    30593   Using filesort

Version: 5.0.54a-enterprise-gpl-nt on Windows 2003.

Am I going at this properly?


texts:
We're clearing out some old MySQL users that connected from remote hosts during a database transfer.

Why is this DROP USER query failing for the user asdfasdf_imac4? I'm logged in as root.

mysql> select user,host from mysql.user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| root             | 127.0.0.1            |
| root             | ::1                  |
| debian-sys-maint | localhost            |
| forum            | localhost            |
| root             | localhost            |
| asdfasdf_imac4   | ‘303.303.303.90’     |
| asdfasdf_imac4   | ‘303.303.303.92’     |
+------------------+----------------------+
7 rows in set (0.00 sec)

mysql> drop user 'asdfasdf_imac4'@'303.303.303.90';
ERROR 1396 (HY000): Operation DROP USER failed for 'asdfasdf_imac4'@'303.303.303.90'


texts: How can I 'fake' stats for generate_series data?

texts:
With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.1.4
*/
explain analyze
    select foo, generate_series(1,10)
    from (select 'A' as foo) z
;
/*
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.014 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
*/

note: (cost=0.00..0.02rows=1width=32) (actual time=0.010..0.014rows=10loops=1)

This is improved in 9.2, giving the standard 1000 estimate:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.2.1
*/
explain analyze
    select foo, generate_series(1,10)
    from (select 'A' as foo) z
;
/*
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..5.02 rows=1000 width=32) (actual time=0.014..0.020 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
*/

note: (cost=0.00..5.02rows=1000width=32) (actual time=0.014..0.020rows=10loops=1)

How can I 'fake' this behaviour in 9.1 and earlier?


texts: Scheduled backup task doesn't always backup all databases despite always saying job successful

texts:
I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.

It quits with success every day but some days it quits with success only after backing up a few of databases. It can be different number of databases each time. Most days it successfully backups all databases but sometimes 2 backup successfully, sometimes 5, etc.

I don't see any errors in job history, event viewer or sql server log.

Backups are taking place to a local disk, although the folder is a "junction" to a folder on an expandable storage volume.

OS is Windows 2003 64bit running Sql Server 2008 web edition 64 bit as a virtual machine running on Vmware ESXi 5 host.

Stored Procedure:

ALTER PROCEDURE [dbo].[backup_all_databases]
@path VARCHAR(255)='c:\backups\'

AS

DECLARE @name VARCHAR(50) -- database name
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @dbIsReadOnly sql_variant -- is database read_only?
DECLARE @dbIsOffline sql_variant -- is database offline?

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
AND version > 0 AND version IS NOT NULL

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.bak'

SET @dbIsReadOnly = (SELECT DATABASEPROPERTY(@name, 'IsReadOnly')) -- 1 = Read Only
SET @dbIsOffline = (SELECT DATABASEPROPERTY(@name, 'IsOffline')) -- 1 = Offline

IF (@dbIsReadOnly = 0 OR @dbIsReadOnly IS NULL) AND @dbIsOffline =0
BEGIN
    BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
    WAITFOR DELAY '00:00:20'
END

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Any suggestions please?


texts: Leading zeros are truncated when I spool to CSV

texts:
I have spooled a file to CSV using sqlplus. Everything works great but there is one  issue. I have mobile numbers which start with 0, for example 04589 and 02586.

After the spooling is complete, 0s are truncated and the end results are 4589 and 2586.

I want the output to display in CSV as:

04589
02586


texts: SQL Server Configuration.ini File. SSIS Data Required?

texts:
I am planning a new SQL Server installation based of the settings from a previous installation on another server.

I am using the Configuration.ini file. Since SSIS is not required can I exclude the below lines from the config file? Or are these required anyway?

Thanks!

; Startup type for Integration Services.

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account.

ISSVCACCOUNT="NT AUTHORITY\NetworkService"

; Controls the service startup type setting after the service has been created.

ASSVCSTARTUPTYPE="Automatic"


texts:
I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture:

Suppose I have several databases running under one instance of SQL Server:

(1) Is there a different plan cache per database? More specifically, when I examine sys.dm_exec_cached_plans am I looking at database-specific information or instance-wide information?

(2) Is 'optimize for adhoc workloads' set per database or per instance?


texts: Database restore on new server and upgrade to SQL Server 2012

texts:
I have to move a 5 TB SQL Server 2008 database that has mirroring setup to a new server that will have SQL Server 2012 and always on AG setup.

I am wondering if detach/attach would be faster or backup/restore. The compressed split backup is about 1.2 TB.

I understand that detach/attach means database will not be available on the source.

Any advise on matters to consider, as this will be a first SQL Server upgrade for me.

Thanks as always to the DBA community.


texts: Best way to create a login with hashed password?

texts:
What is the correct way to create a login with a hashed password because .

I've tried the method explained in https://msdn.microsoft.com/en-us/library/ms189751.aspx but it dosn't work.

Query1

CREATE LOGIN administratori WITH hashed_password = '12345'
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO

If somebody could tell me how to write the hashed password part.

Should i do it like this , and if i do it like this when i authenticate (login) how do i tell sql it should hash the password first.

CREATE LOGIN administratori WITH PASSWORD = (HASHBYTES('SHA2_512','12345'))
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO

So please what would be the best way to create a login with a hashed password (preferably hashed with SHA2_512)with a query.And if anyone can tell me where the mistake is at 'Query1' i would be grateful.


texts:
I've reviewed these two existing questions:

Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install?
Installing SQL Server Express in parallel of Standard

...and I believe mine to not be a duplicate, as the details of my scenario differ slightly (and in an important way). If I'm mistaken please let me know and I'll try to edit my question appropriately.

Question:

I already have two named instances of SQL 2014 Standard Edition installed, but no default instance. I wish my default instance to be a SQL 2014 Express Edition installation (MSSQLSERVER).

But I see that the Express installer wants to put its files in C:\Program Files\Microsoft SQL Server\, where files and folders already exist from the earlier Standard Edition named instance installations (e.g. .\110\*, .\120\*, .\Client SDK\*, etc.).

If I proceed with the Express installation and accept the default instance root directory (again, which already exists under Standard), will I cause any problems for the currently running Standard Edition named instances?

Or instead should I indicate a new instance root directory to be created (e.g. C:\Program Files\Microsoft SQL Server Express\) for the new Express installation?

I'd prefer the former if possible, in order to keep everything consolidated, but I also don't want to cause trouble for myself if I can avoid it.


texts: mySQL Group Replication Error "ERROR 3092 (HY000)"

texts:
The full error (on 2nd node) is:
mysql> START GROUP_REPLICATION;

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

This is after starting the 1st server without error.
The log shows:
2021-02-27T19:05:45.079426Z 16 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:10.874606Z 16 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-02-27T19:06:10.875454Z 17 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2021-02-27T19:06:10.878134Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.878182Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.881114Z 18 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:13.300659Z 0 [ERROR] [MY-011516] [Repl] Plugin group_replication reported: 'There is already a member with server_uuid 6227f63c-dd97-11ea-8989-86fbcb300464. The member will now exit the group.'

I am following these instructions.
The instructions say that the UUID should be the same for all servers.
My droplet is a LAMP Ubuntu 20.04 droplet. mySQL 8.0.23
Anyone run into this? Any thoughts on what's going wrong? I've checked and double-checked the procedure and made sure I followed it as best I could.


texts: MySQL Password Validation - No previous passwords

texts:
I wonder if there is any possibility to use the password validation for MySQL 5.6 (https://dev.mysql.com/doc/refman/5.6/en/validate-password.html) to prevent for example the last 4 passwords.

The possibility to prevent a user from choosing a simple password but allow the user to always choose the same password seems to be a little bit odd.

Sadly I am not able to upgrade the MySQL server.


texts: Do SQL Server Views update their data types automatically based on what tables feed them?

texts:
Someone mistakenly created a bunch of nchar fields in database tables.
I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside.

In my script, I currently have it limited to pull only the tables that have nchar types, assuming that the views that are fed by the tables will know to update their values. Is this assumption correct, or should I be including views in this list as well?

For reference, the script in its current form is below (mostly taken from this great SO answer):

declare @tn nvarchar(128)
declare @cn nvarchar(128)
declare @ln int

declare @sql as nvarchar(1000)

declare c cursor for
    select cols.table_name,cols.column_name,cols.character_maximum_length
    from information_schema.columns cols
        inner join information_schema.tables tabs
            on (cols.TABLE_SCHEMA = tabs.TABLE_SCHEMA and cols.TABLE_NAME = tabs.TABLE_NAME)
    where cols.data_type ='nchar' and tabs.TABLE_TYPE = 'BASE TABLE'

open c
fetch next from c into @tn, @cn, @ln

while @@FETCH_STATUS = 0
begin

    set @sql = 'alter table ' + @tn + ' alter column '
        + @cn + ' nvarchar(' + convert(nvarchar(50), @ln) + ')'
    exec sp_executesql @sql

    set @sql = 'update ' + @tn + ' set ' + @cn + ' = LTRIM(RTRIM(' + @cn + '))'
    exec sp_executesql @sql

    fetch next from c into @tn, @cn, @ln
end

close c
deallocate c

References

Convert all NCHAR columns to NVARCHAR columns in a database or table


texts: Simple PL/SQL code to throw NO_DATA_FOUND exception

texts: In a recent interview I was asked to write a simple PL/SQL code to throw NO_DATA_FOUND exception without actually raising it from the code. I am wondering what could be a simple pl/sql snippet to do so. Probably using dual?


texts:
I have:

a CSV file (5000+ records);
an empty item table;
am empty favourite table;
a number of other empty tables.

I would like to populate the item table with the contents of the CSV file. Then I would like to create some foreign key relationships between both tables.

In what order do I do this?

I keep receiving an error every time I attempt this:

#1452 - Cannot add or update a child row: a foreign key constraint fails...

Note that my empty favourite table already has some foreign key constraints set up. I am using PhpMyAdmin and MySQL Workbench.

Am I supposed to have all the tables populated with data before I set up foreign key relationships? Or do I set up the relationships then populate with data?


texts: How can I fake inet_client_addr() for unit tests in PostgreSQL?

texts:
I have a simple stored procedure whose return value depends on the value of inet_client_addr().  How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure?

The only solution I've come up with so far is to create a wrapper function around inet_client_addr():

CREATE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT inet_client_addr();
$$ LANGUAGE sql;

Then use that in my function:

CREATE local_connection() RETURNS BOOLEAN AS $$
    SELECT my_inet_client_addr() = '127.0.0.1';
$$ LANGUAGE sql;

Then in my unit test, I can re-define my_inet_client_addr():

BEGIN;
SELECT PLAN(2);
REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT '127.0.0.1'::INET;
$$ LANGUAGE sql;

is(local_connection(),TRUE,'Connection from 127.0.0.1 is local');

REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT '192.168.1.1'::INET;
$$ LANGUAGE sql;

is(local_connection(),FALSE,'Connection from 192.168.1.1. is not local');

ROLLBACK;

Is there any way to accomplish the same without the wrapper function my_inet_client_addr()?


texts: Linked Server Error: Cannot fetch a row from OLE DB provider "MSDASQL" for linked server

texts:
I have SQL Server 2016 SP2 CU6 Enterprise Edition on a server Serv-A.
And PervasiveSQL on another machine/VM Serv-B.

I have installed 64 bit ODBC Driver of PervasiveSQL on Serv-A and created a System DSN named PervDSN.

I created a linked server using PervDSN, named [Server_Name].

Now, when I execute these SELECT statements

select * from [Server_Name].[Database_Name]..Table_Name;

select * from openquery([Server_Name], 'select Column_Name from Table_Name') Table_Name')

I get same error

Msg 7399, Level 16, State 1, Line 5
The OLE DB provider "MSDASQL" for linked server "Server_Name" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 5
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "Server_Name".

and core dump created in SQL Logs. Here is the main error it has

  2019-04-25 23:14:11.22 spid152     DumpCallbackHk::CreateCabFile: at
  2060 g_CabDdfFile file is null, normally because nocollectors added
  any files, exiting 2019-04-25 23:14:11.22 spid152
  DumpCallbackHk::CollectFiles: at 481 failed at CreateCabFile with
  error: 0x80004005 2019-04-25 23:14:12.89 spid152     External dump
  process return code 0x20000001. External dump process returned no
  errors.

  2019-04-25 23:17:30.42 spid152     ***Stack Dump being sent to
  C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump0164.txt 2019-04-25
  23:17:30.42 spid152     SqlDumpExceptionHandler: Process 152 generated
  fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
  terminating this process. 2019-04-25 23:17:30.42 spid152     *
  ******************************************************************************* 2019-04-25 23:17:30.42 spid152     * 2019-04-25 23:17:30.42 spid152
  * BEGIN STACK DUMP: 2019-04-25 23:17:30.42 spid152     *   04/25/19 23:17:30 spid 152 2019-04-25 23:17:30.42 spid152     * 2019-04-25
  23:17:30.42 spid152     * 2019-04-25 23:17:30.42 spid152     *
  Exception Address = 00000001800157FD Module(odbcci64+00000000000157FD)
  2019-04-25 23:17:30.42 spid152     *   Exception Code    = c0000005
  EXCEPTION_ACCESS_VIOLATION 2019-04-25 23:17:30.42 spid152     *
  Access Violation occurred writing address 00000000468C0098 2019-04-25
  23:17:30.42 spid152     * Input Buffer 124 bytes - 2019-04-25
  23:17:30.42 spid152     *               select * from
  [Server_Name].[Server_Name]..Table_Name;

I'm stuck on this for last 2 days. Solution will be appreciated.

Thanks!


texts: Is there any database with versioning (column based) built in?

texts: I'm wondering if there is a database (SQL or NOSQL) which provides versioning for for the records ( if the column/field/property is enabled for versioning). I'm developing an authentication software and I need to provide a log /"history" for a number of properties so I'm wondering if there is a built-in feature or I need to reinvent the wheel one more time.


texts: What is the recommended memory_target and memory_max_target for ORACLE 12 DB running on a dedicated server with 32GB RAM?

texts:
What is the recommended memory_target and memory_max_target for ORACLE DB running on a dedicated linux server with 32GB RAM?

The server is dedicated to the Oracle 12 database.

Is there any recommendations in such case?


texts: Exadata: how to backup system before upgrade rpm packages?

texts:
My PCI scanned found some vulnerabilities on my Exadata. I must upgrade glibc package.

Before that I want to backup the system not to ruin something. By which tool can I backup OS?

I used acronis, clonezilla but maybe I have old version , because they are not able to backup the system.

Please, give me any advice.


texts: Mysql Alter Tables took 5 hours to add 1 column

texts:
Had an issue recently where to add a column on a table with 1 million rows took 5 hours. I added a column with default value of 1. This was done during the night so there was not much activity on the database but it did have many locks once this started.

Looking at the server stats CPU, memory and hard drive space were all well below half of their maximums. So I'm not sure why this took so long the table in question only has 7 columns including the new one. Data types are int(11), datetime or varchar(45).

I'm using MySQL Cluster 7.3.3, with 4 data nodes all of which are in the same datacenter.

Can anyone advise why it would take 5 hours to simply add a column?

Table structure:

CREATE TABLE ddi_t (
    id int(11) NOT NULL AUTO_INCREMENT,
    ddi varchar(45) NOT NULL,
    is_deleted tinyint(1) NOT NULL DEFAULT '0',
    created_time datetime NOT NULL,
    module_group_id int(11) DEFAULT NULL,
    created_user_id int(11) DEFAULT NULL,
    supplier_id int(11) DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY uq_ddi_1 (ddi),
    KEY fk_ddi_1_idx (module_group_id)
) ENGINE=ndbcluster AUTO_INCREMENT=935352 DEFAULT CHARSET=latin1


texts: Efficiently searching geolocations - LAT/LNG and DECIMALs

texts:
I have a database with > 7.5 million rows (and growing), each with an image record that has a specific LAT/LNG geolocation representing where the photo was taken, stored as DECIMAL values -

 mysql> describe image_meta;
 +---------------------+------------------+------+-----+-------------------+----------------+
 | Field               | Type             | Null | Key | Default           | Extra          |
 +---------------------+------------------+------+-----+-------------------+----------------+
 | id                  | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
 | media_id            | varchar(255)     | YES  | UNI | NULL              |                |
 | user_id             | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_unix    | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_zulu    | datetime         | YES  |     | NULL              |                |
 | latitude            | decimal(12,10)   | YES  | MUL | NULL              |                |
 | longitude           | decimal(13,10)   | YES  | MUL | NULL              |                |

I want to search within a rectangular area, defined by LAT/LNG, and the queries are very slow.

I am using this query:

SELECT user_id FROM image_meta WHERE (latitude BETWEEN 40.779769 AND 40.792399 AND longitude BETWEEN -73.988457 AND -73.963308);

with the result:

123569 rows in set (8 min 28.99 sec)

What's the best way to go about making this go faster? Should I search a broader area with fewer significant figures, then use PHP to narrow the results by the more precise LAT/LNG boundaries?

I'm a novice SQL guy (always had DBAs to help me in the past) but I'm doing this project solo. Thanks in advance...


texts:
In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that index for the duration of the query to increase performance.

However, I don't think that's possible, and some initial Googling seems to agree with me. Does anyone know of a DBMS that actually implements this strategy?


texts: what are steps should i do to make my local sql server connects to a remote sql server on my server?

texts:
I want my sql server to remote connect to the server sql server, so that I can access databases from my local machine.

How to do that in steps, or if there is an article online that will be nice as well.

What I am doing now is trying to connect to the remote db from my local sql server using the server IP, but it tell me "Cannot connect to ..."


texts: sql server management studio enable encryption

texts:
I see that i can manage encryption through the option:
right click database->tasks->manage database encryption

I see two options using asymmetric key or certificate as shown below

but i don't see an upload option of the key or certficate .

Can somebody please let me know how to upload a certificate and enable the encryption?

Thank you


texts: IBM FlashSystem V9000 storage - should the os, mdf and ldf and tempdb be separated onto different volumes?

texts:
Is there any benefit that justifies separating the os, mdf, ldf and tempdb onto separate volumes?

RAID and redundancy is already included.

Connection options to the host are:

16 x 16/8/4 Gb Fibre Channel
8 x 10 Gb Fibre Channel over Ethernet (FCoE); and
8 x 10 Gb iSCSI

...so I don't think there is a concern with that being a bottleneck.

The FlashSystem datasheet is here:

IBM FlashSystem V9000


texts: PG: How to show total count for this query if result is 0

texts:
I have a postgres 9.6 table called selected_media, with a column called _type that is set to either book or movie.

I want to select the COUNT of each type, in 1 single row, like this:

25 | 715

To do this, I have this query:

SELECT
  COALESCE(SUM(CASE WHEN _type = 'book'  THEN 1 ELSE 0 END), 0) AS books_count,
  COALESCE(SUM(CASE WHEN _type = 'movie' THEN 1 ELSE 0 END), 0) AS movies_count
FROM selected_media
WHERE subscriber_id = $1

The problem is 2 fold:

I want each column to show the respective total count for that _type only if the sum in that column happens to be 0 (none selected). This is because the UI/UX states "selecting 0 books or movies is the same as selecting all of them".
It needs to be fast, this table will have millions of rows in it


texts: Join query with closest date in "ON" clause

texts:
I need to make an OUTER JOIN on two tables, based on 2 columns, first id an INTEGER id, which is pretty straightforward to use in the query, but the second one is a DATETIME, which doesn't actually matches a value on the other table, but needs to "match" the closest date.

As an added bonus to the problem, on the ref table data is not as DATETIME, but on two separate columns: one DATE and another TIME.

This question is a derivate of other two I've asked before, but as I need to narrow down the problems, I preferred to make separate questions.

The question is how to choose the closest date on a best performance approach. I've tried using between (1 minute tolerance) and things like that, but the query takes too long and the explain plan gives too many rows on the involved tables.

Perhaps something like using subqueries might work faster, but I just don't know how to build them for the JOIN syntax.

References:

Join on "poor" table
Join query with different column types in "on" part

BD: MySQL 5.5.43-0+deb8u1-log


texts:
I have this function working fine:

CREATE or replace FUNCTION pointscanner(
    IN source integer,
    IN target integer,
    IN k integer,
    IN directed boolean,
    IN quant integer
)
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text,
        admin_level text, z_order integer) AS $$
DECLARE
    geomRoute geometry;
    routeBB box2d;
BEGIN
    SELECT INTO geomRoute ST_Union(geom) from route_agg($1, $2, $3, $4);
    routeBB := ST_Extent(geomRoute);

    RETURN QUERY
    SELECT pt.way, pt.name, ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance,
        pt.tags, pt.operator, pt.admin_level, pt.z_order
    FROM planet_osm_point pt, route_agg($1, $2, $3, $4) rt
    where routeBB && ST_Transform(pt.way,4326)
    order by distance asc limit $5;

END; $$
LANGUAGE 'plpgsql' VOLATILE;

But as you can see I'm calculating the route twice (route_agg) and sometimes it may be very costly. How can I improve this to run route_agg just once?


texts:
I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious.

I set up a testbed server and set access in pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 md5

Then restarted the postgresql server and after that, I could, as my regular user, use

psql -h localhost -U postgres -l

And this gave me the table listing.

On the second system pg_hba.conf matches that other file. I restarted the database server process and yet psql -h localhost -U postgres -l as my user yields this error:

2012-03-29 23:31:17 UTC LOG:  could not receive data from client: Connection reset by peer
Password for user postgres:
psql: fe_sendauth: no password supplied
2012-03-29 23:31:29 UTC LOG:  could not receive data from client: Connection reset by peer

I do not have a password configured for that user. If I

sudo su - postgres

Then try

psql -h localhost -U postgres -l

...as postgres won't work, still prompting for a password, but if I use:

psql -l

it gives the list of databases.

This server, the second one, has a "production" database on it that another developer set up. But he doesn't know why it wouldn't let me list it as my username with the -h host -U postgres arguments on that server, while the fresh-install on the first server will list them (although the first one has nothing but the default installation on it.)

The platform is Ubuntu 11.10, postgres is 9.1. Any idea what I'm overlooking?

EDIT
Here's what's happening when I try getting a database list:

user@testbed:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@testbed:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

user@testbed:/etc/postgresql/9.1/main$

On the other system:

user@production:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_hba.conf~: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@production:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
Password for user postgres:
psql: fe_sendauth: no password supplied
user@production:/etc/postgresql/9.1/main$


texts: does one PostgreSQL column have more than one sequence?

texts:
When I use this command to get the sequence of my table in PostgreSQL 13:
select pg_get_serial_sequence('public.rss_sub_source', 'id')

It shows the current sequence name is public.rss_sub_source_id_seq.
Then I use this command to query the relation of table sequence:
SELECT t.oid::regclass AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE --d.classid = 'pg_catalog.pg_class'::regclass
  --AND d.refclassid = 'pg_catalog.pg_class'::regclass
   d.deptype = 'i'
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';

It shows that the sequence name is rss_sub_source_id_seq1?
Why does the column id have two sequences in PostgreSQL? Which one is using? How to avoid this situation? is it a PostgreSQL 13 bug?


texts: Query Plans Change Performance Worse in SQL Server 2014

texts:
We recently upgraded our server from SQL Server 2008R2 to SQL Server 2014. We have a query that ran fine in 2008R2 but now in 2014 runs incredibly much slower and has a bad execution plan.

I've done several tests...

Switch the 2014 DB back to 2008/2012 compatibility mode.
Test the query using Pagination.

Both of those resulted in the query running the same as it did and fast as SQL Server 2008R2.

Why is the plan so bad and query run so long in SQL Server 2014?

This image shows 2 queries, one using rownumber the way it ran in 2008R2, and then the second is the fix with pagination. Both ran in 2014, both very different, but in 2008 we see the same performance as if we used pagination in 2014.


texts: Forwarded Records, effective way to fix them

texts:
I'm dealing with an issue in SQL Server databases of an application. This application is used for Healthcare and I only maintain the databases, I do not create the application.

Clients are facing the problem of forwarded records on their tables after a period of time. I've created a stored procedure to fix these forwarded records by creating a clustered index and dropping it again, effectivly reorganizing the table and fixing the forwarded records.

The way my current script works is as follows:

Fill a table (#Forwarded_Records) with information from
sys.dm_db_index_physical_stats, sys.indexes and sys.columns. This will fill in the table with tables which contain forwarded records and information about their indexes including offline/online availability.
Once the table is filled, the stored procedure will check if there is still time in the window to do other jobs. If there is it will start with the first record from the forwarded_records table and start creating/dropping an clustered index.
Once all the records have been fixed, it will start to refill the forwarded_records table  and search for new tables containing forwarded records. This will start the cycle again.

I'm facing the following problems:

Healthcare only gives small maintenance windows per day and the database must be performing good/always available 24/7
The filling of the forwarded_records table can take up to 45 minutes(way too long). This is also the case if there are no forwarded records to be found.
The databases can consist out of 4000+ tables. Some having over millions rows. I prefer first looking for forwarded records instead of applying the job to all tables(including ones which don't contain any forwarded records)

Now what is my question?

How can I improve this process so it will be more effective. Currently it is very effective in its first cycle and fixes all the forwarded records. But starting it's second cycle it will just spend more time on "finding" forwarded records than fixing them(it takes 45 minutes to find them, and only 2 minutes to fix them).

Are there any better methods for my case? Should I take the 45 minutes per day to find them for granted or am I better off doing all tables instead of searching for tables which actually need fixing.

Thank you for your help.

(First post on StackExchange, tried to be as constructive as possible. Please help if this question needs editing)


texts: Different calculations in EXCEL and TERADATA SQL

texts: The calculation select (75/979.86)*979.86 in Teradata SqL gives 78.3888. In excel, it would give 75 simply and that is logically correct. I get it that SQL is rounding off 75/979.86 to 0.08 ie the number of decimal digits in 979.86. And hence the result is different.So, I can simply do (75*979.86)/979.86 instead and get the result.But is there any way to match these exactly maybe by changing data type or rounding off?


texts: which time period covers date string over datetime column

texts:
My question is a restlessness I been having for a time. So, here it goes:

If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of time does the query covers?. From this, I can figure some others like timezone related or daylights changes.

So, have you any knowledge or source to check this information?

I know this is not the kind of filter to apply in a datetime column but due a human mistake in a query once I still have doubts

Thank you


texts: AOAG Database went into NOT SYNC\SUSPECT MODE

texts:
I have SQL Server AOAG 2014 with 3 replicas. 2 Syncronous Commit with Automatic Failover and 1 (BCP) Asynchronos with Manual Failover.

It is a DEV environment and I scheduled log backups every 15 mins.

BCP Replica AOAG Database went into "NOT SNCHRONIZING\SUSPECT" mode.

When I checked the logs it says LOG DRIVE is 100 GB and it is completely occupied with this specific Database Log.

When I checked SYS.DATABASES --> Log_reuse_wait_desc -- I have seen "AVAILABILITY REPLICA".

I tried to backup log of this particular database but still I cannot shrink this particular database.

Finally , I have to re-initialize the database by removing it from AOAG and then by re-adding. then backup log for this particular DB and shrink it . This fixed the issue.

What else can I do for this particular situation.


texts:
I have an 2008R2 SQL Server.
There are a few AD groups added to the logins.
Each group must have some permissions (and only these permissions if possible).
Can someone help me with a few questions:

Can I restrict permissions for one of the groups only to view users and their permissions? And if we have only AD groups in Login section - executing stored procedures like xp_logininfo 'domain\group' , members is the only one way to show users in this group?
All users are getting the "public" role by default. I've excluded all permissions from the permission section in the role properties, but users with this role are still able to see system databases. Can I deny this?


texts: Trying to drop database only if it does not exist by using the db_id function fails

texts:
When the database foo is not yet created and I I run this

IF db_id(N'foo') IS NOT NULL
BEGIN
  ALTER DATABASE [foo] SET OFFLINE
  GO

  DROP DATABASE [foo]
  GO
END

I get a message

  Cannot drop the database 'foo' becuase it does not exist or you do not
  have permission.

Why?

I have confirmed that the sql statement select db_id(N'foo') returns NULL.


texts: I need help migrating data through databases

texts: I have a web page with some data about the users. Recently, I made a complete change and start developing a new portal and of course a new DB structure. My question : What is the best way to migrate my data from my old DB to my new DB ? My old DB, and my new DB have similar but different structures, both are in SQL SERVER 2008 R2. Thanks.


texts: Multicolumn statistics on large table yielding poor estimations

texts:
I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the optimizer to make good estimates - in other words, is it no longer "selective" enough? With this particular customer's database/table, my query plan estimations are way off compared to others.

The particular statistic that I am concerned with comes from the table's PK/CLUSTERED INDEX. It is a multi-column statistic containing an int (ParentId) and a smalldatetime (TimeStamp).

When I issue a DBCC SHOW_STATISTICS('SomeTable', 'PK_SomeTable'), I get the following output (histogram omitted - but I can post it if it will help):

Name                    Updated              Rows       Rows Sampled    Steps   Density         Average key length  String Index    Filter Expression   Unfiltered Rows
PK_SomeTable            Jan 31 2014 10:59AM  181170887  181170887       200     2.022617E-05    8                   NO              NULL                181170887

All density     Average Length  Columns
0.0004892368    4               ParentId
5.519651E-09    8               ParentId, TimeStamp

Most of my queries are performed using the combination of both these columns (ParentId and TimeStamp). The small all density value shows the selectivity of this pair - obviously since it's the PK.

(1) The histogram appears to only show the ParentId column. Am I missing something here? Are both columns being accounted for?

(2) If I take 200,000,000 rows / 200 steps, I essentially have 1,000,000 rows defined in each histogram step. This seems large enough that it could cause estimation issues, right? What about sort spills to tempdb and things like that?

(3) Would manually created statistics/filtered statistics be an avenue to explore? How does one go about deciding what type of filter to apply?


texts:
On Microsoft SQL Server 2012, I would like to materialise the result of the row_number function, because I hope it makes queries running faster. I considered to make a view index, or to add a persisted computed column to the relevant table. I fail with both. How can I achieve what I want?

As example, let us look at an address table with multiple addresses per person.

View index

create view nrview with schemabinding as
select
  row_number() over (
    partition by person_id
    order by valid_from) nr,
  address_id,
  person_id,
  valid_from
from address

create unique clustered index ix
on nrview (
  address_id,
  nr,
  valid_from,
  person_id)

This fails with error message 10143:

  Cannot create index on view "mydatabase.dbo.nrview" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view.

Computed column

alter table address
add nr as
  row_number() over (
    partition by person_id
    order by valid_from)
  persisted

This fails with the error message 4108:

  Windowed functions can only appear in the SELECT or ORDER BY clauses.


texts: Speed of one big insert vs multiple small inserts on SQL Server

texts:
I have a write-heavy application in which my initial testing has shown that one large insert into a table with four "large" columns is significantly faster than four inserts (in a single transaction) into four separate tables, each having one "large" column.  For example,

CREATE TABLE BigTable (
  id int,
  col1 varbinary(8000),
  col2 varbinary(8000),
  col3 varbinary(8000),
  col4 varbinary(8000)
)

.

CREATE TABLE LittleTable1 (id int, col varbinary(8000))
CREATE TABLE LittleTable2 (id int, col varbinary(8000))
CREATE TABLE LittleTable3 (id int, col varbinary(8000))
CREATE TABLE LittleTable4 (id int, col varbinary(8000))

where the id field in each of the above tables is auto-increment and a clustered index and the tables are modified only by INSERTs.  I am seeing something on the order of 3x slower insert speeds for the four tables despite the fact that the amount of data written in both cases is nearly identical.  I am not surprised that four separate inserts are slower than one large insert, but 3x slower?  Is this a fundamental characteristic of SQL Server or should I be looking for some kind of flaw in either my table structure or testing methodology?

EDIT: Adding more detail about my testing methodology.  The inserts for both sets of tables were contained in stored procedures.  For the one big table, the procedure was a fairly trivial:

CREATE PROCEDURE InsertBig (
  @val1 varbinary(8000), @val2 varbinary(8000), @val3 varbinary(8000), @val4 varbinary(8000)
) AS
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO BigTable (col1, col2, col3, col4) VALUES (@val1, @val2, @val3, @val4);
    COMMIT TRANSACTION;
END;

For the smaller tables, the procedure was not much more complicated:

CREATE PROCEDURE InsertMultipleSmall (
  @val1 varbinary(8000), @val2 varbinary(8000), @val3 varbinary(8000), @val4 varbinary(8000)
) AS
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO LittleTable1 (col) VALUES (@val1);
    INSERT INTO LittleTable2 (col) VALUES (@val2);
    INSERT INTO LittleTable3 (col) VALUES (@val3);
    INSERT INTO LittleTable4 (col) VALUES (@val4);
    COMMIT TRANSACTION;
END;

I did not collected wait statistics or track log autogrow events.  I did perform the test with 10k-50k iterations 5-10 times and saw a very consistent performance differential between the two each time.


texts: How can I force MySQL to IGNORE ALL indexes?

texts:
I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes?
I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful.
As for why I (and others) need to do this: For example, I needed to summarize referers statistics by tld like this:
SELECT
    count(*) as c,
    SUBSTRING
    (
        domain_name,
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import`
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

...but I always have to look at what indexes are defined or determine what index will be used via Explain. It would be very handy to simply write IGNORE INDEX ALL and simply not care.
Does anyone know the syntax or a hack? (Tens of lines via MySQL definition tables are really not a shortcut).
Added from chat discussion:
Bechmark:

no index = 148.5 secs

with index = 180 secs and still running with Sending data
The SSD array is so powerful, that you almost no care about data cache...

Definition for benchmark:
CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB, the test with index (no USE INDEX() or similar) is still running, 250 secs, I just killed it.


texts: What is the relationship between ADO.NET and Npgsql?

texts:
There is a sentence in the Npgsql official page:

  Npgsql is an open source ADO.NET Data Provider for PostgreSQL.

I know that I can access a Postgresql database using Npgsql with the .NET framework.

Is Npgsql a subset of ADO.NET or is it derived from ADO.NET?


texts:
My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables.

I have a script that will run on end users' computers as the user. (This is why I chose a stored procedure in the first place. Is there a better suggestion?). I need to insert data about the user's computer into the Computer table and data about the attached monitors into the Monitor table. What I would like to do is map the monitors to the computers in the mapping table. Is it possible to do all this in a stored procedure?

p.s I'm new to working with databases and I may be going about this all wrong.

EDIT:
With the help of @Michael Green i was able to come up with the script below. However, I still can't get it working. Currently I'm reading up on how variables are set and used in SQL so I'm slowly getting it. Also, I'm using a Powershell script to do the inserting which I'm pretty sure I have on lock.

Because my database schema is so simple I'll just post it here.
Note: All timestamps are set in the default values.

-- My Table Parameter Value (TVP)
CREATE TYPE ComputerTableType AS TABLE
(Name nvarchar(15),
IPAddress nvarchar(15))

-- My Stored Procedure (SP)
CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@TVPComputer ComputerTableType READONLY

AS
DECLARE @TVPComputer TABLE (Computers int)
INSERT INTO dbo.Computers (Name, IPAddress)
OUTPUT INSERTED.Id
INTO @TVPComputer
VALUES (@TVPComputer);
INSERT INTO dbo.Monitors(Serial)
OUTPUT INSERTED.Id
INTO @Serial
VALUES (@Serial);

EDIT #2:
Still learning...
So I've come to the conclusion i don't actually need a Table Parameter Value(TPM) for my Computers table. I only have two fields which isn't a big deal In my opinion. So what I've done is add each parameter to the top of my Stored Procedure and continue with following @Michael Green's suggestion. I can't seem to get the Table Variable working correctly. Below is my updated code. Still not working! I keep getting a "Must declare the scalar variable". I found some other topics on this error on Stack Exchange but nothing that resolves my issue. It looks like a scope issue or something?

CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@Name nvarchar(15),
@IPAddress nvarchar(15)

AS
BEGIN

    DECLARE @MonitorId TABLE (Id int);
    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(Id)
    VALUES (@Serial)

    DECLARE @ComputerId TABLE (Id int);
    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(Id)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
    VALUES (@MonitorId, @ComputerId)
END

EDIT #3 - Working!
I can't thank you enough @Michael Green for your help. Your answer took me on quite a learning journey which ultimately brought me to a resolution. Below is my working code!

CREATE PROCEDURE InsertUpdateSerialInventory --Fixed Name. That link was a good read thank you!
@Name nvarchar(15),
@IPAddress nvarchar(15),
@Serial MonitorTableType READONLY

AS
BEGIN

    DECLARE @MonitorId TABLE (MId int);

    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(MId)
    SELECT Serial
    FROM @Serial;

    DECLARE @ComputerId TABLE (CId int);

    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(CId)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
    SELECT m.MId, c.CId
    FROM @MonitorId AS m CROSS JOIN @ComputerId AS c;
END


texts: Copy contents of network drive to local folder

texts:
I have an automated third party backup places files on a network drive.

I've mapped this drive to a local folder on the DB server using the following PowerShell script

$pass ="xxxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('MERIVALEHOSTED\SVC.APPSQL',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\10.0.30.113\sqlbackup\ACCCluster01$AccWarehouseAG';
Get-PsDrive;

This maps my network drive to the K drive.

I then want to copy the entire folder structure of this (it's the output of Ola Hallengrens backup - DIFF/FULL/LOGS) to a local drive, but not before deleting the current contents of this folder.

This is the first step of a automated DB restore. I've been able to automate the restoration process successfully using the DbaTools powershell module, but the file copy isn't working.

I've tried both:

Copy-Item -path K:\*.* -destination M:\VoxtronRestore\;

as well as this:

function Copy-Directories
{
    param (
        [parameter(Mandatory = $true)] [string] $source,
        [parameter(Mandatory = $true)] [string] $destination
    )

    try
    {
        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { $_.psIsContainer } |
            ForEach-Object { $_.FullName -replace [regex]::Escape($source), $destination } |
            ForEach-Object { $null = New-Item -ItemType Container -Path $_ }

        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { -not $_.psIsContainer } |
            Copy-Item -Force -Destination { $_.FullName -replace [regex]::Escape($source), $destination }
    }

    catch
    {
        Write-Host "$_"
    }
}

$source = "K:\"
$dest = "M:\VoxtronRestore"

Copy-Directories $source $dest

The first of which fails to copy anything, and the second gives me an error about overwriting the source with itself.

I am wondering if anyone has any better suggestions or modifications to the above which will enable this to complete successfully.


texts: Why does "DELETE WHERE (VARCHAR)=0" execute successfully?

texts:
DELETE FROM stats WHERE name=0

"name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were actually equal to "0".

Why did this succeed in such cases as: "Mike"=0

Thankfully this was done on a personal development server and not production! I surely want to avoid this happening in the future.