texts: Add column for subsequent values for a record

texts:
I have a table structured as follows:

CREATE TABLE t (
  HHID      varchar2(1),
  INDIV_ID  int,
  PRIM      varchar2(1)
);

insert into t values ('A',       10,       'Y');
insert into t values ('A',       11,       'N');
insert into t values ('A',       12,       'N');
insert into t values ('A',       13,       'N');
insert into t values ('B',       14,       'N');
insert into t values ('B',       15,       'Y');
insert into t values ('C',       16,       'Y');
insert into t values ('C',       17,       'N');

What I need is to convert this to the following:

HHID    PRIMARY   SECOND  THIRD  FOURTH FIFTH ....
 A       10        11      12     13
 B       15        14
 C       16        17

Big issue is that the number of possible added columns is unknown, I just need the primary INDIV_ID to show up as the primary and any additionals to be capture in subsequent columns.

Every answer I've looked at right now only suggests answers for the case for a simple pivot of a row id to the column ID however, I haven't seen a pivot that manages to place subsequent records of the same HHID as an additional column.


texts: Unable to Connect LocalDB Server in Navicat Premium

texts:
I've installed LocalDb server on my PC.

I am trying to create a connection to the LocalDB server in Navicat Premium.

I am unable to get the correct Host Name/IP Address for the LocalDB server.

I noticed by default LocalDB server always creates an instance named MSSQLLocalDB when started.   I've searched the internet but couldn't find the exact solution for my problem in Navicat premium software.

Here is the error I am getting:

I've installed only LocalDB server. Is it not a SQL Server native client? I don't want to install both LocalDB server and MS SQL Server Express Edition as I have no need of SQL Server Express Edition.

Installing SQL Server Native Client says:

  Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2016. There is no SQL Server 2016 Native Client.

I am using the recent SQL Server 2016 LocalDB. I believe the download links are for SQL Server 2012. I am using SQL Server 2016, which does not need a native client.

After trying out some more I was able to land on a new error as shown in the second image file below. I am sure that the instance name is correct as you can see in the image:


texts: how to find backup history of SSAS database

texts:
Shockingly on Internet, I am unable to find backup history of SSAS database.

Is there no way to do it?

Also should we take multiple backups of SSAS database? I am not much aware of SSAS database.

Thanks in advance.


texts: How do I migrate data from a SQL Server DB to MongoDB?

texts: I wish to migrate a large SQL Server Database (having a million tables) to MongoDB. I am not able to understand how to convert the complex Joins, indexes and keys in SQL Server to MongoDB which has over a million tables and huge data?


texts:
I'm trying to understand the various PostgreSQL replication technologies. But still I am unable to understand difference between Hot Standby and Streaming Replication.

In this Blog the Hot Standby and Streaming replication is same.  Can anyone help me understand this?


texts:
I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say that the record is no longer current.
I am planning to implement this using a Trigger (which I am assuming is the best option).

In the above case, with a flag to say that a record is not the current one, what's the best way to ensure that reads from the table always used the latest records? Only checking the flag value with where condition?


texts: What privileges do I need in order to grant execute on dbms_rls?

texts:
I have an automated build script that creates a new user and builds up a schema. I also want to grant execute on dbms_rls to myuser as part of this script, but I am getting this error:

Error starting at line : 3 in command -
grant execute on sys.dbms_rls to chemreg
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

I can successfully execute grant execute on dbms_rls to myuser if I am running as SYS, but my build script will be running as another user.  Which privileges does that user require in order to grant execute on dbms_rls?


texts: What is DBCC CHECKIDENT current column value

texts:
DBCC CHECKIDENT output includes a "current identity value" and a "current column value". I can see that "current identity value" is the identity that will be assigned to the next row. But what is the "current column value" referring to?

My routine works like this (pseudo):

dbcc checkident ('TableA')
set identity_insert on
insert TableA select TableB where status = 'X'
delete TableB where status 'X'
set identity_insert off
dbcc checkident ('TableA')

The current identity value of both DBCC statements is the same. The current column value seems to be the highest ID recovered. But what does it mean?


texts: MySQL export user with semi-colon ";" on the end

texts:
I am trying to set up automatic export of user accounts from one server to import into another. I am running the following command;

$ mysql -u root -p -B -s -e "show grants for 'myuser'@'localhost';" > user_export.sql

$ cat user_export.sql

GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*IHIHIHOOEHOEHOH'
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuser'@'localhost'

The problem here is that these SQL statements don't end with ; so I can't import it on another remote server using:

$ mysql -u root -p -D mydb < user_export.sql

I know I could write bash one-liner to wrap this and add a semi-colon but I am hoping there is  away to get these statements with the semi-colon on the end to keep this nice and simple, and clear.


texts:
I'm using the Informix DBEXPORT to dump my database however it uses | as its default delimiter.

I know I can specify the delimiter I'd like to use using the DBDELIMITER environment variable but how do I set it to use tab

I tried using \t but that didn't seem to work

(It's running on a CentOS 5 Server btw)

UPDATE

checking through the documentation it says that you can specify tab using CTRL-I but when I set it to CTRL-I it comes out in the unl file as ^@


texts: Convert output of a query to JSON format

texts:
Given the following:

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b72da5e9263d55490fa0809c52577083

I need to have the secondname in the results too, and the output should be in JSON format.

parent -> children, then each child again its children, etc.

{ "Main Product" :
    { "title" : "MP",
      "children" :
      [
        { "Addon Product" :
          { "title" : "AP",
            "children" :
            [
              { "subAddon Product" :
                { "title" : "SAP" } },

          ...

of course any better idea for output is welcome. SInce i see export is JSON is too difficult, perhaps export to a more easy format ie csv, xml, so that i could then generate the json.


texts:
I have a database that has both sequenced correlated to table unique ids and "standalone" but having values.

I need to know which ones are really used but I'm not the owner of every procedure running on the database.

Is there any way to know when a Postgres sequence was last used?


texts: Index Maintenance - Reorganize After BIG SQL Server Shrink

texts:
I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = 50,
@FragmentationLevel2  = 80,
@UpdateStatistics = 'ALL'
,@FillFactor = 100

So basically I know that shrinking is a very bad idea and not a common practice,
 I'm Shrinking because is really needed and it has been working good.

So due to the fragmentation that the shrinking is causing, I'm planning to use the mentioned stored procedure to Reorganize all the affected indexes and avoid getting the freed space back with a Rebuild

So the specific question would be, does this look like a good approach or have you faced something similar and used another approach for it ?, or if you have suggestions are gonna be highly appreciated.


texts: How many records should be deleted when batch delete is used?

texts:
I am going to migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause, but need to have a buffer table instead.

The data source table is not going to be read by other users, but the new table (in which I am inserting records will be frequently used during the operation) - so, I need to do the migration without blocking the other users activity.

The database is under FULL recovery model, SQL Server 2012 and transaction log backup is made on every 15 minutes (the transaction log growth during the operation is not an issue anyway).

I have the following T-SQL statement:

CREATE TABLE #DataBuffer
(
    ....
);

DECLARE @RowCount INT;

WHILE 1 = 1
BEGIN

    BEGIN TRY

          BEGIN TRANSACTION

            TRUNCATE TABLE #DataBuffer;

            DELETE TOP (4000)
            FROM [dbo].[OldSourceTable]
            OUTPUT [deleted]. ...
            INTO #DataBuffer (...)
            WHERE ... ;

            INSERT INTO [dbo].[NewSourceTable] (...)
            SELECT ...
            FROM #DataBuffer;

            SET @RowCount = @@ROWCOUNT;

            COMMIT TRANSACTION

            IF @RowCount = 0
            BEGIN
                BREAK
            END

    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION
        END

    END CATCH

END

and I am wondering which is the best value of rows to be deleted in a batch delete statement?

I have seen different variants and the only thing I have found as recommendation was from here:

  If you delete 4000 or more rows in a single command, the whole table
  will be locked (or the whole partition if you enabled that on
  partitioned table). DO NOT delete 10000 rows in a batch if the table
  needs to be available for other users! So, delete e.g. 2000 rows in a single delete...

But I was not able to find a confirmation in the MSDN or elsewhere about this.


texts: Slow SQL backup to local disk problem, with odd workaround

texts:
I have some SQL Server 2017 (14.0.3037) servers, both Standard Edition and Developer Edition, running on Windows Server 2016 Standard in a VMware environment (v6.5).  The disks are on fast SAN storage

Disk config:

C: OS, incl. swapfile
  D: SQL program files, system db and SQL Backup
  E: DB Data
  F: DB Tlog
  G: TempDB

The problem is that we see very slow SQL backups.  On a 50 GB database that normally took 2 minutes to backup, now it takes 77 minutes.

When there is enough free space on the disk for a compressed backup, and the backup can complete.   But if there is too little free space the backup is very slow.

But I found a strange workaround...
During the backup, if I find that it is running slow, (by measuring MB/sec on the data disk), if I then delete a big file, the backup speeds up immediately, the performance (MB/sec on the data disk) goes up, and the backup is fast.

So now I have expanded the disk so it has more free space, and the backup is fast.

I see this as a workaround, not a solution, so I would like to hear if anyone has seen this problem?  If anyone has more info about why the SQL backup runs very slow if SQL Server thinks that there is not enough free space on the disk...

Is this a new SQL 2017 feature?

Edit:
This is also happens on SQL 2016, and probably from 2008 and up...

Microsoft has released a info about how much a compressed SQL Backup pre-allocate when backup starts, it is 1/3 of the reserved data in a database:
https://support.microsoft.com/en-gb/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server


texts: Creating an entity-relationship diagram for a recipes business domain

texts:
I am doing this simple ASP project for my uni, just wondering if you can guys direct me or give me some advice.

The project Idea is to create a recipe website which will allow users to add recipes and then admin will approve it.

Recipes will be divided in to few categories: Food Region: 'East', 'South', etc., and Categories:'Beef', 'Noodles', etc.

Can you guys check if I am on the right direction?
Is my entity-relationship diagram (ERD) below correct?


texts: How to give an existing server login access to an attached/migrated database

texts:
I'm migrating databases from SQL Server 2012 to 2014. I detached the databases from source server, then copied the files (.mdf, .ldf, etc.) to the new server and attached them. That process went fine, now the databases are available on SQL Server 2014.

The problem now is that when I go to Security -> Logins on the new server and check "User Mappings" for LoginX all the new attached databases are checked and apparently LoginX should have access to them.  But when trying to access those databases using that login they are not available for that user.  I have tried several things:

I tried to remove those mappings to try to recreate them but it fails saying that "Cannot drop the user dbo.. (Error 15150)".
Also checked Security -> Users under the new attached databases and there is no user associated with LoginX.  Well I tried creating a user for that login on one of the databases and that also fails saying that "The login already has an account under a different user name. Error 15063.
I have also tried sp_change_users_login 'Auto_Fix' 'loginX' but that did not work and actually was expected to do nothing because loginX is not an orphan... but I'm running out of ideas.
Deleted LoginX from server and then recreated it and then the mappings just reappeared (I did not put them there).  I have no clue why/how those mappings are there.
According sp_change_users_login 'report' there are no duplicate logins or orphan logins/users.

The question is, how can I fix that? This scenario is kind of the opposite of an orphan where I have a login and it is supposed to have access to some databases but the databases do not know about the login and I don't know how to recreate that link or destroy it and make it right.

All users/logins are Windows/Active Directory users so SIDs are (should be?) the same for both servers.

The login was not created using same SID.  We are migrating dozens of databases, so the migration was scripted using the detach/attach method.

All our users/logins are Windows AD logins. I'm going to check that maybe I have mixed SQL and Windows types.

I tried ALTER USER [user name] WITH LOGIN = [Windows Account]; but it fails saying "Cannot alter the user because it does not exists or you do not have permission" (Error 15151). I can see the user listed on server logins and I'm using a sysadmin account.


texts:
I am a dba by accident. I am doing index  rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this topic. I have a sql server 2008R2 instance which has around 25 databases of different size and needs. I have been assigned to do index tuning for 8 databases. Since the size and the fragmentation level and the no of indexes and also the rate at which de-fragmentation happens differs from database to database, I understand that some databases need an index maintenance on a monthly basis and few others on a weekly basis. I have two questions:

How frequently should I schedule for the rebuild/reorganize process?
For eg: Last month I rebuilt indexes for fragment level above 30% and reorganized and updated stats for 5-30% fragment level. Then I checked it after a week, i found one or two fragmented indexes, I checked it after a month and I found roughly around 10 fragmented indexes..is it the right time to schedule for an index maintenance again?

If I develop a common script for the instance, how should I make it work for all the databases on the instance, because not every database would need an index maintenance at the same time.


texts: Find accounts with passwords that are not complex

texts:
I can see all the SQL Login passwords that enforce the password policy with this command

SELECT * FROM sys.sql_logins WHERE is_policy_checked = 1

However, it seems that when the enforce password policy is checked, it requires the next password change to enforce the policy and doesn't mean that current password does:

This MS article states

  Password complexity policies are designed to deter brute force attacks
  by increasing the number of possible passwords. When password
  complexity policy is enforced, new passwords must meet the following
  guidelines:

I can verify the above as follows

Create login with weak password:

CREATE LOGIN [TestLogin] WITH PASSWORD=N'weak', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Enforce the policy

ALTER LOGIN [TestLogin] WITH CHECK_POLICY=ON

at this point, the following query returns 1

SELECT is_policy_checked FROM sys.sql_logins WHERE name = 'TestLogin'

So is there anyway I can check which SQL accounts have a weak password? My guess is "no" as I would have thought this was a bit of a security vulnerability and the only way would be to change all the passwords when the policy has been set to check.


texts:
I have to implement some safety authentication rules on postgresql DB  with a windows server. For example, rules may be "users not used for a period longer than three months will be locked" or "postgres enforces account lockout after a set number of failed login attempts."

Using authentication "standard" ( md5 on pg_hba.conf) I think is not possible.  I think that with PAM it would work. However,  PAM does not exist on windows server. Any ideas?


texts:
I have a chats collection which given down below

{
    "_id" : ObjectId("5a44e6545818041cde24aac9"),
    "messages" : [
        {
            "createdAt" : ISODate("2017-12-23T07:46:29.201Z"),
            "message" : "Hii",
            "userId" : "000000"
        },
        {
            "createdAt" : ISODate("2017-12-23T12:46:29.201Z"),
            "message" : "Bye gtg ttyl",
            "userId" : "111111"
        },
        {
            "createdAt" : ISODate("2017-12-24T07:46:29.201Z"),
            "message" : "Bye Take Care",
            "userId" : "000000"
        }
    ],
    "createdAt" : ISODate("2017-10-23T07:46:29.201Z"),
    "users" : [
        {
            "facebookId" : "0000000",
            "unread" : 0
        },
        {
            "facebookId" : "111111",
            "unread" : 0
        }
    ]
}

When I do this following query in nodejs

chats.aggregate( [ { $unwind : "$messages" },{ $unwind : "$users" } ])

I get an array which is something like this in which unwind opens my array something like this

[
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "0000000",
            "unread": 0
        }
    },
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        },
    }, //And so on.
]

But can I only get the objects after unwind where messages.userId !== users.facebookId

something like the following object directly from MongoDB when i fetch data

[
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        },
    },{
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T12:46:29.201Z",
            "message": "Bye gtg ttyl",
            "userId": "111111"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "0000000",
            "unread": 0
        }
    }, {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-24T07:46:29.201Z",
            "message": "Bye Take Care",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        }
    }
]

I tried this but the output is same :/

chats.aggregate( [ { $unwind : "$messages" },{ $unwind : "$users" },{$match:{ 'messages.userId' :{ $ne: '$users.facebookId'}}}]).exec(function(err, data) { res.send(data); });


texts:
I am looking to turn page compression on on a spatial index. The instance is 2008 Enterprise. The index is pre-existing on a vendor supplied app. In searching through BOL, it states that compression and fillfactor are both allowed.

ALTER INDEX [ix_city_citypoint] on city
REBUILD WITH (FILLFACTOR = 90, DATA_COMPRESSION = PAGE)

  Msg 153, Level 15, State 4, Line 12
  Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.

original create:

CREATE SPATIAL INDEX [ix_city_citypoint] ON [dbo].[city]
(
    [citypoint]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
GO

I can always ignore the index as it's small but now it's morphed into one of those "teachable" moments. Thank you for any insight


texts:
The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns

id
auth
old_auth
device_id_fk

This error is showing

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The results of SHOW CREATE TABLE

CREATE TABLE `wtb_device_auths` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `device_id_fk` int(11) NOT NULL,

  `auth` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `old_auth` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `auth_index` (`auth`),

  KEY `auth1_index` (`device_id_fk`),

  KEY `auth2_index` (`old_auth`)

) ENGINE=InnoDB AUTO_INCREMENT=627642 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

How to fix it?


texts:
I would like to export an SSRS report to a CSV file. Sounds simple, but I do not know how to add double quotes to the column headers in the exported CSV file. For example:

"UserName"|"UserAdd"
"Mike"|"Some Address"


texts:
I've reinstalled MySQL but when I copy the data dir from the old instance:
maicol07@maicol07$ sudo mysqld --user=root
2020-07-14T13:07:34.031447Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) starting as process 90378
2020-07-14T13:07:34.446932Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-14T13:07:37.429706Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2020-07-14T13:07:37.430405Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 3 = ['mysql/gtid_slave_pos.ibd', 'mysql/servers.ibd']

2020-07-14T13:07:37.431128Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2020-07-14T13:07:37.431713Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
mysqld: Error on delete of 'mysql_dd_upgrade_info' (OS errno 2 - No such file or directory)
2020-07-14T13:07:37.433160Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-07-14T13:07:37.434617Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-14T13:07:37.438495Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).

I think MySQL started upgrading from 5.7 to 8, but I'm not sure...
Can you help me?
Thanks


texts: Microsoft Visual Studio SSIS Package importing Date Format

texts:
I am importing data from text to an OLE DB Destination. Here My date from the text file is in a format MMDDYYYY with no hyphens or slashes etc.  Upon trying the import to the OLE DB Destination via data conversion where I select DT_DATE, I am getting the following error.  [Flat File Source [56]] Error: Data conversion failed. The data conversion for column "DATE_OF_SERVICE" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Not sure If there is another step I could use to translate the data to a more understandable format for the Package to work with.


texts: Index on primary key not used in simple join

texts:
I have the following table and index definitions:

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

Why are none of the indexes on munkalap_id used in the following query?

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

It's the same even if I add a filter:

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms


texts: Binlog commits and flushes on SQL Server (compared to MySQL)

texts:
With MySQL secondary/primary, sync_binlog and innodb_flush_log_at_trx_commit control log and disk flushing, and are important to maintain consistency between primary and secondary (so that the secondary can never be ahead of the primary in case of a crash). An admin is free to set this (and mess this up), for performance reasons for instance. I was wondering, how is this arranged in Microsoft SQL Server in mirroring or replication mode? Does it even have the same tunables?
Specific questions would be:

Being more 'click-and-go', does SQL server set everything to safe values?
What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?


texts: How to INSERT a row INTO a table that has an 'order' column without cursor

texts:
I have a SQL Server 2016 table with an order/position column.  This column is an INT that indicates the preferred order/position of the rows in table for presentation purposes.

How do I INSERT a row in position 1 without using a cursor?  (i.e. new row has position 1, existing rows - assumed to be in correct order before insert - must be "moved down")

The order/position column is NOT NULL and enforced to be UNIQUE. I would also prefer not to use "blunt force" technique like set all rows' order/position value to some "never used" values and then just perform reorder on all rows.


texts: Update with CTE & OUTPUT blocks on concurrent calls

texts:
I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks.

But now each client that I add slows down (blocks?) the stored procedure. E.g. a 1-2 second update with 1 client becomes 2-4 seconds for two clients. (This simple metaphor query runs at 0.001 seconds, but gets up to like 0.03 after a handful of clients -- so the issue is independent of my real-world implementation.)

Is it a locking issue? Do I need to wrap it in a (some kind of) transaction?

WITH UpdateView AS (
    SELECT TOP 1 W.*
    FROM [WidgetSandbox].[dbo].[Widgets] W
    INNER JOIN [WidgetSandbox].[dbo].[Sizes] S ON W.SizeId = S.Id
    WHERE W.StatusId = @availableStatusId
    AND W.ColorCode = @colorCode
    ORDER BY S.DiameterInches
)

UPDATE UpdateView
SET StatusId = @soldOutStatusId
OUTPUT INSERTED.Id INTO @outputIds;

SET @singleUpdatedId = (SELECT TOP 1 Id FROM @outputIds);

SELECT * FROM [WidgetSandbox].[dbo].[Widgets]
WHERE Id = @singleUpdatedId;

I wish I had a better idea of what the specific problem is, but as it is I'm just stuck....

DDL, if it helps: https://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

Repo: https://github.com/RobertBaldini/WidgetSandbox


texts: SQL Server MDW - Query for data collector retention period

texts:
I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance.

These are system data collectors, part of Management Data Warehouse, not custom collectors.

Any ideas which tables will hold this data?


texts: Rotate table for year-over-year comparison

texts:
I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_year int,
    condition number(10,2)
);

insert into road_insp (insp_id, road_id, insp_year, condition) values ( 1, 100, 2009, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 2, 100, 2011, 3);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 3, 102, 2009, 9);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 4, 102, 2010, 7);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 5, 102, 2013, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 6, 103, 2009, 10);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 7, 103, 2011, 8);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 8, 103, 2012, 6);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 9, 103, 2013, 4);

commit;

select
    insp_id,
    road_id,
    insp_year,
    condition
from
    road_insp;

   INSP_ID    ROAD_ID  INSP_YEAR  CONDITION
---------- ---------- ---------- ----------
         1        100       2009          5
         2        100       2011          3
         3        102       2009          9
         4        102       2010          7
         5        102       2013          5
         6        103       2009         10
         7        103       2011          8
         8        103       2012          6
         9        103       2013          4

I want to compare the condition of roads, year-over-year.

I can do this by joining to sub-selects for each year:

select
    a.road_id,
    y09.condition condition_2009,
    y10.condition condition_2010,
    y11.condition condition_2011,
    y12.condition condition_2012,
    y13.condition condition_2013
from
    road_insp a
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2009
    ) y09
    on a.road_id = y09.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2010
    ) y10
    on a.road_id = y10.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2011
    ) y11
    on a.road_id = y11.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2012
    ) y12
    on a.road_id = y12.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2013
    ) y13
    on a.road_id = y13.road_id
group by
    a.road_id,
    y09.condition,
    y10.condition,
    y11.condition,
    y12.condition,
    y13.condition
order by
    road_id

   ROAD_ID CONDITION_2009 CONDITION_2010 CONDITION_2011 CONDITION_2012 CONDITION_2013
---------- -------------- -------------- -------------- -------------- --------------
       100              5                             3
       102              9              7                                            5
       103             10                             8              6              4

However, this method is quite cumbersome. Is there a more concise way to rotate a table for year-over-year comparison?


texts: Mysql Shell doesn't change the configuration of my database for group replication

texts:
I am attempting to use a preexisting database on multiple nodes in a group replication setting. I am using mysqlsh 8.0.16, mysql server 5.7.24 and ubuntu 16.04 as the host.

When I attempt to configure the database with mysqlsh, I get the following output

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency         | OFF           | ON             | Update the config file and restart the server    |
| gtid_mode                        | OFF           | ON             | Update the config file and restart the server    |
| log_bin                          | <not set>     | <no value>     | Update the config file                           |
| log_bin                          | OFF           | ON             | Update read-only variable and restart the server |
| log_slave_updates                | OFF           | ON             | Update the config file and restart the server    |
| master_info_repository           | FILE          | TABLE          | Update the config file and restart the server    |
| relay_log_info_repository        | FILE          | TABLE          | Update the config file and restart the server    |
| server_id                        | 0             | <unique ID>    | Update the config file and restart the server    |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update the config file and restart the server    |
+----------------------------------+---------------+----------------+--------------------------------------------------+

and i specify the file used (i tried with multiple cnf files that are in the mysql directory).

However the files do not get edited despite having root privileges, none of the changes seem to take effect when I restart the server (or the instance it is running on).

Perhaps there is an elegant way to do this manually instead?


texts: The mapped logins are removed when database is restored

texts: Whenever I restore a database all my logins associated with that database are removed. How can I avoid this ?


texts: Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

texts:
I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. Since the tables are so large I'd like to process the update in batches, both so I can gauge progress and to avoid creating huge temp tables. Each column is indexed.

Would this be a good example where a CTE comes into play? Quite honestly they confuse me with the way they need to be written, it's hard to visualize...

The tables are structured as:

ServerA (utility SQL server)
Table: CLIP_IDs
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)

ServerB (main database server)
Table: PACKAGE
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
Table: DOC2
Columns: External_Identification nvarchar(255), Package_id bigint (2b rows)

Both SQL servers are linked both ways, if initiating the query from one is more efficient. I have a feeling issuing the query from ServerA will be, as it seems like the execution plan offers less remote queries.

I stopped the query below after 26 hours because I think I have a syntax logic error. Can someone explain what it is and offer any suggestions please?

Executed from ServerA:

DECLARE @rowsUpdated INT

SET @rowsUpdated = 1

WHILE (@rowsUpdated > 0)
BEGIN
    UPDATE CLIP_IDs
    SET [Count] = x.[count]
    FROM (
        SELECT TOP 50000 c.package_uuid
            ,count(d.external_identification) AS [count]
        FROM CLIP_IDs c
        INNER JOIN ServerB.DATABASE.dbo.package p(NOLOCK) ON c.package_uuid = p.package_uuid
        INNER JOIN ServerB.DATABASE.dbo.doc2 d(NOLOCK) ON p.package_id = d.package_id
        WHERE c.messageextractstate = 1
            AND c.[count] IS NULL
        GROUP BY c.package_uuid
        ) x

    SET @rowsUpdated = @@rowcount

    PRINT N'Finished set of rows: ' + convert(VARCHAR, getdate(), 120)
END


texts: Who has deleted MySQL user from management console?

texts:
How can I find who has removed a SQL login from MySQL instance?

Checked event viewer but cannot find anything.


texts:
I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed...

For example, consider the following:

    1. I have 2 databases (db1 and db2) and both databases are geographically far from each other, more like cities away.
    2. db1 has a table namely, tbl_orig
    3. db2 has a table namely, tbl_backup
    4. I want to use MySQL transaction when copying the table from db1 (tbl_orig) into db2 (tbl_backup) so that in case of a power outage or anything that would interrupt the process, it would prevent my database to be in an unstable state.

Say I use the following commands below:

    mysql> start transaction;
    mysql> insert into db2.tbl_backup select * from db1.tbl_orig;
    mysql> commit;

In the above sql statements, in between the "start transaction" and the "commit" lines... what exaclty is happening to the rows indicated in the insert statement?

Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?

Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?

I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.


texts:
I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size:    16 x 512B = 8192B
Page Header:            = 96B
Maximum In_Row Row:     = 8060B

This leaves (8192 - 96 - 8060)B = 36B.

Ok, this is logical and correct. The question I have is this: why do so many people say that the remaining 36B is reserved for the slot array?

Obviously, the slot array gives 2B per row on the page; so, it can be as small as 2B and as large as 1472B:

2B: 1 row * 2B = 2B

1472B:
8096B = n*9B (min row size with overhead...think single TINYINT column) + n*2B (slot array cost per row)
=> 8096 = 11n
=> n = 8096 / 11 = 736.

736*2B = 1472B.

This gets me to 20 due to the 14B version tag.

USE master ;
GO

CREATE DATABASE test ;
GO

USE test ;
GO

ALTER DATABASE test
    SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO

ALTER DATABASE test
    SET READ_COMMITTED_SNAPSHOT ON ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i CHAR(8000) DEFAULT(REPLICATE('a',8000))
    , j CHAR(53)   DEFAULT(REPLICATE('a',53))
) ;

INSERT INTO tbl
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

Another example. If you go to 50 from 49, you get the VARCHAR(MAX) going to LOB_DATA.

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
    , j CHAR(49)   DEFAULT(REPLICATE('a',49))
) ;

sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO

INSERT INTO tbl
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

It appears that this issue remains, even in SQL Server 2012. @SQLKiwi points to this post by Kimberly Tripp - http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices/.


texts:
We recently enabled CDC on four tables in a busy database and since then we are capturing a lot of sessions that are waiting on XE_BUFFERMGR_FREEBUF_EVENT. When we see this wait type CDC is trying to INSERT into the associated CT table, and the wait duration can be up to 10ms.

I've stopped all XE sessions on the instance but I still see the same issue and this could be causing some issues on the wider system

Has anyone else seen this?

We're running SQL Server 2012 SP4.


texts: Does pausing an online resumable rebuild on an otherwise inactive partition affect the entire index that spans across all partitions?

texts:
Using SQL Server Enterprise 2017 edition.
I just had an interesting issue with a partitioned clustered index that i was rebuilding just a partition of.
The table and all but one indexes are on a partition schema that is partitioned on a DATEMODIF (varchar) field in 'YYYYMMDD' format.
we have yearly partitions in place : 17 is for data for 2016, 18 for 2017 etc…
The table has 26 billion rows,
count by year is approx:
2015 :   312,686,074
2016 : 1,990,483,950
2017 : 3,378,498,597
2018 : 5,756,681,366
2019 : 6,301,979,049
I ran
ALTER INDEX [IDX_FK_MyIndex] ON [dbo].[T_PARTITIONED_TABLE] REBUILD PARTITION = 17
WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = ON, MAXDOP = 2)

The VM has 6 CPUs and 320GB RAM, and Nimble SAN storage in the background.
I had the usual workload that can handle 1800 transactions per second drop to 50-100 per second.
(the transactions are all INSERTs into the partitioned table-> into partition 22, for 2021)
I left it running for 50 minutes (it had managed 25% of the clustered index on partition 17 (for 2016)), and the backlog was getting too high so i decided to PAUSE it with ALTER INDEX [IDX_FK_MyIndex] ON [dbo].[T_PARTITIONED_TABLE] PAUSE
Even with this paused, i only got up to 150/200 transactions/second.
After more fretting and sweating, i used the ABORT command to see if this or some ‘hidden’ workload was causing the slowness. And bam, after the abort it jumped right back up to 1800 trans/sec.
Not a request for support ;), but was curious – I thought partitioned indexes were independent, but the pause of an online resumable rebuild on an otherwise inactive partition affects the entire index, spanning across all partitions?
Has anyone seen anything like this, or have an explanation? I couldn’t find anything on MS online docs saying anything about this…


texts: Is it a good idea to use Synonyms to avoid creating a duplicate table?

texts:
We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we have to update 3 databases.

Would it be a better idea to delete the Users table from databases 2 and 3 and replace it with a Synonym that points to database 1?

Here's the Pros/Cons I can think of:

Pros

Easier Maintenance. Can update Users in one location instead of 3
User Ids would match between databases (Important since a lot of add-on apps are based on UserId)

Cons

Don't think this is standard procedure, so might be confusing
Users would have to have identical settings between databases
(From gbn's answer below) If Database 1 ever goes down, Database 2 and 3 will also be unavailable. Also there is the potential problem of the data being inconsistent in the event of a restore

This is an option I'm considering for a few different tables containing Settings that are identical between the databases, not just the Users table. I am using Users in the example since it is easy to understand.


texts: creating an index for varchar column greater than 900 bytes

texts:
I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is 900 bytes.

Is there any way in the world I can add any sort of an index to this column?  For example, create an index on the first 900 bytes?

Thanks


texts:
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table.  In this example I want to join using a timestamp, but I think one would have a similar (though more complex) problem with geographical coordinates.

To simplify a lot, I have one transaction table with a primary key id, timestamp, currency and amount - and one currency rate table with id, timestamp, currency and rate.  Those tables are independent from each other, and they contain a lot of rows; assume that for some periods there are many transactions between each update of the currency rate, while in other periods there are many updates of the currency rates for each transaction.  I want to join them so that there is one row for each transaction, with the closest matching currency rate.  I have indexes on the timestamps.

So, here is a brief example of what I have and what I need:

transaction:
+---------------------+--------+
| timestamp           | amount |
+---------------------+--------+
| 2017-07-31 00:07:05 | 173.17 |
| 2017-08-02 15:29:11 | 136.57 |
| 2017-08-05 03:45:24 |  81.27 |
| 2017-08-05 03:46:47 |   48.1 |
| 2017-08-05 03:47:38 |  35.21 |
+---------------------+--------+

rate:
+---------------------+-------------------+
| timestamp           | rate              |
+---------------------+-------------------+
| 2017-07-31 00:04:49 |            9.2923 |
| 2017-07-31 01:37:59 | 9.313423370522607 |
| 2017-08-01 08:07:59 |             9.325 |
| 2017-08-01 16:52:23 |            9.3542 |
| 2017-08-01 21:07:09 | 9.357076262185192 |
| 2017-08-02 15:07:33 |  9.34936993421895 |
| 2017-08-02 17:48:45 | 9.357217848393876 |
| 2017-08-04 04:33:51 |  9.38690807898736 |
| 2017-08-04 08:13:01 | 9.383765889641701 |
| 2017-08-06 03:45:03 | 9.118193727124817 |
| 2017-08-06 04:15:01 | 9.353042966450854 |
| 2017-08-06 05:23:29 | 9.353042966450854 |
+---------------------+-------------------+

Want something like this:
+---------------------+--------+--------+
| timestamp           | amount | rate   |
+---------------------+--------+--------+
| 2017-07-31 00:07:05 | 173.17 | 9.2923 |
| 2017-08-02 15:29:11 | 136.57 | 9.3493 |
| 2017-08-05 03:45:24 |  81.27 | 9.3869 |
| 2017-08-05 03:46:47 |   48.1 | 9.3869 |
| 2017-08-05 03:47:38 |  35.21 | 9.3869 |
+---------------------+--------+--------+

(The production data is much more dense than this)

Post-edit: I did try to use a subquery, it failed spectacularly but probably because of a mistake from my side.  Subquery seems to be the best way to solve this, so I've moved it from the question side into an answer.


texts:
In SQL Server, when using optional parameters, the only way to have default values is using @param = NULL upon creating.

Is there a way for the stored procedure to know that the application will only update specific columns and the remaining columns will retain the original values?

Here is my example:

Data: col1 = "AA" col2 = "BB" col3 = "CC"

Application update inside stored procedure: UPDATE table SET col3 = "C", col ="B" WHERE id = 1

After update, value of col1 will remain.

I hope my explanation is clear enough :)


texts: Postgres - Join latest row alongwith complete join

texts:
I have 3 tables products, stock_entries and purchase_invoice_items. I am trying to create a materialized view using these 3 tables. Additionally, I want to join only the latest row of purchase_invoice_items as well. Here's the sample of the query:

select *, latest_pii.price, latest_pii.id
from products prd
left outer join stock_entries se on se.product_id = prd.id
left outer join purchase_invoice_items pii on pii.id = se.purchase_invoice_item_id
left outer join (
        select id, price
        from purchase_invoice_items
     ) latest_pii ON latest_pii.id = (select max(sq_pii.id) from purchase_invoice_items sq_pii where sq_pii.id = pii.id)

But this doesn't give correct results. Here's what output looks like:

[
  {id: 1, latest_pii_price: 350, latest_pii_id: 5, pii_price: 350, pii_id: 5},
  {id: 1, latest_pii_price: 350, latest_pii_id: 6, pii_price: 350, pii_id: 6},
  {id: 1, latest_pii_price: 300, latest_pii_id: 9, pii_price: 300, pii_id: 9},
  {id: 2, latest_pii_price: 100, latest_pii_id: 10, pii_price: 100, pii_id: 10},
  {id: 2, latest_pii_price: 150, latest_pii_id: 11, pii_price: 150, pii_id: 11},
]

Instead of this, I was expecting output to be like this:

[
  {id: 1, latest_pii_price: 300, latest_pii_id: 9, pii_price: 350, pii_id: 5},
  {id: 1, latest_pii_price: 300, latest_pii_id: 9, pii_price: 350, pii_id: 6},
  {id: 1, latest_pii_price: 300, latest_pii_id: 9, pii_price: 300, pii_id: 9},
  {id: 2, latest_pii_price: 150, latest_pii_id: 11, pii_price: 100, pii_id: 10},
  {id: 2, latest_pii_price: 150, latest_pii_id: 11, pii_price: 150, pii_id: 11},
]

I am not able to figure out what am i missing. Any help would be appreciated. Thanks


texts: MySQL: grant SELECT to all databases except system

texts:
I need to add an user able to read all my databases.
So I create:

create user 'reader' identified by 'password';
grant select on *.* to 'reader';

The problem with this is that the user is able to read also system tables like mysql.user, which I think is a security problem.
I tried to revoke the select privilege on that table:

revoke SELECT ON mysql.* from 'reader';

But I get

ERROR 1141 (42000): There is no such grant defined for user 'reader';

What am I doing wrong? Is there another way to achieve what I want?
Or manipulating the grant tables is the only way?


texts: Physical or logical backup takes more space when backing up same MySQL database?

texts:
Physical backup is copying the data files that belongs to database. Logical backup is using SQL statements.
So physical or logical backup takes more space when backing up same MySQL database?


texts: Apply limit and conditions per grouped data

texts:
I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold products per day in a period. The period, for instance, could be any date range.

Suppose I have a sales table with the following data (this is not the real schema I have defined):

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-01-08 | C            | 1
2015-02-08 | A            | 5
2015-02-08 | B            | 3
2015-02-08 | C            | 100

And suppose I want the 2 most sold products per day. I want a query that
returns the following:

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-02-08 | C            | 100
2015-02-08 | A            | 5

Note that besides that C is the most sold product in the whole period, it does not appear in the top 2 of 2015-01-08

Is there a way to achieve what I want?

Thanks :)


texts: Oracle: i want to setup some variables for login.sql

texts:
I use this login.sql for my oracle session

select           'You are:          '||upper(user) "Welcome to Oracle on AIX 7.1" from dual
union all select 'Logged into:      '||upper(sys_context('userenv','db_name')) from dual
union all select 'Database Version: '||banner from v$version where rownum <= 2;

Instead of using AIX is possible to get some variable?
So when i connect on linux server it doesn't return AIX
I know variable is &variable,but how to set a variable using
sql command "select from..etc"?


texts:
I have a very large table and I must delete big amount of data from it until some point.

The table has only one non-clustered index on a column which is not relevant to use in my delete query. The first idea that comes to me is to include to the index a unique column which I could use in my delete query.

My question is, do you think it will be a good option or something better can be done?


texts: What commands are available to visualize the data pages on SQL Server?

texts:
From msdn:

  The page is the fundamental unit of
  data storage in SQL Server. An extent
  is a collection of eight physically
  contiguous pages. Extents help
  efficiently manage pages.

  This section describes the data
  structures that are used to manage
  pages and extents. Understanding the
  architecture of pages and extents is
  important for designing and developing
  databases that perform efficiently.

So what commands/utilities are available to visualize the data pages on SQL Server ?


texts: Query with limit and order by runs too slow

texts:
I have the following table:

CREATE TABLE dpg2
(
  account_id integer NOT NULL,
  tank_id integer NOT NULL,
  battles integer,
  dmg integer,
  frags integer,
  wins integer,
  recent_battles integer[],
  recent_dmg integer[],
  recent_frags integer[],
  recent_wins integer[],
  dpg real,
  recent_ts timestamp with time zone[],
  recent_dpg real,
  CONSTRAINT dpg2_pkey PRIMARY KEY (account_id, tank_id)
)

with this index:

CREATE INDEX dpg_tank_id_idx
  ON dpg2
  USING btree
  (tank_id, dpg DESC NULLS LAST);

I ran the following query:

explain analyze
    select dpg2.account_id, tank_id, (select nickname from players2 where players2.account_id = dpg2.account_id), dpg2.battles, dpg,
                frags*1.0/dpg2.battles, wins*100.0/dpg2.battles, dpg2.recent_dpg
    from dpg2
    where tank_id=545 and battles >= 150
    order by dpg desc
    limit 50;

with the following output:

"Limit  (cost=1523898.99..1523899.12 rows=50 width=28) (actual time=23950.831..23950.838 rows=50 loops=1)"
"  ->  Sort  (cost=1523898.99..1524200.69 rows=120678 width=28) (actual time=23950.831..23950.833 rows=50 loops=1)"
"        Sort Key: dpg2.dpg"
"        Sort Method: top-N heapsort  Memory: 32kB"
"        ->  Bitmap Heap Scan on dpg2  (cost=13918.06..1519890.16 rows=120678 width=28) (actual time=434.791..23922.872 rows=21963 loops=1)"
"              Recheck Cond: (tank_id = 545)"
"              Filter: (battles >= 150)"
"              Rows Removed by Filter: 1060576"
"              Heap Blocks: exact=458918"
"              ->  Bitmap Index Scan on dpg_tank_id_idx  (cost=0.00..13887.89 rows=967310 width=0) (actual time=299.796..299.796 rows=1082539 loops=1)"
"                    Index Cond: (tank_id = 545)"
"              SubPlan 1"
"                ->  Index Scan using players2_pkey on players2  (cost=0.43..5.45 rows=1 width=10) (actual time=0.105..0.105 rows=1 loops=21963)"
"                      Index Cond: (account_id = dpg2.account_id)"
"Planning time: 0.212 ms"
"Execution time: 23953.952 ms"

What's really confusing me is that the query planner is trying to resort along the dpg column, when all it needs to do is walk down the index corresponding to the given tank_id and pick the first 50 entries that satisfy the "battles >= 150" condition.

In fact, simply removing the 'order by' clause, I get the same result in 2s because it ends up using the index, which has things sorted in the order I want.

I know I've already solved my problem but why is Postgres doing this and what are the possible solutions?

Edit: I already ran analyze on the table and autovacuum is on.


texts: Options for reducing transaction log size when disk space is restricted

texts:
I have a very unfortunate situation in which I have some SQL Server (standard edition) databases living on a small (~120gb) disk, and a transaction log which has grown to 50gb+. Unfortunately, the backup disk is only 45gb. The data itself is across all databases is only ~20gb.

I need to reduce the size of the transaction log to prevent the disk filling up, but I'm unable to back it up because it's too big, and I'm unable to get even temporary space from the server vendor. I've taken a full backup of the data and transferred that out to another machine,

What are my options for reducing the transaction log size?


texts: PostgreSQL not using index during count(*)

texts:
I have a COUNT(*) query in PostgreSQL that runs often, and looks like:

SELECT COUNT(*)
  FROM customer
 WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

This query takes between 30-60 seconds to run and searches millions of records.

EXPLAIN ANALYZE shows it's doing a sequential scan, so I created the index:

CREATE INDEX customer_by_source ON customer (source_id)
WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

However, EXPLAIN ANALYZE still shows it's doing a sequential scan and isn't using the index.

How do I speed up this query and make it use the index?

Edit: My Postgres version is 9.3.3. The table has about 20 million records, divided pretty evenly among each source_id, of which another 5 aren't included in the list.


texts:
If I have solarwinds which has an alert set whenever we have more than 300 writes per second, and I know 300 writes per second is fine, and the writes per second often goes over 6500, how can I work out what the theoretical limit of writes per second is, so I can alter the alert thus making it actionable?

If "The number of writes should not exceed 85% of the disk capacity" how can I work out the number of writes that are possible with my current disk configuration?


texts: How to pass the input parameters to dynamic Pivot stored procedure

texts:
This is my query:

DELIMITER $$

DROP PROCEDURE IF EXISTS `leavemanagement`.`SP_ViewAttendance` $$
CREATE PROCEDURE `leavemanagement`.`SP_ViewAttendance` (

    IN in_branch_id varchar(10)

)
BEGIN
       SET SESSION group_concat_max_len = 1000000000;

      SET @sql = NULL ;
  SELECT
    GROUP_CONCAT(
      DISTINCT CONCAT(
        'max(CASE WHEN attendance.date = ''',
        DATE_FORMAT(DATE, '%Y-%m-%d'),
        ''' THEN coalesce(p.present, '''') END) AS `',
        DATE_FORMAT(DATE, '%Y-%m-%d'),
        '`'
      )
    ) INTO @sql
  FROM
    calendar ,attendance
  WHERE DATE >= '2015-02-02'
    AND DATE <= '2015-02-31' ;

  SET @sql = CONCAT(
    ' SELECT  ',in_branch_id, @sql,'
        from
            (
               select c.date,  a.user_id ,a.user_name,a.attendance_id,a.attendance_date
                 from calendar c
                 cross join attendance a
            ) attendance
            left join attendance  p
              on attendance.user_id= p.user_id
              and attendance.date = p.attendance_date

           where attendance.date>= date
              and attendance.date <= date
              and attendance.branch_id = in_branch_id (---------HERE IM PASSING IN PARAM)

          group by attendance.user_id'
  ) ;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

But it is showing error:

  FUNCTION leavemanagement.1max does not exist

I don't know how to pass parameters in Pivot Query.


texts: How to cache select with variables? (MySQL)

texts:
Context:

I have complicated very often executed query:

CREATE OR REPLACE VIEW ordered_words AS SELECT word.id
  FROM category JOIN frequency ON category.id = frequency.category_id
    JOIN word ON frequency.word_id = word.id
    JOIN language ON word.language_id = language.id
  WHERE
    category.name='Subtitles' AND iso_639_1='en'
  ORDER BY frequency.value DESC;

I enabled cache query_cache_type=1 and I obtain great performance improvement.

First time (4.5 s)

sql> SELECT * FROM ordered_words
500 rows retrieved starting from 1 in 4s 585ms (execution: 4s 576ms, fetching: 9ms)

Second time (5 ms)

sql> SELECT * FROM ordered_words
500 rows retrieved starting from 1 in 11ms (execution: 5ms, fetching: 6ms)

It is ok.

Problem

But now I want to add to this result one column with identifiers 1,2,3... because I would like to be able to get selected row from result of this query. I created the following query:

SELECT @ROW := @ROW + 1 AS ROW
 FROM ordered_words t
 join (SELECT @ROW := 0) t2;

But this query is not cached. Any time execution gets about 4.8 sec. I want to save to cache result of this query.

Alternative solutions:

Because of my experience in databases is small. I do not know if my approach is optimal so I describe wider context. In my application inserts are very rarely used.

My real target is execute very often query that get one random element from ordered_words with dynamically assigned distribution of probability. I do not want to create new table for this, because category and language are free parameters. I will use function rather. But I do not want execute all query any time.

Logic of randomization will be executed out of MySQL. MySQL will obtain only number of word in temporal order saved in result of ordered_words. I am planning treat column row as temporary identifier that allow select word_id without calculating all joins and ordering from first mentioned query.

Maybe is there something like temporary table created in memory, or should I use other database system?

Update

As @Rick-James suggest. Timing was wrong. Cache was created by my IDE - DataGrip not by MySQL. Finally I decided to save result of my complicated query to table in memory by command:

  CREATE TABLE IF NOT EXISTS words_memory_subtitles_en (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, word_id INTEGER)
  ENGINE=MEMORY
  AS
  SELECT word.id as word_id
    FROM category JOIN frequency ON category.id = frequency.category_id
      JOIN word ON frequency.word_id = word.id
      JOIN language ON word.language_id = language.id
    WHERE
      category.name='Subtitles' AND iso_639_1='en'
    ORDER BY frequency.value DESC;

I will mange creating and removing tables like this in PHP.

I decided to remove id from frequency table.


texts: SSIS export to CSV Flat File Destination task, error The code page on input column is ... and is required to be

texts:
I get this error on a File Destination Task,   do you know what the cause is and how to fix it ?

Package Validation Error

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

Error at export data to csv [Flat File Destination [187]]: The code page on input column "..." (958) is 1252 and is required to be 65001.

ta


texts: How do I recover from Mariadb(10.4.18) binlog corruption

texts:
I have a 3 node Mariadb Galera cluster running Mariadb 10.4.18 on ubuntu 18.4.
I applied updates to one of the nodes this morning and now Mariadb doesn't start on that node with the following error.
[ERROR] I/O error reading the header from the binary log, errno=175, io cache code=0
[ERROR] I/O error reading the header from the binary log
[ERROR] Can't init tc log
[ERROR] Aborting'
2 or the 3 nodes are running and are up to date.
There are 5 items in the mariadb-bin.index file and all point to files that are 0 length.
I have searched the internet to find out how to recover from this but the advices seems to be rather limited. I have found the following:
https://www.webmaster2020.com/forum/viewtopic.php?t=2716
http://raafat.tawasol.net/mysql-crashes-io-error/
These suggest deleting the corrupt fail and restarting. Is this safe and the best way to recover from this?


texts:
I am doing dataguard. I have manually done a backup for the standby database. It was working fine. Problem arose when I used the standby controlfile. On the primary, I have created a standby controlfile and transferred it to the standby.

scp test_sdy oracle@db3.oracle.com:/u03/app/oracle/oradata/TEST1/control01.ctl
cp /u03/app/oracle/oradata/TEST1/control01.ctl \
   /u03/app/oracle/flash_recover_area/TEST1/control02.ctl

The standby's init.ora contains:

*.control_files='/u03/app/oracle/oradata/TEST1/control01.ctl','/u03/app/oracle/flash_recovery_area/TEST1/control02.ctl'

When I start up the standby database using pfile, I get this error:

ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u03/app/oracle/oradata/TEST1_SDY/system01.dbf'

Did I do something wrong with the standby control file? How can I fix this?


texts: Need to export table into 2 columns, one repeating, one with rest stacked

texts:
Have a table that has one column I want to duplicate, corresponding to several columns I want to stack on top of each other. I have a table like this (foo):

|col1   |col2   |col3   |
|   1   |   A   |   F   |
|   2   |   B   |   G   |
|   3   |   C   |   H   |
|   4   |   D   |   I   |
|   5   |   E   |   J   |

And I want to create a table like this (foo2):

|col_a  |col_b  |
|   1   |   A   |
|   2   |   B   |
|   3   |   C   |
|   4   |   D   |
|   5   |   E   |
|   1   |   F   |
|   2   |   G   |
|   3   |   H   |
|   4   |   I   |
|   5   |   J   |

This is as far as I got, this only stacks into one column:

DROP TABLE IF EXISTS foo2 CASCADE;
select col_a INTO foo2 from (
select col1 as col_a from foo
union all
select col2 as col_a from foo
union all
select col3 as col_a from foo
) as myview;


texts: MySQL : Till when MySQL retains binary logs?

texts:
I have multiple queries :

Till when does MySQL retain the binary logs ? What is the default setting ? When does it start logging ?

What does the number of the binary log file name suggest for example in mysql-bin.xxxxxx what does xxxxxx signify.

I have oldest file with name mysql-bin.000163 how can check whether this file was the first binary log file ?

mysql-bin.index contains :

/var/log/mysql/mysql-bin.000163
/var/log/mysql/mysql-bin.000164
/var/log/mysql/mysql-bin.000165
/var/log/mysql/mysql-bin.000166
/var/log/mysql/mysql-bin.000167
/var/log/mysql/mysql-bin.000168
/var/log/mysql/mysql-bin.000169
/var/log/mysql/mysql-bin.000170
/var/log/mysql/mysql-bin.000171
/var/log/mysql/mysql-bin.000172
/var/log/mysql/mysql-bin.000173
/var/log/mysql/mysql-bin.000174
/var/log/mysql/mysql-bin.000175
/var/log/mysql/mysql-bin.000176
/var/log/mysql/mysql-bin.000177
/var/log/mysql/mysql-bin.000178
/var/log/mysql/mysql-bin.000179
/var/log/mysql/mysql-bin.000180
/var/log/mysql/mysql-bin.000181
/var/log/mysql/mysql-bin.000182
/var/log/mysql/mysql-bin.000183
/var/log/mysql/mysql-bin.000184
/var/log/mysql/mysql-bin.000185
/var/log/mysql/mysql-bin.000186
/var/log/mysql/mysql-bin.000187
/var/log/mysql/mysql-bin.000188
/var/log/mysql/mysql-bin.000189


texts: Creating High Availability Cluster with PostgreSQL

texts:
I am new to database design.  I have a task to create a high-availability cluster.  I Googled a lot, however I haven't found any good way to do this on PostgreSQL.

Where should I start and what are the steps involved in creating a high-availability cluster?


texts:
I have to do some project and need to estimate the DB size.
I'm going to use Oracle in the project because we will have a lot of transactions and data.
But in the "presentation" I need to specify, in 5 years, how much I expect the DB to grow.

So, I have a PDF with the way of calculating the row size and another stuff, but for SQL-Server.
I want to do it for Oracle.

The pdf is an estimation formula - without having the real DB, so I can't do queries to the DB to check the actual size (That's What I've seen in other posts here when I googled).

The formula for SQL-Server row is

  Row_Size = Fixed_Data_Size +Variable_Data_Size +Null_Bitmap + 4

This formula works in Oracle too?
Also, "4" is the size of  the  header of a row (I don't know how to translate the meaning in English)

So, I have to do this in Oracle. What will be the value 4 in Oracle?
The other things I think I think I've solved them.


texts: backing up selected tables in a database

texts:
We need to transport database backups over a lower bandwidth connection. We only want the data of specific tables, not the whole database.

In SQL Server 2008 R2, how can this be done? I am attempting to find the solution with the least amount of programming or scripting.


texts:
I am working on a poory designed application, so please do not judge me by the work i have been posed. I have servers table whose data can be visualised as

and attributes table which can be described as

and attribute_server table which can be described as

Now the question and scenario is what to do if the attribute has multiple types meaning, meaning a disk has its own set of types per server, and as well a cpu has multiple set of types per server, and as well multiple set of critical and warning threshold per disk attribute, per memory attribute, per cpu attribute, any idea fellas?


texts:
Forgive me if this question already ask,not much of db guy here ,
here is what i tried,

select row_number() over (partition by name order by challanto_date) , *
from (
    select
        rma,
        p.id,
        p.name,
        challanto_date,
        CURRENT_TIMESTAMP as fromDate
    from challan_to_vendor cv
    left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_vendor_id
    inner join main_product p on p.id = cvd.product_id
    union all
    select
        rma,
        p.id,
        p.name,
        CURRENT_TIMESTAMP as toDate,
        challan_date
    from challan_from_vendor cv
    left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_vendor_id
    inner join main_product p on p.id = cvd.product_id
) as a

Here is my create table script :

challan_from_vendor

  CREATE TABLE public.challan_from_vendor
  (
  id character varying NOT NULL,
  date_ad date,
  rma integer DEFAULT 1,
  CONSTRAINT psk PRIMARY KEY (id)
  )

challan_from_vendor_detail

CREATE TABLE public.challan_from_vendor_detail
 (
 id character varying NOT NULL,
 challan_from_id character varying,
 product_id character varying,
 CONSTRAINT psks PRIMARY KEY (id),
 CONSTRAINT fsks FOREIGN KEY (challan_from_id)
     REFERENCES public.challan_from_vendor (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
 )

challan_to_vendor;

CREATE TABLE public.challan_to_vendor
(
 id character varying NOT NULL,
 date_ad date,
 rma integer DEFAULT 1,
 CONSTRAINT pk PRIMARY KEY (id)
)

challan_to_vendor_detail

CREATE TABLE public.challan_to_vendor_detail
 (
 id character varying NOT NULL,
 challan_to_id character varying,
 product_id character varying,
 CONSTRAINT pks PRIMARY KEY (id),
 CONSTRAINT fks FOREIGN KEY (challan_to_id)
    REFERENCES public.challan_to_vendor (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
 )

product

    CREATE TABLE public.product
 (
 id character varying NOT NULL,
 product_name character varying,
 CONSTRAINT pks PRIMARY KEY (id)
 )

Here is my table structures and desire output.

challan_from_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12012       |        0001 |     2018-11-10
| 123121       |        0001 |     2018-11-11

challan_to_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12       |        0001 |     2018-12-10
| 123       |        0001 |     2018-12-11

challan_from_vendor_detail

 | id       | challan_from_vendor_id | product_id |
|:-----------|------------:|:------------:|
| 121       |  12012       | 121313
| 1213       |   12012     |  131381

challan_to_vendor_detail

challan_from_vendor_detail

| id         | challan_to_vendor_id | product_id |
|:-----------|------------------------|:------------:|
| 121        |  12                    | 121313
| 1213       |   123                  |  131381

product

  | id         | product_name |
|:-----------|------------:|
| 191313       |    apple   |
| 89113       |      banana |

Output

| ram         | product_id | challan_from_date | challan_to_date|
|:-----------|------------:|:-----------------:|:--------------:|
| 0001       |       191313|     2018-11-10    |2018-11-11      |
| 0001       |       89113 |     2018-12-10    |2018-12-11      |


texts: How to avoid the SORT operation in SQL query that contains ORDER_BY condition?

texts:
I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than using the index.

The raw SQL looks like this:

SELECT activity.provider, activity.provider_id, activity.crawled_at, activity.raw, activity.account_provider_id, activity.created_at
FROM activity
JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id)
JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id)
WHERE token.uid = 1
ORDER BY created_at desc NULLS LAST
LIMIT 10 OFFSET 10000;

I have already created an index provider, account_provider_id, created_by on the activity table.

CREATE INDEX activity_provider_account_provider_id_created_at_idx
  ON activity
  USING btree
  (provider COLLATE pg_catalog."default", account_provider_id COLLATE pg_catalog."default", created_at DESC NULLS LAST);

However, the query plan returned by PostgreSQL requires an expensive SORT operation.

This query would take more than 10 seconds to finish. I also tried set enable_sort = false, but it didn't work. How should I optimise this query?

EDIT: I have added the execution time graph. The SORT operation is the most expensive part of the query. Without the ORDER_BY, the query returns pretty fast.


texts: Recommendations for how I should create my table based on data given?

texts:
I'm asking this as I'm kinda overwhelmed by the data presented to me (I'm still fairly new in programming and SQL). Basically I need to create a database to store warehouse equipment information, and of course to make it as simple as possible when retrieving these data in .NET

Here are the key attributes of the equipment that perplexed me in creating a SQL table for "Equipment":

Many, but not all equipment can have SSN (this is usually a 10-digit number to uniquely identify the type of equip)
Many, but not all equipment can have serial (a number to uniquely identify the SAME equip which has multiple quantity)
All equipment have description (this is the name of the equip)
Some equip may have both SSN and serial, while some equip have none.
Location: Tells where the equipment is stored in the warehouse. Equipment with different serial may be stored in separate location. As such, need to keep track of location for every serial.
Set: Tells which equipment is part of a set. For example, a machine can be part of a set together with an antenna and a wire, all 3 are different equipment stored in "Equipment" table.

That's about it, but it's too complicating for my current skills. This is so far what I'm able to come up with:

tblEquips:

EqpID - Since an equip can have neither SSN or serial, I have to create an incremental
EqpID
SSN
Description
currentQty
totalQty
location
assemblage - datatype is bit, tells whether this item is a set
Serial - I don't know how to store this, I'm thinking of storing a long list of serials separated by a comma? But it won't work when it comes to identifying which serial is in which location

tblAssemblage:

SetID
Set -  store to identify a set along with its information. Eg. a machine, an antenna and a wire. Maybe XML data type?

As such, anyone can help modify, or even work on new tables to organize this whole chunk of interrelated data? Thanks in advance for the help, and sorry about my English - it's not my native language, I hope it's comprehensible.


texts: JDBC prepared statement - make a token null

texts:
So, I have an insert statement in a Java program I'm writing. Under some conditions, I want it to insert some values as null. However, before I can execute the statement, I have to set all of the tokens to values. How can I make it set something to null instead?

Edit:

Example:

    sql = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
            + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
            + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES (?, ?"
            + ", 'STI.PHYSICIAN', 1, 0, ?"
            + ", ?, ?)";

Sometimes, I want to insert nothing for POSTAL_CODE (for example)


texts: Return Entire Row which has the first Max Value in One Field


texts: "Remote" connection to a local MYSQL database on LAN

texts:
I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give another family member access on their laptop, but get error 10060 each time (could not connect)

I have tried giving them an account from host %, and another one from their local IP, though neither is working. I have tried having them connect to each of the following with both accounts - my actual IP, my local IP, and the hostname variable for the server. each connection returns error 10060.


texts: SQL Server max memory, page file, max degree parallelism

texts:
Three questions hope you DBA masters of the universe can answer:

I know it is best practice to set SQL Server's max memory. So if a server with 44 GB RAM has SQL Server installed we should set the SQL Server max memory to approx 37GB.

But what should we set if SQL Analysis Service and SQL Reporting Services are installed on the same server as SQL Server? Is it then better to set SQL Server's max memory to 22GB RAM so Analysis can take the other 22GB RAM?

On a server running SQL Server with 44 GB RAM what is best practice for setting the page file size? I read on Brent Ozar (http://www.brentozar.com/archive/2014/06/sql-server-setup-checklist-free-ebook-download/) the following:

SQL Server doest not need a giant page file. if you're installing other applications on the server (which we don't recommend-- SQL Server should be isolated). you may need a larger page file. If SQL Server is the only major service running on the box, we typically create a 2GB size page file on the system drive.

Microsoft says we should set page file system managed to 1.5x the RAM which matches the advice given by Remus (see -> http://rusanu.com/2009/11/22/system-pagefile-size-on-machines-with-large-ram/).
So on our server with 44GB RAM we have SQL Server installed next to SQL Analysis Service and SQL Reporting Services. What size should we set the page file?

For SharePoint the SQL Server that is part of SharePoint's farm and where SharePoint stores it's databases, the max degree of parallelism should be set on 1. Now that we have a server that is not part of the SharePoint farm (call it sql2) on this server we have SQL Server, SQL Analysis and Reporting installed. SharePoint does not store its databases on this server, but we do have reporting and analysis databases/cubes created that SharePoint reads and writes to.

Brent Ozar says:

Rule of thumb: set this to the number of physical cores in a single NUMA node (processor) sockets on your hardware or less

What should we configure on this SQL Server instance for max degree of parallelism when it has 4 VCPU's?

Hope you guys give not only me but more sql admins a bit more insight in the best practices than the jungle on the interwebs.


texts: How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql

texts:
I am using Django and postgres for a multi-tenant webapp. We have around 100 tables and each table has a tenant_id column, identifying the tenant of that particular row. I need a solution that would calculate the total space per tenant. Is there a possibility of doing that? And can it be done dynamically?

For the background: we need that so as to limit per user disc space upto a limit of X GB.

Thank you.


texts: SSIS: script task on Visual studio 2013 can not open since Visual Studio 2015 was installed

texts:
Until the installation of Visual studio 2015, it was always opening Visual studio 2012.

Attached the only error he throws, It seems he was trying to open Visual Studio 2015.


texts: In sqlplus we can set the local_listener to either the description of the listener or the listener alias. What is the difference between listener alias and listener name. Are these the same ? If not how can I resolve the listener name from the listener alias.


texts: SQL - find records that are prefixes of my string

texts:
I have a table with a column named prefix storing 'aaaa'. My application has a long string, eg. 'aaaabbbbccccdddd'. I want to select all rows in which a particular column is a prefix of 'aaaabbbbccccdddd'. The length of the prefix is variable.

How can I do it in Postgres?

I tried

SELECT * FROM my_table WHERE '%' || prefix || '%' ILIKE 'aaaabbbbccccdddd'

but it does not match.


texts:
I am using pgAdmin 4 and am looking to download query results as INSERT statements rather than as a CSV (the only option I can find). I am used to doing this with MySQL Workbench, but maybe I have been spoiled over there?

Is it possible in pgAdmin 4 to download query results as INSERT statements?


texts:
I have installed 12C and importing data through sqlldr , but it increases the temporary tablespace size tremendously which causing the oracle shutdown.
Please help and guide me what to do to get rid of this issue.


texts:
I'm trying to get the description of a table in oracle because I need to know what foreign keys does it have and which tables they refer to.
I'm using desc
but all I get is "ORA-00900: invalid SQL statement"
Any idea why?
Thanks.


texts:
If I am reading the documentation correctly, issuing this command

psql -v ON_ERROR_STOP=1 database < script.sql

where script.sql starts with BEGIN

BEGIN;
ALTER TABLE...
some other DDL...

COMMIT;

should mean that if one of the commands in script.sql fails, the whole script is rolled back?

(I am looking for a way to issue a DB upgrade that atomically succeeds or fails.)


texts:
I have a table where I need to populate values, which are incremented using given step size.  In addition I also have a notion of range, so that certain number of steps will fit inside the range (not necessarily evenly).  Once the end of range is reached, a round-robin process needs to happen.  The twist is that I also need to count how many times I did the round-robin and, once the given max of round-robins is reached, I need to sort of reset the count of my round-robins.  Since this is confusing, here is an illustration.

Let's create and populate a simple table:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE
tempdb.dbo.#mytable

CREATE TABLE #mytable (
id INT IDENTITY(1,1),
iteration INT,
step INT
)

INSERT #mytable
DEFAULT VALUES
GO 20

Now let's declare some variables and use them to update data in our table (note that the @max_iterations variable is not used, because I don't know how to leverage it and why I am asking my question):

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET     iteration = (id*@step_size)/@max_iteration_range,
        step = (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable

Here is the resulting output:

    id          iteration   step
    ----------- ----------- -----------
    1           0           7
    2           0           14
    3           0           21
    4           0           28
    5           0           35
    6           1           2
    7           1           9
    8           1           16
    9           1           23
    10          1           30
    11          1           37
    12          2           4
    13          2           11
    14          2           18
    15          2           25
    16          2           32
    17          2           39
    18          3           6
    19          3           13
    20          3           20

The problem I am trying to solve is reflected in the last three rows.  Since my @max_iterations variable is set to 2, once I exceed this value in the "iteration" column, I want to go back to 0 and start over.  The last three rows should be:

    id          iteration   step
    ----------- ----------- -----------
    18          0           6
    19          0           13
    20          0           20

I appreciate any suggestions on how to accomplish this.

Thank you!

P.S.  This problem stemmed from the need to generate SQL Agent job schedules, which are evenly spaced out, but have to run with frequency no less than once every X hrs/min, etc.  So I can't just continue incrementing the start time.


texts: HStore vs. multiple tables with inheritance - do I have a good use case for HStore?

texts:
My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects.

Now, for my localized example: I’m designing a database structure for holding experimental results. My first idea was to have separate tables for each experiment. This would look like:

However, this would require a lot of JOINs across tables since it would be fairly common for end-users to want to look for data across multiple experiments. (Side note: maybe a good use case for inheritance?). I thought I could avoid this by having a monolithic experiments table like this:

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety. This is why I’m considering using HStore kind of like this:

This appeals to me because each individual result set is very document-like, but I’m afraid that I’ll be running into the same issues that the multiple tables approach would bring.

Other considerations and thoughts that may be relevant:

I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table.
Different users will be inputting experimental data, I would use schema for this
There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

I should also mention that I have very limited experience with PostgreSQL, but I’m already delighted at what it has to offer over MySQL!


texts:
I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package.

I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be entirely dedicated to this database).

My pgdata is located in var/lib/postgresql/9.1/main. I plan to stop the postgresql service, copy the pgdata content to the new partition, then make a symbolic link to the new partition, chown the new directory to postgres user, restart postgresql... but I'm afraid this all looks more like a hack.

Is there a way to create a database specifically on a specified partition ? Something more "canonical" (not a play of word with ubuntu)


texts: Delete the N oldest entries grouped by multiple columns

texts:
I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char.

Structure of TABLE words

id | word | char | category

For example I have the following entries:

id | word | char | category
1     abc     a       1
2     abc     a       1
3     abc     a       1
4     bcs     b       1
5     bcs     b       1
6     bcs     b       1
7     csd     c       2
8     csd     c       2
9     asd     a       2
10    asc     a       2

For N=2 the following entries should be removed as their id´s are the lowest:

1     abc     a       1
4     bcs     b       1

Can somebody help? I tried various things (GROUP BY, HAVING, INNER AND OUTER JOINS) but it does not work.


texts: A way to take a backup of SQL Server 2000 database on a different domain network

texts:
I am trying to take backups of databases on SQL Server 2000. This server is on a different network domain. I can access SQL Server Management Studio using sa login and password from the network domain I work on. I did some research for a work around and tried the following recommended script I read about.

EXEC master.dbo.xp_cmdshell 'NET USE B:\\xxx\backups\xxxx\sql /user: xxxx\xxxx xxxxxxx'
GO
BACKUP DATABASE [XXX] TO DISK = N'B:\\xxx\backups\xxxx\sql\XXX.bak' WITH NOFORMAT, INIT, NAME = N'XXX-Full Database Backup', SKIP, REWIND, NOUNLOAD, STATS = 10
GO

Failed with an error:

  Msg 3201, Device error or device off-line

Is it possible to take a backup on a different network domain using SQL Server Management Studio as in my case? If so, any suggestions.


texts:
I'm trying with this code:

DELIMITER //
CREATE TRIGGER myTrigger AFTER UPDATE ON cityies
FOR EACH ROW
BEGIN
 if NEW.visitors <=> OLD.visitors THEN
set visit_time = now()
END IF;
END;//
DELIMITER ;

I'm getting this error message:

  unknown system variable visit_time

Note: visit_time has DATETIME type.


texts:
Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB.

We've disabled verbose logging, truncated the logging table but now we want to shrink the MDF file to reclaim disk space as the drive the MDF file is on is only around 200GB. I don't have the luxury of increasing its size. Our disk space monitoring tool is alerting me that drive space is less than 8% over and over and over.

Given this is the first instance this has ever happened, we don't expect to ever need that extra 102GB of disk space again. We'd like to reclaim it.

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size. I've also tried using DBCC SHRINKFILE and DBCC ShrinkDatabase with NoTruncate and TruncateOnly. No matter what I do the file doesn't seem to budge.

One potential issue is that somehow the database initial size is 188GB. Deleting and re-creating the database isn't a very practical option for us so how can I shrink the MDF file and reclaim our disk space?

Running dbcc shrinkfile('My_Data'), I get the following results:

DBID: 5
Field: 1
CurrentSize:24030464
MinimumSize: 128
UsedPages: 17770472
EstimatedPages: 17770472

The database MDF file is still the same size (192,243,712 KB).

The table has a clustered index:

Name:TRACES
ObjectID: 394588594
Type: Clustered Index/b-tree

The query:

SELECT
    name,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

...for the database I am trying to shrink, returns:

Name: My_Data AvailableSpaceInMB: 48693.375000
Name: My_Log  AvailableSpaceInMB: 67028.437500

I've also tried shrinking in small chunks using a PowerShell script that shrinks in 20MB increments. That also doesn't change the size of the MDF file.

I've tried dbcc shrinkfile(My_Data) without any other options from SQL Management Studio. It runs for a long time, finishes, and the MDF file is still the same size. Specifying a target size, it runs for a good 30-40 minutes (finishes) and nothing changes file size wise.

I ran this: DBCC SHRINKFILE (N'My_Data' , 139045) and get the following results:

DbId: 5
Field: 1
CurrentSize: 24030464
MinimumSize: 128
UsedPages: 17808744
EstimatedPages: 17808744


texts: Who created a procedure? (SQL Server 2008 R2 )

texts:
Is there a way to list who created certain procure in SQL Server 2008?

In this SO link there are some answers but from 6yrs ago.

I know it's a best pratice to write your name and date, and then start to create the procedure, but I don't see this where I work.

If there is no way to do this, can this task be made with triggers?

And a bonus question. Is this the job, of a dba? to know who created it?

Thank you very much.


texts: Best way to optimize an aggregated issues over time query?

texts:
I have a query that creates an aggregated issues over time report that includes back-filling data from entities with missing dates. I've been using the following query for a few years but it is starting max out my DB CPU on big datasets. I'm a little lost on what to focus on first and I could use some guidance.
SELECT t1.yearmonth, SUM(rt.errors) as errors, SUM(rt.alerts) as alerts FROM
  (SELECT d.yearmonth, r.user_id, MAX(r.date) as date FROM
    (SELECT DISTINCT  date_add(date, interval  -WEEKDAY(date)-1 day) as yearmonth FROM reports) d
  INNER JOIN reports r ON d.yearmonth >=  date_add(r.date, interval  -WEEKDAY(r.date)-1 day)
  GROUP BY d.yearmonth, r.user_id ) t1
INNER JOIN reports r1 ON r1.user_id = t1.user_id AND r1.date = t1.date
INNER JOIN report_totals rt ON rt.report_id = r1.id
GROUP BY t1.yearmonth
ORDER BY t1.yearmonth

The example here creates results based on a weekly interval, but my application supports daily, monthly, quarterly, and yearly as well.
I do have a SQL fiddle setup here for further context
http://sqlfiddle.com/#!9/6c7cc82/7
I think the performance hit may be coming from the date_add and date_format functions, but I'm not sure how to accomplish what I'm doing without them. Maybe a calendar table? Any help would be appreciated, thanks!


texts: Like predicate to match a whole word only

texts:
I have a SQLite database with a table named minecraft.

+----+----------------------+
| id |         name         |
+----+----------------------+
|  1 | Pocket Mine MP       |
|  2 | Open Computers       |
|  3 | hubot minecraft skin |
|  4 | Terasology           |
|  5 | msm                  |
+----+----------------------+

I need to find all the records which have 'e' and 'o' in their 'name' field. Here is my Select query:

select * from minecraft where name like '%e%o%'

Here is the result of the above query:

+----+----------------+
| id |      name      |
+----+----------------+
|  2 | Open Computers |
|  4 | Terasology     |
+----+----------------+

The problem is that the Like predicate matches the entire value, not words. The row with id = 2 should not be matched, because all the criteria didn't happen in a single word ('e' is found in the first word and 'o' in the other word): Open Computers.

How should I change my Select query so it matches the only row with 'e' and 'o' in a single word?

+----+------------+
| id |    name    |
+----+------------+
|  4 | Terasology |
+----+------------+

Thanks


texts: Can I tell when and who updated my database from v11 to v12 on Azure?

texts:
Earlier today I noticed that all my Azure SQL database servers where updated from v11 to v12. I am the only one in my company that should be touching those dials, but not the only person in my company with the access. Since I did not initiate this upgrade I would like to find out who did, and when this unplanned update occurred.

I know I could write an email to everyone in my company with the access asking WTF for information politely, but I'd rather start the conversation with as many facts as possible. Also, I'm inclined to think this was a Microsoft/Azure SNAFU, because upgrading all servers feels too deliberate.


texts: Query looks optimal but logical reads is huge

texts:
I run the following (really simple) query in SQL Server:

    SELECT MAX(PK_Field1)
    FROM MainTable kh
    WHERE kh.PK_Field1 >= '2014-12-01T00:00:00'
    AND kh.PK_Field2 = 1572
    AND kh.PK_Field3= 'FD5BF2F3-8ED7-479C-A71F-D04E4288CBFC'

And I get these stats from it:

Table 'MainTable'. Scan count 9, logical reads 31078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 44 ms.

Although the elapsed time is not that much, this query is executed around a thousand times every ten minutes, so having more than 30k logical reads doesn't seem quite optimal to me.
However, the main index in this table is designed in a way that a query like that can take full advantage from it. The table MainTable contains this:

PK_Field1 datetime
PK_Field2 int
PK_Field3 uniqueidentifier
Another_Field datetime

This table contains no other columns, it has 3 million records and the only index is the clustered one on PK fields (in same order as defined in the table, ordered ASC). The actual execution plan shows the following:

Additionally, fields PK_Field2 and PK_Field3 are foreign key referencing two other tables. Referenced columns are IDs in their respective tables.

What do you think? Is there room for some optimization?


texts: How to make ON CONFLICT work for compound foreign key columns?

texts:
I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound.   Here's an example.

create table foreign_table (
   id_a text    not null,
   id_b text    not null,
   id   integer primary key,
   constraint ft_a_b_key unique (id_a, id_b)
);

create table my_table (
   id          integer,
   ftable_id_a text,
   ftable_id_b text,
   constraint my_table_a_b_fk
      foreign key (ftable_id_a, ftable_id_b) references foreign_table (id_a, id_b)
);

Using this query:

insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;

where, say, 'a3' isn't in foreign_table, I would expect the ON CONFLICT to handle the error.

Instead I get the error:

[23503] ERROR: insert or update on table "my_table"
   violates foreign key constraint "my_table_a_b_fk"
Detail: Key (ftable_id_a, ftable_id_b)=(a3, b3)
   is not present in table "foreign_table".

Is there a way to correct this so ON CONFLICT handles the error?