texts: Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

texts:
Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size.

Select
    DB_NAME(mf.database_id) AS [Database Name],
    mf.Name,
    mf.physical_name PhysicalName,
    cast(mf.size as bigint) * 8192 mfSize_bytes,
    fs.size_on_disk_bytes fsSize_bytes
From sys.master_files mf
    Left Join sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fs
        On mf.database_id = fs.database_id and mf.file_id = fs.file_id
Order By DB_NAME(mf.database_id)

I am confused- converted both values to "bytes", comparing them. But on all instances i checked- temdb file size (from sys.master_files) is smaller than file size on disk (from sys.dm_io_virtual_file_stats).

In all other cases if there is difference, then that is correct (size on disk is actually smaller than file size- because they are sparse files).

What is the reason for this difference?

UPDATE:
When i am querying

Select is_sparse, * From sys.database_files

On SQL Server 2005 database snapshot, then column is_sparse = 0 (because it is showing properties of the files from original database, not snapshot file. So- is there bug in documentation?).


texts: difference between create constraint non clustered and create index non clustered

texts:
I am new to sql server. i was trying to learn creating index. i tried to create a table with below query:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

The non clustered index got created automatically on primary key. Now, i want to create NCI on Primary key column with INCLUDE. I did create it with below query:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_CountryId] ON [Ref].[Country]
(
    [CountryId] ASC
)
INCLUDE ([CountryName])

As you can see in below image, there is two NCI on same column that is CountryId, one is created by default while creating a table(PK_Country) and another which i created with INCLUDE using above query(NCI_CountryId).

I do not want two NCI's on same column, need only NCI created with INCLUDE. Is there a way to create NCI with include on PK at time of creating table ?

I'm working on sql server on Azure VM.


texts: Any way around unique index 16 column max

texts:
According to the CREATE INDEX documentation:

  Up to 16 columns can be combined into a single composite index key.

We've got a table with ~18 columns that need to form a unique combination. This table is not performance sensitive -- we rarely update values/insert records. We just need to ensure that we avoid duplicating our records... and thought we could impose a simple uniqueness constraint.

Any ideas? I'm open to avoiding the unique index/constraint entirely if there is a better way.


texts:
I created certificates according to the instructions on the official website of MongoDB, but it doesn't work properly.

I have a replica set and self-signed certificates.

I need a full encryption of traffic between replica members. How do I properly set up these replica members? And how do I enter the MongoDB shell?

I created certificates according to the instructions, in
mongod.conf:

sslMode = requireSSL
sslPEMKeyFile = /etc/ssl/mongo.pem ...

and try the add the parameter:

sslCAFile =  /etc/ssl/mongo.crt

and tried to enter:

mongo --ssl --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/mongo.pem or
mongo --ssl --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/mongo.crt

I get the error -  The server certificate does not match the host name.

If I use the client certificate then error: SSL peer certificate validation failed:self signed certificate.

the Mongod service is up and running. Mongo version - 3.0.2. Centos - 6.5 x64.

I went through a lot of links but haven't found a working solution.

If it is possible can you please give me the steps?


texts:
I created the local service. My server name is like sqlitesync.com. In my database folder created the configuration (web.config) and WCF web service (AuroraSyncService.svc). When I test this service in browser it shows nothing.

i followed this tutorial Link

When start the service http://sqlitesync.com/sqlitesyncservice/AuroraSyncService.svc in browser it shows the empty like

When I create my own (index.html) http://sqlitesync.com/index.html it works fine

I don't know how to solve this also I am new to SQL Server.

Anybody provide a solution to solve this..

configuration code:

<connectionStrings>
<add name="AuroraSyncCn" connectionString="Data Source=(local);Initial Catalog=SQLite-Sync.com;User ID=***;Password=***" providerName="System.Data.SqlClient" />


texts: Can we download SSRS 2017 to use with SQL Server 2016?

texts:
We are using our organization's SQL Server 2014 SP3 servers.  (They also have 2016 SQL Servers, but we elected to skip 2016 and just move to the next SQL servers when they get installed.)
We have a need for the REST API access in SSRS 2017, so we would like to upgrade our current SSRS server to SSRS 2017.

The Windows server admin just set up a new/test SSRS server for us, but he used SSRS 2016, because that is the latest media that the organization has, and they are of the impression that we can only install SSRS for the SQL server version that we're using.

Am I correct in thinking that it should be possible for us to download (for free) and install SSRS 2017 and then point it to the existing 2016 SQL servers?  Or do we need to purchase a license for SQL Server 2017 to be able to use SSRS 2017?

(This seems similar to existing question SSRS 2016 on SQL Server 2014, but that question and answer seem to focus on SQL Server Express, which is not our situation.)


texts: Could once `max_standby_archive_delay` and `max_standby_streaming_delay` with value -1 can cause replication stop on heavy loads?

texts:
In my replication servers on a postgresql master-slave replication scheme on aws RDS I get the following error:

SQLSTATE[40001]: Serialization failure: 7 ERROR:  canceling statement due to conflict with recovery

The cause, as far as I understood is that the replication happens simirary like a database migration. A sequence of queries is written something called WAL and then are being executed in a FIFO sequence.

Also, I understood that once you execute queries whilst wal is being executed it can cause conflicts because sometimes the query that is currently is being executed can cause fetching stale data.

So according to documentation there are delays that allow first the current queries to be executed and then apply wal changes. Theese are:

max_standby_archive_delay
max_standby_streaming_delay

But setting these values as -1 on heavy queries (query execution time >30s) will cause the replicas to have stale data for extended period of times?


texts:
Suppose a DB contains two tables A and B as given below:

 A
---------------------
 id        SERIAL
 id_b      INTEGER -- NOT NULL reference to B.id
 ts_update TIMESTAMP

 B
--------------------------
 id             SERIAL
 ts_last_update TIMESTAMP

Now let's say that we'd want Postgres to automatically update ts_last_update in B such that B.ts_last_update is always equal to MAX(A.ts_update) WHERE A.id_b=B.id whenever A receives new rows, updates to existing rows or rows are deleted.  Similarly, it would be desirable to ensure that any updates to ts_last_update in B conform to the same rule above.

I thought of introducing a trigger that is executed on insert, update and delete operations on A.  But it seems that there'd be a need to also introduce a CHECK constraint to ts_last_update in B, however this would mean that whenever the trigger in A runs, the check constraint in B would also run which means that two queries would be executed.

Is my reasoning above correct or is there a better way of enforcing this strange rule?  Is there perhaps a way of specifying some sort of exclusivity where when the trigger runs, the check constraint is ignored?


texts:
I have data in this format

    Skill   Min  Ideal  Max
    ==========================
     C++    0      1    1

I want to display it like this:

    Criteria    C++
   =================
     Min          0
     Ideal        1
     Max          1

I am confused at the part where I want to convert Min, Ideal, and Max into criteria.


texts:
I have a table which needs to do pivot as column to row, but getting data type error,
I have below table data

Need to pivot and output should be like

I tried with query:

select prod As product_id , para As parameter_id
from
(
  select product_family_id,product_id,parameter_id
  from product_family_exclusion
) d
pivot
(
  max(product_id)
  for parameter_id in (prod,para)
) piv;

But getting error:

  Msg 8114, Level 16, State 1, Line 15 Error converting data type
  nvarchar to int. Msg 473, Level 16, State 1, Line 15 The incorrect
  value "prod" is supplied in the PIVOT operator.

What I am missing about PIVOT table?


texts:
Does anyone know of any good tools for cnf configuration management?  It's often the case I have a group of machines that has a very similar config except for a few differences such as server id or buffer pools to match their specific hardware.

I'm looking for something where I can setup a general template, tell it I want to generate some configs for that machine from that template, have it prompt me for the variables that need to be filled in and write out the .cnfs for me.


texts: SQL Server 2012 Enterprise: Copy Databases to New SQL Server, Same Domain, Users, Etc

texts:
Am having trouble with what seemingly should be an easy process.  I'm a SQL Server n00b, so please be gentle and very explanatory with your answers.

Currently, I have a SQL Server 2012 cluster (running on top of Windows Server 2008R2), and have created a new SQL Server 2012 Enterprise server in the same AD domain that I want to move all my databases to so I can start using this new instance (its running all SSD drives for better performance with databases, logs, temp logs, etc, all on different 512GB SSD drives).  The new instance uses the same AD group as SQL administrators, so logins are the same.

I want to copy all the databases from the "old" SQLCluster to the new SQL instance (the new instance is standalone). I've tried the Copy Database Wizard, making sure firewalls are stopped on both instances (seems easiest to avoid conflicts), telling it to make a text log file at the root of C: drive in case anything goes wrong.  However, the wizard gets to the last step (running the job) and bombs out, but doesn't create a log file ... it just says the job failed, giving me little to go on.  Both instances have the SQL Agent running.

Can someone give me some guidance on the easiest way to do this while leaving the databases on the source cluster online?  Note that I also have hourly backups that run via R1Soft that make copies of the database and log files; I thought perhaps I could just dump the MDF and LDF files from this backup in a directory and re-attach them, but the file structure is different on the new SQL Server 2012 machine with log files and DB files on different drives, and I get an error there with that, and don't know how to change those paths to reattach the databases from the backup (they are straight images of the MDF/LDF files, NOT SQL Server backups).

Thanks for any help you can provide.


texts:
The likely errors that raises "snapshot too old"s during datapump export are well documented all over the internet such as :

undo_retention parameter being too short
redo logs groups are too small or there are too few
undo tablespace is too small

Is there a way to monitor those failure cases while the job is running ?

The kind of solution I'm looking for would be a query that would report :

total percentage of the snapshot's undo tablespace usage until it needs to auto-extend or maxes out
total percentage of redo logs usage until it overwrites itself
time left until undo retention is reached by the snapshot

Any query that does part of these things would be welcome. Does looking up such information requires oracle analytic packages? Is it possible at all to query such a thing ?


texts: I have a transaction log shipping configured. Job activity monitor shows that both Copy and Restore steps have failed, however, I see that backup files are in fact copied across to the secondary server and also the database has been restored successfully. What is going on?


texts: GoogleSQL query first range of records and perform average

texts:
Using GoogleSQL I want to extract just the first 24 records of each record_id, based on time period. After that perform an average over that range (1-24), and also calculate the growth rate period over period.
Query  works fine but looking for optimization.

record_id, period, downloads
123, 2004-01-02, 100
123, 2004-01-09, 200
345, 2004-01-09, 180
123, 2004-01-16, 110
345, 2004-01-16, 200
345, 2004-01-23, 500
123, 2004-01-23, 100

SQL Query:

    SELECT
      id,
      period,
      record_id,
      downloads,
      AVG(downloads) OVER (PARTITION BY record_id) AS avg_downloads,
      100 * (downloads - growth) / growth AS growth_rate_percent, -- (Present - Past) / Past
      AVG(growth_rate_percent) OVER (PARTITION BY record_id) AS avg_growth_rate_percent
    FROM
      (SELECT
        record_id,
        period,
        downloads AS downloads,
        ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY period) AS id,        -- Enumerate Rows.
        LAG(downloads) OVER (PARTITION BY record_id ORDER BY period) AS growth,  -- Previous Record.
        AVG(downloads) OVER (PARTITION BY record_id) AS avg_all_downloads,     -- Average downloads.
       FROM
        my_table)
  WHERE id <= 24;


texts: How do I get the execution plan for a view?

texts:
I have a schema with a number of views.  I need to check the execution plans to make sure the appropriate indexes are in place and being used.

How do I do this?

I'd rather not have to copy and paste the output from show create view <viewname> into explain, especially as some of the views are built on top of other views and this would be quite a pain.


texts: MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

texts:
MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows:

    CREATE TABLE test1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(100),
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Transaction1:

    mysql> SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql>
    mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test1(name) values ('shit1') ;
    Query OK, 1 row affected (0.00 sec)

Transaction2:

    mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test1(name) values ('shit2') ;
    Query OK, 1 row affected (0.00 sec)

Transaction1:

    mysql> delete from test1 where name = 'shit1' ;

The transaction1 now trying to apply exclusive lock on non-committed row from transaction2. How can it be possible ? "shit2" row is not committed, mysql use next-key lock in the delete ? why ?

Transaction2:

    mysql> delete from test1 where name='shit2' ;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock details: where name = 'shit1' tried to lock "shit2"

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-05-21 19:24:16 0x7f6d16db5700
    *** (1) TRANSACTION:
    TRANSACTION 24153, ACTIVE 61 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
    MySQL thread id 5009, OS thread handle 140106507769600, query id 111381 localhost root updating
    delete from test1 where name = 'shit1'
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24153 lock_mode X locks rec but not gap waiting
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) TRANSACTION:
    TRANSACTION 24154, ACTIVE 28 sec starting index read
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 5008, OS thread handle 140106511636224, query id 111382 localhost root updating
    delete from test1 where name='shit2'
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 000000005e59; asc     ^Y;;
     2: len 7; hex 02000000f10301; asc        ;;
     3: len 5; hex 7368697431; asc shit1;;

    *** WE ROLL BACK TRANSACTION (2)


texts: Better way to restore to existing pg database

texts:
We want to restore to an existing 9.3 pg database. What we ended up doing was:

1) delete the database and recreate again,
2) use pg_restore to restore.
3) reboot the ubuntu server to allow rails app to pick up the new database.

We tried the following pg_restore command and it did not restore to an existing database:

$pg_restore --dbname=existing_database --verbose /home/myname/dbbackup.backup

There was no change of existing_database after pg_restore. Is there a command we can restore to an existing pg database without deleting it first as we did? We have to use command line. Many thanks.


texts: Windows Authentication Doesn't Work Locally

texts:
I have a box running SQL Server 7 (I know) on Windows XP (I know).

All of the sudden, I'm unable to use Windows authentication to connect to the database from the local machine.

I can connect using SQL authentication on the local machine.

I can connect using both SQL and Windows authentication from another machine.

Here's the error I get when using Query Analyzer to connect to the local database using Windows authentication.

I've tried connecting using both the machine name, . and localhost.

Any ideas?

Thanks!

Update:  Added Screenshot of server protocols


texts: Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

texts:
EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"

We have a client that reports error "1046 subqueries are not allowed in this context. Only scalar expressions are allowed" for SQL Server 2008 R2.  We cannot reproduce in house.  Is there a server setting or database configuration option that can cause this?

-- Simplification of tables looks like this

create table foo_type(
    foo_type_id int not null,
    foo_type_name nvarchar(100) not null,
    primary key (foo_type_id)
)

insert into foo_type values (1, 'type one')
insert into foo_type values (2, 'type two')

create table foo(
    foo_id int not null,
    foo_type_id int not null,
    foo_name nvarchar(100) not null,
    primary key (foo_id),
    constraint fk_foo_to_foo_type
    foreign key (foo_type_id) references foo_type
)

insert into foo values (11, 1, 'foo one')

-- and the mysteriously failing insert

insert into foo values (
    (select coalesce(max(foo_id),0) + 1 from foo),
    2,
    'foo two'
)


texts: Oracle 12c Client Compatible with 9i Server?

texts:
I'm developing on a new .NET application that needs to read/write data from an Oracle 9i database. I've got it working with ODAC 11.2.0.4 which comes with Instant Client 11.2. I had selected this version because I had read that Oracle 12c Client was only backwards Compatible to version 10g release 2 servers. Unfortunately I can't find that reference.

I would prefer to use latest version of ODAC 12.2, but I need to confirm that I'll still be able to connect to a 9i database.

Note: All the links I found to Oracle Compatibility charts are hidden behind an Oracle registration wall that isn't currently working for new signups. So I'm asking the question here.


texts: BCP woes: Unexpected EOF encountered in BCP data-file

texts:
I have a table defined as such:

CREATE TABLE [dbo].[IpMetadata](
    [StartIp] [bigint] NOT NULL,
    [EndIp] [bigint] NOT NULL,
    [CountryCode] [char](10) NOT NULL,
    [ProxyType] [varchar](50) NULL,
    [ProxyDescription] [varchar](50) NULL,
    [IspName] [varchar](100) NULL,
    [MobileCarrier] [varchar](50) NULL,
    [MobileCarrierCode] [varchar](50) NULL,
    [Latitude] [varchar](50) NULL,
    [Longitude] [varchar](50) NULL,
    [PostalCode] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [GmtOffset] [varchar](50) NULL,
    [SupportsDaylightSavings] [char](10) NULL,
    [MetroCode] [varchar](50) NULL,
    [AddressCount] [int] NOT NULL,
     CONSTRAINT [PK_IpMetadata] PRIMARY KEY CLUSTERED
    (
        [StartIp] ASC,
        [EndIp] ASC
    )
)

I have a UTF-8 encoded sampling file (D:\data\ipsnip.csv) with tab-delimited, CRLF terminated rows to insert into this table like so:

#start-ip   end-ip  edge-two-letter-country proxy-type  proxy-description   isp-name    mobile-carrier  mobile-carrier-code edge-latitude   edge-longitude  edge-postal-code    edge-city   edge-region edge-country    edge-gmt-offset edge-in-dst edge-metro-code address-count
0   0   **                  0   0   0   0   reserved    *** *** +9999   n   -1  0
1   255 **                  0   0   0   0   reserved    *** *** +9999   n   -1  254
256 16777215    **                  0   0   0   0   reserved    *** *** +9999   n   -1  16776959
16777216    16777343    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   127
16777344    16777407    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777408    16777471    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777472    16778239    cn          chinanet fujian province network        0   26.0786 119.298 350000  fuzhou  35  chn +800    n   156115  767
16778240    16779263    au          big red group       0   -37.8387    144.99  3141    south yarra vic aus +1000   n   36206   1023
16779264    16781311    cn          chinanet guangdong province network     0   30.6611 104.082 510000  guangzhou   44  chn +800    n   156196  2047
16781312    16785407    jp          i2ts inc.       0   35.6838 139.754 100-0001    tokyo   13  jpn +900    n   -1  4095

I run the BCP command like so:

bcp MyDatabase.dbo.IpMetadata in D:\data\ipsnip.csv -F2 -Slocalhost -n -T

I get a response back like so:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

I have tried explicitly specifying the column and row terminators.  I have tried using unicode column definitions.  I have tried changing the line ends to LF instead of CRLF.  I have tried replacing the field terminators with semicolons/ pipes.  I have tried -n and-N.  I don't know what else to try.  Can anyone assist?


texts: SQL Server Query with literals runs instantly, using variables takes minutes

texts:
For Microsoft SQL Server 2017 (RTM-CU20) on Azure running an ERP system (so changing the code here is not an option).

I have a table with over 42 million rows.  It's a machine operating stats tracking table.   Users enter operating statistics by equipment, date, shift and type of stat (Operating hours, Tons, whatever).

The table has a primary key structure like this (please no comments about nchar instead of nvarchar or storing dates as strings, that's how the ERP vendor provides it and that's just what I have to work with):

equip nchar(12)       This is a 12 character equipment number
key_type nchar(1)     One of two values E for equipment or G - always E here
shift_seq_no nchar(2) Sequence of shifts (usually 00 or 01)
stat_date nchar(8)    Date, YYYYMMDD
stat_type nchar(2)    A code for stat type, OH here (operating hours)
trc_seq_no nchar(3)   A sequence number for when there's more than one entry per shift

It appears that the calling code (java based web app making a JDBC call to the database with JBOSS in the middle of it all) queries a list of equipment scheduled for preventative maintenance in a given date range and goes looking for the most recent stat for each piece of equipment, since the row will contain the cumulative stat value for the equipment.

It then puts together a very long SQL query that effectively does this (replace x with appropriate variables:

select * from table
where (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or ( and on and on)

There are 73 repeats of the 'OR' in total

I generated two queries based upon what I can see is running.

One where I just have a straight up set of 73 sets of literals (equip = '000001234567' and col = 'value' and etc) - this runs instantly.  The actual execution plan shows a Clustered Index Seek (Clustered) in a Nested Loop with a Constant Scan showing 73 executions of 1 row read and a run time of less than 1 second

I then took the same query and declared 428 variables and plugged them into the query in place of the 428 literals instead. The actual execution plan shows a Clustered Index Scan (Clustered) in a single step (Execution plan says SELECT -> Filter -> Clustered Index Scan).    The hint shows all 42,271,100 rows read with a run time of 5 minutes and 34 seconds when run in SSMS.  This is a web based application and the debug tracing through the online app comes in at over 8 minutes.   You can imagine that just isn't deemed acceptable for an online screen.

Now that the long story is complete, does anyone have any recommendations of SQL Server tuning that might be able to be done in order to make this query run via variables the same way it runs with literals since I can't touch the code.

I think I'll be sending this back to the vendor for analysis as well, but I was hoping that someone here in the brain trust might have an idea for a quicker solution (since my Google Fu has failed me at the moment).

I've seen several articles about this happening when querying a view and the general solution seems to be "add a WITH (RECOMPILE) hint" but I'm not using a view here, I'm querying a table directly.

Thanks in advance for any help here.  I'm primarily an Oracle guy and know enough about SQL Server to be dangerous.


texts:
create table test(id INT, value DECIMAL(25,6));
insert into test values (16, 10e16);
insert into test values (17, 10e17);  -- Works till here
insert into test values (18, 10e18);  -- Fails here. Data truncation: Out of range value for column 'value'
insert into test values (19, 10e19);

How is this possible?


texts: Mysql Connections are string when checking from SHOW STATUS LIKE '%onn%';

texts:
I have a Mysql db server which seems to be getting slow by time. When i am checking detail from SHOW STATUS LIKE '%onn%'; I get following details

Variable_name                                  Value
---------------------------------------------  --------
Aborted_connects                               2
Connection_errors_accept                       0
Connection_errors_internal                     0
Connection_errors_max_connections              0
Connection_errors_peer_address                 0
Connection_errors_select                       0
Connection_errors_tcpwrap                      0
Connections                                    2327
Max_used_connections                           7
Performance_schema_session_connect_attrs_lost  0
Ssl_client_connects                            0
Ssl_connect_renegotiates                       0
Ssl_finished_connects                          0
Threads_connected                              5

Is there anyway i can get the connections detail of the open connections?
Like source ip, username e.t.c.
Mysql version is 5.6


texts: How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)?

texts:
I'm building a site ("The Site") and I'm considering how to design the DB schema for it.

I have a few scenarios that will affect the outcome:

The user can choose to register a native account on the site, which will make him use that account for login in to his account on the site in the future.
The user can choose to login via another site ("external site") (such as facebook connect, google, foursquare, etc..), which will make him use that for login in to his account on the site in the future.
Doesn't really matter which login method he chose (native account or external site account), the user will be able to authenticate his account on the site with external sites (such as facebook, twitter, instagram, etc...) for using the data he has on the external site (such as his tweets from twitter, his photos from flickr, etc..), in the site.

How should I design the schema of the DB ?

For the first two scenarios, I thought of having a table called Users that has an id, and a type fields. The type reference a different table, such as FacebookUsers or GoogleUsers, each has a user_id field - which has a foreign relation to the Users.id field - and other fields such as the first_name, profile_picture, etc..

However:

I'm not sure how smart it is to use this approach.
I don't know how to implement the third scenario in such an approach.


texts: Is it okay to have different lower_case_table_names value on master and slave?

texts:
I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.

I haven't started replication on the slave, but everything looks good at this point.

Will it cause any issues with replication?


texts: Can the default minimum date range for 'datetime' be changed in SQL Server 2008 R2?

texts:
The minimum date range is set to somewhere in the 1750's. I need to store dates prior to 1750.  One solution that came to mind is to store the dates in a particular format in a 'varchar' field, and later on, retrieve them back to the UI as we want.

Is there a better solution to this - an elegant one?


texts:
Want to know how to save cropped images in the database? The info about the images or reference to the cropped images in itself?

Working on a marketplace for artists where artists can upload their work. The way the app is designed, most artists asked us to provide them with a functionality to crop their images so they look good on their profiles

I propose two solutions and wanted to know if they are feasible:

When a user has cropped the photo, save that cropped photo in your storage and store the cropped photo's path in the table
I was exploring the internet and I found a library which allows the user to crop the image and when he/she's done, the library returns a meta object about the image, it's co-ordinates, width height etc.

I'd like to know your thoughts, the first one consumes memory, the second one causes rendering issue since I'll have to show the image based on the co-ordinates


texts:
For the purpose of reporting database growth, I am created everything except one and most important thing. In SSDT I'm created report, which contains graph line which need to be show in depend of parameters. I have drop-down list of databases, graph where are in sections: VALUES (Fields!dataSize.Value), Category Groups(date), Series Groups (databaseName).
I have parameter databases_id (dataset1, value field: id, label field: databaseName).

When I start report and select some database and click on VIEW REPORT, I get lines for every parameters(databases) on list. How to get only lines for selected parameters(databases)?


texts: In a replication setup, does corrupted data also gets replicated?

texts:
Say one of the tables got corrupted, will that corrupted table still get replicated to the other DB? Or, will the replication stop to prevent the other DB from being corrupted too?

Note: Question is for both master-master or master-slave setup.


texts: how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL

texts:
Here is tbl1 :
gid pm
1   2.1
2   2.0
3   1.95
...

Here is tbl2 I want :
gid num pb  gidTbl1
1    1        1
2    2        1
3    3  2.1   1
4    1        2
5    2        2
6    3        2
7    4  2.0   2
8    1        3
...

On an insert in tbl2, I would like that pb = pm for gidTbl1 = gid AND for the max num in tbl2.
Here are a trigger and the function on tbl2 I wrote :
CREATE OR REPLACE FUNCTION maj_pb()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
IF TG_OP IN ('INSERT')
THEN NEW.pb = pm from tbl1
        where NEW.gidTbl1 = tbl1.gid
        and num = (select max(num) from tbl2
                          where NEW.gidTbl1 = g.gid
                        ) ;
RETURN NEW ;
END IF ;
END ;
$BODY$;

CREATE TRIGGER trg_maj_pb
BEFORE INSERT
ON tbl2
FOR EACH ROW
EXECUTE PROCEDURE activite.maj_pb();

error message is :

num doesn't exist

I presume that the trigger is looking for num in tbl1 (?). But I can't find an other way to write the function.

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.


texts: PostgreSQL: psql command-line client does not show all schemas

texts:
I have PostgreSQL 9.3 server. Connecting using an JDBC client (Squirrel) and executing

select schema_name from information_schema.schemata

shows me all the schemas on the server.

Connecting the same using psql via an SSH session:

su -l postgres
psql -U postgres -W -p 5432 -h localhost

and the same select shows only a few schemas:

schema_name
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

There is only one DB instance on the server. Username and password are the same. Why is that? Do I need to configure something for psql?


texts:
I'm using SQL Server 2012 to develop php/web applications.

On my development machine, I have SQL Server also.
The php codebase is version controlled with git.

The various applications we have (and there's more than 30) are in the process of being updated. As this happens, the database structure sometimes changes, and the data (even on my local content) changes a lot too.

Even as we develop new code, we need to work fixes and amends into the old code as well.
We use branches to develop this within git and that works well.

However: maintaining a database set to match these is a serious hassle.
If a project connects to three databases, and for example, there are three branches, the development becomes confusing, as well as a hassle to set up.

So - on to my questions:

Is there a database version of "profiling"?
Is it possible to have parallel versions of the databases that can be switched to active/inactive - just as switching a branch separates what amounts to the same code?
Switching a branch in git is a matter of seconds. Is there a tool to do the same within parallel versions of the current db structure?

This would save me some serious headaches (and the associated dangers of leaving a db-config pointing to the wrong database).

Any advice or tips to pointers to approach this problem would be appreciated.


texts: Multiple Cascade Paths (Not really) - Sql Server

texts:
In my database I have a table with 2 columns referencing the same column in a separate table.

I know this creates a race condition, but I have a check in place to make sure they don't point to the same row. Is there any smart way to achieve a cascading update behavior? Or am I forced to fall back to using triggers?

Maybe if I model the database differently. The model in question is an inventory transfer between stores. It notes the receiving store id and the sending store id, which creates a race condition (in the eyes of the RDBMS, I made sure that doesn't actually happen with a check).

Any input on this?


texts: CALCULATE TOTAL NUMBER OF ESTIMATED HOURS

texts:
I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a user defined function maybe? But how?

Question: Calculate and return the total number of estimated hours involved in a given stage at the date of the estimate (i.e. before any further changes to estimated hours were made).

(Screenshot) - Table data from: CHARGE_GRADE, TASK_TYPE, TASK, CHARGE_GRADE, ASSIGNMENT, EMPLOYEE.


texts: select data from related tables and display their counts in the relating table

texts:
I have a table which stores data about an inspection. Inspections are carried out in a particular company belonging to an industry, located in a region. the inspections table has the industry_id field referencing the industry table and the region_id field referencing the region table.

Both industry and region tables have id and name fields.

running this query:

SELECT i.name industry, r.name region, COUNT(*)
FROM labour_inspections l
left join company_industries i on l.industry_id=i.id
left join regions r on l.region_id=r.id
where i.name != 'NULL' and r.name != 'NULL'
GROUP BY i.name and r.name

gives me the results i need but not in the way i want it for this view.

I achieved that by writing separate queries and putting the results together with PHP. It is not elegant and I am concerned about performance.

Is there a way I can achieve the view in the image with a single query? Thank you all in advance


texts:
SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance
FROM `tablebusiness` AS TB
JOIN tableauxiliary AS TA ON TA.BusinessID = TB.ID
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.23178308133 106.726216919,-6.13821691867 106.819783081)' ) , TA.Latlong )
AND MATCH (
TA.FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)
AND TA.Prominent >15
ORDER BY Distance

This query takes 12 seconds.

If I do explain

I saw this easy explanation

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  TA  fulltext    PRIMARY,Prominent,LatLong,LatLong_2,FullTextSearch  FullTextSearch  0       1   Using where; Using temporary; Using filesort
1   SIMPLE  TB  eq_ref  PRIMARY     PRIMARY     752     gensurv_isikota.TA.BusinessID   1

All has 1 row. I wonder what the hell does that mean?


texts: Restoring differential backup from SQL Server 2005 to SQL Server 2012

texts:
We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.

Please note I have done this many times, the full backup is restored on the new server with NORECOVERY and then when I go to restore the diff backup I get the error about the broken LSN chain.

I made a test database, got the same result. I then took the test database files and moved them to a server running SQL Server 2008 R2, the backup restored correctly.

Does anyone know why the differential backups from SQL Server 2005 will not restore properly on SQL Server 2012? And how can I fix this?


texts: Will a row update check all child tables which have foreign key relationships in InnoDB?

texts:
I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table.

Foreign key relations are On-Delete Cascade and On-Update No-Action.

My doubt is when any update happens to the master table, will all child tables get checked and then the update occurs, or update will independently happen on master table.

I want to know how update/delete will happen when there are foreign key relations to child tables.

MySQL version is 5.0.24
OS is Windows

I wanted to know, when any update happens, will the update query cross verify all child tables and then update the master table, so that will the update query will take time to update master table compared to update on normal table which is not having any foreign key relations


texts: What does it mean to have many rows for a SPID in sp_who2

texts: I run sp_who2 active on box box and get multiple rows per single SPID.  For example, see below.  Does this mean that SQL Server has broken the query into 23 parallel sub-queries?  If that's the case, why is it ignoring MaxDegreeOfParallelism setting of 8?  Or is this something else?


texts: Max connections sql server express could have?

texts:
Possible Duplicate:
  Max connections SQL Server Express could have?

I have sql server 2005 express edition installed on one of my client's machine, and lots of other users are also connected to that sql server through the c# application that I have provided them as an interface.

Sometimes a pc get connected to sql server and sometimes just throw the exception "SQl server not found....".

I believe express edition reach maximum connection limit of it and then gives error.

Please guide me how could I solve this issue.

Any white paper that help me know which edition have how many number of parallel connections,

I am open to upgrade to sql 2008 or sql 2012, but prefer express edition. I could go for other versions as well if they really pay me back in this context, otherwise express edition is doing well.


texts: Most efficient way to cancel or avoid select in union query?

texts:
I have a query with the following structure. In some specific cases the query will return data only from 1 or 2 of the tables.

For example:

DECLARE @Var1 int --Parameter in store procedure

SELECT several fields FROM T1 WHERE @Var IN(0,1) AND Conditions
 UNION
SELECT several fields FROM T2 WHERE @Var IN(0,1,2) AND Conditions
 UNION
SELECT several fields FROM T3 WHERE @Var IN(0,2) Conditions

My question is regarding performance. What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

IF @Var = 0
    SELECT several fields FROM T1 WHERE Conditions
     UNION
    SELECT several fields FROM T2 WHERE Conditions
     UNION
    SELECT several fields FROM T3 WHERE Conditions
ELSE
 BEGIN
  IF @Var = 1
     SELECT several fields FROM T1 WHERE Conditions
       UNION
     SELECT several fields FROM T2 WHERE Conditions
  ELSE
     SELECT several fields FROM T2 WHERE Conditions
       UNION
     SELECT several fields FROM T3 WHERE Conditions
 END

I'd rather not to use the IF option because the query is quite big with several Fields and Conditions, however, I did check the execution plan of the first query and it shows an Index Scan.

I will appreciate any suggestion or comment


texts:
Multiple nodes within a cluster. Using HyperV as the virtualisation layer. VMs use Fibre Channel SAN for storage but trying to explore the possibility of using Local SSDs for the TempDB. Issue is when the VM migrates.

I know TempDB doesn't need to persist after migration as the DB will just recreate it, but, when it migrates, the VHD will no longer exist. I would imagine the DB would fail if it can't find the directory to create the TempDB?

Is it possible to create a VHDX per node, which sits on the exact same directory (For example, it is always the 'T' Drive), so that every VM will simultaneously write to it regardless of which node they are on? I don't want a VM on Node 2 trying to write to a local drive on Node 1. I want to keep things local at all times.


texts: Data Model for a popular/basic type of social app

texts:
I am trying to learn about Google Datastore and creating a basic application to have some practical experience with it opposed to just reading the docs. I would like to create a basic social application where users can send messages to their friends and view the messages their friends have sent them.

My Basic App Requirements:

Users Section:

A user registers to the app. The user can then log in by using their
username/email and checking if the password is correct and the status
must be VALID and not something like DEACTIVATED.
A user can also be deleted. When a user is deleted If a user deletes
their account their references from all their friends need to be
removed and messages needs to be removed (or hidden). This is
obviously expensive.

Users Model

-username (need to be able to make equality searches on this field. e.g select * from kind where username = 'Bob') (index)
-email    (need to be able to make equality searches on this field. e.g select * from kind where email = 'Bob@gmail.com') (index)
-fullname
-password
-status   (need to be able to make equality searches on this field. e.g select * from kind where status = 'DEACTIVATED') (index)
-time_created

Friends Section:

A user can query to find all of their friends (Accepted and Pending).
A user can add another user to be friends (make sure relationship
doesn't already exist and make sure user exists).
A user can
accept/reject a friend request.
A user may have a max of 100 friends.

Friends Model

-User1  (index)
-User2  (index)
-status
-time_created

Messages Section:

A user may see all the messages they sent/received to/from confirmed friends
A user can send a message to a confirmed friend.
A user may receive a message to a confirmed friend.
Messages will be paginated in the application so 10 messages at a time come up sorted by the newest coming first.

Messages Model

-from (need to be able to make equality searches on this field. e.g select * from kind where from = 'Bob')
-to (need to be able to make equality searches on this field. e.g select * from kind where to = 'Bob')
-msg
-time_created (need to sort on this so that the newest messages come first)

My plan is to make 3 kinds: Users, Friends, and Messages (each would have the attributes shown above).

Some concerns I have is regarding atomic operations. For users, would it be wiser to create a one way relationship between friends or two-way. If two-way, say the friends request gets accepted or rejected, would the application be atomic? Another question is if a user gets removed, how best to handle this to be inexpensive and keep data consistent.

Is this a good model for this basic/popular type of social application? I am very interested to hear of anyone suggestion how they would implement this basic application and the reasons why.

Cheers!


texts: How to set auto growth in SQL Server 2016?

texts: How can I set the Auto growth values in SQL Server 2016? The usual place in the database Properties -> Files is not available.


texts: Composite field in SQL Server, does it exist?

texts:
I'm designing a table to be used by a Web App, and my framework requires that the primary key be a surrogate key. But I also need an extra field which will be a composite of 2 other fields. In other words:

Id -> Primary key
OrderNumber
OrderDate
UniqueOrderNumber -> a composite of OrderNumber and OrderDate

UniqueOrderNumber should be a string generated by merging OrderNumber and OrderDate. This new field will then by used in certain queries that require a unique OrderNumber.

I wanted to avoid handling this logic in the application as I feel this should be a job for the databate. Is there a way to have the database generate this UniqueOrderNumber field automatically by concatenating the values of OrderNumber and OrderDate?


texts:
I have five tables that are connected with each other using joins:

department vs course -- one to many
course vs classes -- one to many
classes vs section -- many to many

I want to retrieve the sections the belong to a particular course. I have tried inner join to achieve this but I can not get good results, I get all the sections instead.

select sectionname from Section
inner join Classes on classid=sectionid
inner join Course on courseid=Classes.fk_course_courseid;

Data contained in the table as shown below. I want to retrieve all the sections the belong to courseid = 1 or department id = 1.


texts: Are Database Master Key protected with same password, the same?

texts:
As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Also, I am wondering, as it said that

  When it is created, the master key is encrypted by using the AES_256
  algorithm and a user-supplied password.

Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?


texts: Recursive Query in MySQL using stored proceedure and CURSOR

texts:
I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members.  I have three tables users, groups, and relationships.

For simplicity sake, lets say users only contains one field user_id, and groups only contains group_id.  relationships uses three fields: user_id, group_id, related_group_id to represent both user to group and group to group relationships.

A dynamically populated group hierarchy would be something like:

Users A, B, C belong to Group 1 and Users D, E, F belong to Group 2. All of these are represented in the relationships table as (user_id,group_id):

A,1
B,1
C,1
D,2
E,2
F,2

The dynamic Group 3 would have two group to group relationship records (group_id,related_group_id):

3,1
3,2

To illustrate my problem better lets add a Dynamic Group 4 and have it include Group 3:

4,3

Piecing together what I've learned of MySQL CURSOR's, I've created the following stored procedure called fetch_inheritance_groups:

DROP PROCEDURE IF EXISTS `fetch_inheritance_groups`;

CREATE PROCEDURE `fetch_inheritance_groups`(IN parent_id INT)
    READS SQL DATA
    BEGIN

    DECLARE inherit_id char(32);
    DECLARE eol BOOLEAN;
    DECLARE inherit_cur CURSOR FOR SELECT r.Related_Group_ID FROM usr_relationships r WHERE r.Group_ID = parent_id AND r.Related_Group_ID IS NOT NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET eol = TRUE;

    OPEN inherit_cur;
    inherited: LOOP
        FETCH inherit_cur INTO inherit_id;

        IF eol
            THEN
            CLOSE inherit_cur;
            LEAVE inherited;
        ELSE
            CALL fetch_inheritance_groups(inherit_id);
        END IF;

        SELECT inherit_id;

    END LOOP inherited;

END;

...which is called with...

CALL fetch_inheritance_groups(4);

...and returns the expected results of

3
2
1

Herein begins my problem, they're returned as three separate result sets rather than three rows in a single result set, and secondly, the results are meant to be used within a query like:

SELECT r.uer_id FROM relationships r WHERE r.group_id = 4 OR r.group_id IN (CALL fetch_inheritance_groups(4));

So the three questions I'm hoping someone can answer are:

1. Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

2. How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?

3. What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work?  I believe this is because I can't use CALL inline but I'm not sure.

UPDATE: In a related question here: How long will a temporary MEMORY table persist if I don't drop it (MySQL) I posted a set of updates to the stored proceedure used above which is still recursive, but provides the list of ID's back as a variable, and in a format compatible with FIND_IN_SET() giving me EXACTLY what I needed.


texts: How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?

texts:
How do you implement conversation groups for multiple users using their own instance of the user applications but sending related messages to the service broker queue?  Any good example of this kind of implementation?  I had imagined that I could set the conversation group ID myself, guaranteeing that certain groups of messages from various users will be related, but it seems that this value is going to be automatically generated as a UNIQUEIDENTIFIERS... Is this even the best way to do it?

I would like to make sure to catch and lock related messages where two users "on the same team" for instance are bidding on the same item together, so that the server application logic can properly process those related messages together (basically only the first one will be accepted and the second one will be rejected until the user's view can be updated so they know that the other user changed the bid already).

flow direction is from users A, B, C sending xml messages to the queue, then a dequeuing service which sends the xml to an external web service.  The messages we are sending are structured xml messages which will modify orders on different items on the external web service after dequeuing.

Thank you!


texts:
After working on my database a bit, I am working on a VB.Net app that accesses it using LINQ.  I have the following stored procedure in the database:

CREATE PROCEDURE dbo.GetTableColumn(
    @ColName VARCHAR(MAX),
    @TblName VARCHAR(MAX),
    @Result  BIT OUT
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        BEGIN
            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName
            EXEC (@SQL)
            SET @Result = 1
        END
    ELSE
        SET @RESULT = 0
    RETURN @Result
END

I have added it to my DataLINQContext and call it in the following method in my VB.Net code:

Public Function GetTableColumn(ByVal col As String, ByVal table As String) As AutoCompleteStringCollection
    GetTableColumn = New AutoCompleteStringCollection
    Using dbContext As New Customer_LINQDataContext
        Dim result As Boolean
        Dim query = dbContext.GetTableColumn(col, table, result)
        MessageBox.Show(query.ToString())
    End Using
End Function

Now what I am trying to accomplish, is that the query object receives the data from the query performed by the procedure.  This does not happen.  Instead I only get a 1 or 0 result, based off whether or not the column exists within the table.

So obviously something is wrong somewhere.  I feel it's a mess up on the part of my procedure, but I am not sure how to code it to accomplish what I want it to do.


texts: How do I develop this data model without a circular reference?

texts:
I am trying to develop a concept for a new call tracking system for a help desk.

The top 3 levels reference each other.  I am trying to determine a better way to link these tables, and am drawing a blank.

The three levels are Call, Issue and ActionTaken.  Each call can have multiple issues and each issue can have multiple actions taken.  When looking up the call afterwards it needs to only show the actions taken on that call.

For example:

Call - 1, Issue 1 - Outlook not working, ActionTaken - Reset password
          Issue 2 - Ipad keeps crashing, ActionTaken - Restored ipad
Call - 2, Issue 1 - Outlook not working, ActionTaken - Outlook profile reset

We need to be able to look up call 1 and see the two issues and the actions taken during that call, but we also need to be able to look up issue 1 and see the two calls and the corresponding actions taken.

How would I accomplish this?


texts: I have a MySQL table that stores a list of bookings that's made online. I need to have these bookings dumped to a CSV file which will then be picked up by a client server application through a SSH connection. Earlier we were dumping the entire table and so the CSV file had the transactions since beginning of time. Now we have to generate the dump several times in a day and the dump should contain only the newer records. Does my MySQL provide any options to generate a dump that contains only those records that were inserted after the last dump was taken?


texts: SSIS: What services to restart so Folder Permissions Propogate to username executing pacakge?

texts:
I created an SSIS package that needs access to a folder on a shared drive.

The package runs successfully on my local machine but I get an access denied error when I run it on a different server using a different username.

I created a ticket for IT to give access permissions to the user name that is executing the package but the package was still failing with same access to the path is denied error.

I was told by IT that I would need to restart the services that the (windows) username was using so that the changes would propagate.

My questions is, what services would I need to restart so that the username will have the new access rights propagated to it so that my package will run (with access to the folder)?

Do I need to restart the server that the package resides on? Does the server that contains the shared folder need to be restarted? Does some SQL Server service need to be restarted? What is the easiest way to have the permissions propagate to the username?


texts:
Im not a DBA however am an infrastructure guy used to installing and configuring database servers such as MSSQL, MySQL, MongoDB etc...

I have a MySQL database which is being used for archival of building systems data. Once a night, the building management system will archive its data which results in around 500k rows of data being added across various tables in the database.

There are 12,000 tables in this database and overall it contains 7 billion records.

Are there any issues with growth? The database is currently 150GB and this is a purely write only load. There will hardly ever be any read activity on this DB.

I understand IOPS and performance scaling, this is a question about MySQL itself and specifically its ability dealing with a DB of this size.

Is there anything I should be aware of?

EDIT: I am fully aware that this is not ideal, hence this post but I am totally 100% unable to change the way the third party stores its info in the DB so don't have much of a choice but ask how I can best 'optimise' SQL to work with what its given.

Thanks


texts: We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear for me how can I use that.


texts: Bash Script Can't Connect to Azure Postgres using ENV Vars - "The Username should be in  format"

texts:
I have a bash script that exports ENV vars to use in connecting to an Azure Postgres DB (mydb.postgres.database.azure.com). For the user portion of that specifically it looks like this:
export PGUSER="postgres@mydb"

This works fine to connect to my local DB where the @mydb part isn't required, but it fails when connecting to Azure (where it is required) with:

psql: FATAL:  Invalid Username specified. Please check the Username and retry connection. The Username should be in username@hostname format.

I've found lots of solutions/answers to this like this one and this one however those aren't viable solutions when using ENV vars to set connection details (and while I didn't expect it to work I nonetheless tried changing my script to export PGUSER="postgres%40mydb" and indeed it did yield the same error).
Does anyone know how to accomplish this?


texts: Is there a performance difference in committing and rolling back a read-only transaction?

texts:
I open a (repeatable read) transaction (BEGIN TRAN) to do some work on certain records. First thing I do is checking if the data I need to change is in the database. In some cases there will be and then I proceed to my changes. But in some cases there will be nothing to do. In this case I either COMMIT TRAN or ROLLBACK TRAN and return from the stored procedure. At this time no changes were done to the data yet, so the effect of commit and rollback is the same.

Are there any consideration I should be aware about to chose between commit and rollback? Is there different performance cost? Other considerations?


texts: Creating Synonym For a Table in PostgreSQL over dblink?

texts:
I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table in the second database. It will be good to mention that both databases has the same structure.

Is it possible to create a synonym or is there any alternative way to do so?


texts: Unique Constraints on Nullable Columns in SQL Server 2005

texts:
In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered index as shown below but this is not available in earlier versions of SQL!

CREATE UNIQUE NONCLUSTERED INDEX User_UserName_IUC
ON [User] (pinNr)
WHERE UserName IS NOT NULL

But I don't think this is available in SQL Server 2005. In fact this blog post indicates there is a workaround using a trigger to check for uniqueness. Does anyone have an example of this? or maybe an alternative?

Unfortunately upgrading to SQl Server 2008 is not an option for this particular client!!


texts: Postgres- comparing two arrays in the where clause optimization

texts:
I am comparing an array inside my table with an array that has the maching IDs. this query performs slowly as soon as the number of items in the passed in array gets larger. I was wondering if there are any further optimizations that I can do.

CREATE OR REPLACE FUNCTION "DataOrdering"."GetHierarchyProductsCount"(
    categoryhierarchyids integer[])
    RETURNS TABLE("TotalCount" integer)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
    ROWS 1000

AS $BODY$
    BEGIN
    RETURN QUERY
        SELECT Count(*)::int as TotalCount FROM "DataOrdering"."ProductDescMapping" PDM
        WHERE PDM."CategoryHierarchyIds" && categoryhierarchyids;
$BODY$;

ALTER FUNCTION "DataOrdering"."GetHierarchyProductsCount"(integer[])
    OWNER TO postgres;

table shcema:

CREATE TABLE "DataOrdering"."ProductDescMapping"
(
    "ProductDesc" text COLLATE pg_catalog."default" NOT NULL,
    "ProductCode" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "ProductDescMappingId" integer NOT NULL DEFAULT nextval('"DataOrdering"."ProductDescMapping_ProductDescMappingId_seq"'::regclass),
    "CategoryHierarchyIds" integer[] NOT NULL DEFAULT '{}'::integer[],
    CONSTRAINT pk_productdescmappingid PRIMARY KEY ("ProductDescMappingId"),
    CONSTRAINT uc_productdescmapping UNIQUE ("ProductCode")
)

TABLESPACE pg_default;

ALTER TABLE "DataOrdering"."ProductDescMapping"
    OWNER to postgres;
-- Index: ix_productcategoryhierarchy

-- DROP INDEX "DataOrdering".ix_productcategoryhierarchy;

CREATE INDEX ix_productcategoryhierarchy
    ON "DataOrdering"."ProductDescMapping" USING btree
    ("CategoryHierarchyIds" ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: ix_productdescmapping

-- DROP INDEX "DataOrdering".ix_productdescmapping;

CREATE INDEX ix_productdescmapping
    ON "DataOrdering"."ProductDescMapping" USING btree
    ("ProductCode" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;


texts:
When PostgreSQL throws an exception, there is a line "CONTEXT" like:

    ERROR:  INSERT has more target COLUMNS than expressions
    LINE 3: ...
                                                         ^
    QUERY:  INSERT INTO ...
    CONTEXT:  PL/pgSQL FUNCTION "XXXXX" line 4 at SQL statement

But when I throw an exception, this line is not there. I didn't find how to add it.

    RAISE EXCEPTION 'blablabla' USING HINT = 'blablablabla';

Is it possible to add this line to my exception?


texts:
I've been trying to find an option similar to MySQL Workbench 'Reverse engineer' for MSSQL Server 2012. I want to generate the SQL script from a diagram I've created. Is such option available for SQL Server? Or would I have to use a third party solution for this?

I tried googling for a solution but I couldn't find anything related.

Thank you.


texts: Primary key within JSON data in Postgresql

texts:
I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key:

> SELECT json->'id' AS id FROM t LIMIT 1;
             id
-----------------------------
 " 63631ff3809de7a17398602f"

I can create a UNIQUE INDEX on id, thus:

> CREATE UNIQUE INDEX t_id ON t((json->>'id'));

CREATE INDEX

I'd like to add this as a table_constraint_using_index, but it fails for both PRIMARY KEY:

> ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

and UNIQUE:

> ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Should I be able to add such a constraint?


texts: Convert UTC time to local time with specific format in Redshift

texts:
I have a column called created_at with data stored in UTC without timezone like : 2017-02-05 15:43:27.151

I would like to convert to an other timezone like CET in specific format like :

2017-02-08 16:43:27 +01:00

I succeed converting the time to specific timezone but needed to specify the timezone of the input. Query looks like :

SELECT created_at,
       (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'CET' AS cet
FROM my_table;

[
  {
    "created_at" : "2017-02-08 15:43:27.151",
    "cet"        : "2017-02-08 16:43:27.151"
  }
]

But I failed to extract or generate from timezone code the timezone offset.

I tried

SELECT EXTRACT(TIMEZONE_HOUR
               FROM
                 (SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET'));
[
  {
    "date_part" : 0
  }
]

How to get the 2017-02-08 16:43:27 +01:00 ?

EDIT 1:

SHOW timezone; --utc
SELECT current_timestamp; -- 2017-02-09 11:00:20.225039+00
SELECT to_char(current_timestamp AT TIME ZONE 'CET', 'YYYY-MM-DD HH24:MI:SS OF'); -- 2017-02-09 12:00:35 +00

Why the offset is 00? (I'm using OF because I'm on Redshift)?

  OF |  Offset from UTC; valid for TIMESTAMPTZ only

From doc

EDIT 2: I thought AT TIME ZONE 'CET' turn the the date to timestamptz but I was wrong. You need to cast the date as ::timestamptz then OF will display the UTC offset.

SELECT to_char((current_timestamp AT TIME ZONE 'CET')::timestamptz, 'YYYY-MM-DD HH24:MI:SS OF');
--2017-02-09 14:48:48 +01

Now I need to add the :00.


texts: How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

texts:
Consider this design for a typical school database:

Person:
-----------------
FirstName
LastName
SocialSecurityNumber
Phone
Email

Student:
-----------------
Grade

Teacher:
-----------------
Specialty

As you see, there are three tables in this design. One table holds the general information about the abstraction, and two other tables hold specific data about concrete entities. Students and Teachers tables have one-to-one relationship with Persons table. So far there is no problem.

However, based on this design, there is this possibility that we have a record with Id 30 in Persons table, and because of any reason (like manual script execution against database) we insert two records with the same Id in Students, and Teachers tables. This way, a person with Id 30 is both a teacher, and a student at the same time.

Well, that makes sense in the context of a school database. But there are some contexts that derived tables are mutually exclusive, thus an entity from one concrete type can not logically be an entity from the opposite type too.

How can I prevent overlap Id insertion across derived tables in hierarchical database designs? I know I can achieve that with triggers, but I think code smell in using triggers.

Notes: I'm using SQL Server, and Entity Framework calls this design Table per Type (TPT).


texts: Failed to restored indexes using mongorestore - MongoDB

texts:
Note: I already know about --noIndexRestore

I have an org collection with the following document structure,

{
    "_id" : ObjectId("5cc2bf9f5ca45131b8f8e427"),
    "name" : "National",
    "parent" : "null",
    "level" : 1,
    "label" : "National",
    "identifier" : "Default",
    "client_id" : ObjectId("5af3178c141292374cbf363e")
}

I have created the indexes on the following fields,

name
parent
level
identifier
client_id

When I check indexes,

db.org.getIndexes();

Result:

/* 1 */
{
    "v" : 2,
    "key" : {
        "_id" : 1
    },
    "name" : "_id_",
    "ns" : "testdbname.org"
},

/* 2 */
{
    "v" : 2,
    "key" : {
        "name" : 1
    },
    "name" : "name_1",
    "ns" : "testdbname.org"
},

/* 3 */
{
    "v" : 2,
    "key" : {
        "parent" : 1
    },
    "name" : "parent_1",
    "ns" : "testdbname.org"
},

/* 4 */
{
    "v" : 2,
    "key" : {
        "level" : 1
    },
    "name" : "level_1",
    "ns" : "testdbname.org"
},

/* 5 */
{
    "v" : 2,
    "key" : {
        "identifier" : 1
    },
    "name" : "identifier_1",
    "ns" : "testdbname.org"
},

/* 6 */
{
    "v" : 2,
    "key" : {
        "client_id" : 1
    },
    "name" : "client_id_1",
    "ns" : "testdbname.org"
}

All of the above indexes looks fine to me. But when I take backup on server and restore it somewhere else it gives me the following error while restoring the org collection.

reading metadata for testdbname.org from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:40.979+0500    restoring testdbname.org from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:41.226+0500    reading metadata for testdbname.questionbank from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:41.237+0500    restoring indexes for collection testdbname.org from metadata
2019-10-05T10:20:41.240+0500    Failed: testdbname.org: error creating indexes for testdbname.org: createIndex error: Values in v:2 index key pattern cannot be of type object. Only numbers > 0, numbers < 0, and strings are allowed.


texts: How can one fetch subtotal in relation to a group by from an outer query?

texts:
I have a simple query,  where I would like to display for past 7 days, grouped by hour from a table apiRequest: The count of request having a transaction vs the count of those having no transaction.

I tried two different approaches but both failed. The first column displays fine, it is the second one that is problematic:

+--------------+-------------+--------------------+-----------------+
| Date         | Time        | Total Requests     | no trans.       |
+--------------+-------------+--------------------+-----------------+
| January 20th | 2:00-3:00   |                 44 |               0 |
| January 20th | 3:00-4:00   |                 32 |               0 |
| January 20th | 5:00-6:00   |                 51 |               0 |
| January 20th | 6:00-7:00   |                 18 |               0 |

First approach was with a subquery, that nearly worked but I could not get the WHERE accountDetails NOT LIKE "%Transactions\":[{%" to work.

Second query was with an inner join, but could not manage to get the group by to work as expected for second column.

--  Two column table that displays number of requests for past seven days, grouped by hour of the day
-- First try, with subquery - the second column does not show result

--  Col 1 - Shows count of requests with transactions,
--  Col 2 - count of requests without transactions
--  for every hour of the day in past seven days
SELECT DATE_FORMAT(a.created, "%M %D") Date,
       CONCAT(HOUR(a.created), ':00-', HOUR(a.created)+1, ':00') Time,
       count(a.`RequestId`) as "Total Requests",
         (select count(a2.`RequestId`) FROM apiRequest a2 WHERE DATE_FORMAT(f.created,"%Y %M %D %H") = DATE_FORMAT(a2.created, "%M %D %H")) "IBV's no trans."
        FROM apiRequest a
   WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL
GROUP BY DATE_FORMAT(a.created, "%M %D %H");

-- Second try, with inner join - group by does not work
SELECT DATE_FORMAT(a.created, "%M %D"),
       CONCAT(HOUR(a.created), ':00-', HOUR(f.created)+1, ':00') AS Hours,
       count(a.`RequestId`) as "Total requests",
       f2.ibvsNoTrans
        FROM apiRequest a
        INNER JOIN (SELECT COUNT(RequestId) ApiNoTrans FROM ApiRequest WHERE accountDetails NOT LIKE "%Transactions\":[{%" GROUP BY date_format(apiRequest.created, "%Y %M")) f2
           WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL
GROUP BY YEAR(a.created), MONTH(a.created), DAY(a.created), HOUR(a.created), Hours;

I am quite certain I am close to the goal but missing a detail.
I feel this should be simpler, what is the proper approach, and is there a more optimal manner to match on dates / hours than what I have?


texts: Vacuuming database termination for postgreSQL

texts:
I was doing database vacuuming to free up disk space for postgreSQL database. The corresponding table size was large and it was taking long time for vacuuming to get complete. But unfortunately because of slow network connection , the network connection got terminated which resulted vacuuming getting terminated in middle.

I am worried whether any database corruption will occur. Can I once again issue database vacuum command?

vacuum full message_log;

Please clarify.


texts: Trying to connect two SQL Server databases

texts:
I have been trying to connect to a SQL Server instance. When I use the IP address, it connects. But when I use the server name, it doesn't. The main reason why I need to use the name is because I want to setup replication.
This is the error I am getting:

Any ideas on what I could be missing?
I am in a Windows environment and I used the machine IP. Firewall is opened for port 1433 TCP.


texts:
I have DDL like the below, which returns data like such:

GP17    GP16
0       103
90      0
375     0

I am wanting to pivot the data somehow so that it returns only one row, like such:

Jan 17 ---- Jan 16 ---- Feb 17 ---- Feb 16 ---- Mar 17 ---- Mar 16
0            103         90          0           375         0

Now this is relevant to the date range being passed in below, for example, if the date range being passed in is 20170401  20170630 then I would want the dates to read Apr 17, Apr 16, May 17, May 16, Jun 17, Jun 16

How can this be achieved with SQL Server 2008 R2 and the DDL below?

Declare @Test Table
(
    dname varchar(100)
    ,tr float
    ,dc float
    ,sdate date
)

Insert Into @Test (dname, tr, dc, sdate) Values
('Green', 100.00, 10.00, '20170101'), ('Green', 200.00, 15.00, '20170201'), ('Green', 200.00, 10.00, '20170215')
,('Green', 10.00, 0.00, '20160101'), ('Green', 15.00, 10.00, '20160130'), ('Green', 110.00, 22.00, '20160101')

Declare @startdate date = '20170101', @enddate date = '20170331'

SELECT
ROUND(SUM(ISNULL(tr17,0))-SUM(ISNULL(DC17,0)),0) As GP17,
ROUND(SUM(ISNULL(tr16,0))-SUM(ISNULL(DC16,0)),0) As GP16
FROM
(
SELECT
ZT1.dname,
SUM(ISNULL(ZT1.tr,0)) AS tr17,
0 As DC17,
0 As DC16,
0 AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= @startdate
AND ZT1.sdate <= @enddate
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
SUM(ISNULL(t1.dc,0)) As DC17,
0 As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= @startdate
AND t1.sdate <= @enddate
Group By t1.dname, t1.sdate
UNION ALL
SELECT
ZT1.dname,
0 As tr17,
0 As DC17,
0 As DC16,
SUM(ISNULL(ZT1.tr,0)) AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= DateAdd(yy, -1, @startdate)
AND ZT1.sdate <= DateAdd(yy, -1, @enddate)
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
0 As DC17,
SUM(ISNULL(t1.dc,0)) As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= DateAdd(yy, -1, @startdate)
AND t1.sdate <= DateAdd(yy, -1, @enddate)
Group By t1.dname, t1.sdate
) AS A
GROUP BY YEAR(activityDate), MONTH(activityDate)


texts:
I try to create table GroupArticle in database CmsGroup, one column ArticleId references another table in another database? is this possible and correct syntax?

CmsArticle

CREATE DATABASE "CmsArticle"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("ArticleId")
);

CmsGroup

CREATE DATABASE "CmsGroup"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Group"(
"GroupId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("GroupId")
);

CREATE TABLE IF NOT EXISTS "GroupArticle"(
"GroupArticleId" SERIAL NOT NULL,
"GroupId" integer NOT NULL,
"ArticleId" integer NOT NULL,
FOREIGN KEY ("GroupId") REFERENCES "Group" ("GroupId") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("ArticleId") REFERENCES "CmsArticle.Article" ("ArticleId") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY ("GroupArticleId")
);


texts: How can I test a constant boolean expression?

texts:
Suppose I have a simple boolean expression involving only constants, like

4 + 5 = 9

Maybe (for debugging purposes - maybe the expression is a complicated LIKE expression) I want to just check to see if this expression is true or false. Is there a simple trick for doing this?

Currently I'm just using

SELECT * FROM my_schema.my_table WHERE 4 + 5 = 9;

and seeing if I get anything back. I thought SELECT 1 WHERE expression might work, but no luck (MySQL 5.6.10). To be more precise about what I mean by "simple trick", let's say we want a command which will run without modification on any server. My current command doesn't fit the bill since we need to know the name of a schema and table in the server.


texts: How to prevent deadlocks in many-to-many insert/update trigger function?

texts:
I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point.

I have a tweet table that receives thousands of records per second. One of the columns is a PostgreSQL array[]::text[] type with zero-to-many urls in the array. It looks like {www.blah.com, www.blah2.com}.

What I'm tryin to accomplish from a trigger on the tweet table is to create an entry in a urls_starting table and then adding the tweet/url_starting relationship in a tweet_x_url_starting.

Side note: The url_starting table is linked to a url_ending table where the fully resolved url paths reside.

The problem I face is deadlocks and I don't know what else to try.
I went on an Erwin Brandstetter learning spree. (if you're out there man... THANK YOU! )

How to implement a many-to-many relationship in PostgreSQL?
Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
Postgres UPDATE … LIMIT 1 (skip locked help)

I tried adding ORDER BY's for deterministic, stable orders and FOR UPDATE SKIP LOCKED but am not sure I'm doing any of it correctly.

Here's the structure. Using PostgreSQL 10.5.

CREATE TABLE tweet(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    twitter_id text NOT NULL,
    created_at timestamp NOT NULL,
    content text NOT NULL,
    urls text[],
    CONSTRAINT tweet_pk PRIMARY KEY (id)
);

CREATE TABLE url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

CREATE TABLE tweet_x_url_starting(
    id_tweet integer NOT NULL,
    id_url_starting integer NOT NULL,
    CONSTRAINT tweet_x_url_starting_pk PRIMARY KEY (id_tweet,id_url_starting)

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT tweet_fk FOREIGN KEY (id_tweet)
REFERENCES tweet (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT url_starting_fk FOREIGN KEY (id_url_starting)
REFERENCES url_starting (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

Here's the tweet table trigger.

CREATE TRIGGER create_tweet_relationships
    AFTER INSERT OR UPDATE
    ON tweet
    FOR EACH ROW
    EXECUTE PROCEDURE create_tweet_relationships();

And finally, the function.

CREATE FUNCTION create_tweet_relationships ()
    RETURNS trigger
    LANGUAGE plpgsql
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 1
    AS $$
BEGIN
    IF (NEW.urls IS NOT NULL) AND cardinality(NEW.urls::TEXT[]) > 0 THEN
        WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
            ORDER BY 1
          ON CONFLICT (url) DO UPDATE
            SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
            ORDER BY 1, 2
            FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING;
    END IF;

    RETURN NULL;
END
$$;

I blindly threw the stuff I read about into the function without success.

The error looks like this.

deadlock detected
DETAIL:  Process 11281 waits for ShareLock on transaction 1317; blocked by process 11278.
Process 11278 waits for ShareLock on transaction 1316; blocked by process 11281.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (494,33) in relation "url_starting"
SQL statement "WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
      ORDER BY 1
          ON CONFLICT (url) DO UPDATE
              SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
    ORDER BY 1, 2
        FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

How can I stop the deadlocks? Thanks!


texts: ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND on

texts:
I am getting an error:

ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ

But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?

Thanks.


texts: Full-text search on column for existence of a keyword from a table

texts:
I have a table of keywords and I want to query another table and return rows where a column contains at least one keyword.

Is it possible query like that using full text indexes?

If not is there a more efficient way than the one below - dynamic SQL perhaps?

SELECT
    *

FROM
    dbo.Notes AS NotesContainingKeyword

WHERE
    EXISTS
    (
        SELECT
            *

        FROM
            dbo.Notes
            CROSS JOIN dbo.Keywords

        WHERE
            Notes.Note LIKE '%' + Keywords.Word '%'
            AND Notes.Id = NotesContainingKeyword.Id
    )


texts: Set boolean field to true if query match otherwise set to false

texts:
I have bunch of ids someIds (20 - 100 thousands) and table with more than 12 millions rows like that:

spaceShips(
    id BIGINT,
    shipType TEXT,
    shipName TEXT,
    hasArtificialIntelligence boolean
)

And I need for all rows where shipType='warship' (about 2 millions) set field hasArtificialIntelligence true if spaceShips.id in someIds otherwise set false. Is there a better way than two update queries?


texts: postgres user cannot access location of new data directory

texts: I moved my postgresql 9.3 data directory to a different drive, however, my postgres user cannot seem to access that new drive despite my best efforts. I am running on ubuntu 14.04 LTS x64. So I created a new directory under /media/$USER/kauthama/data/psql and set postgres:postgres as the owner. However when I try and do initdb the directory keeps saying permission denied. Even when I do su postgres and then try to navidate to the new directory, the autocompletion stops at /media/$USER/ and cannot find the drive kauthama. I even added postgres to the sudoers group to see if that would work. The permissions on kauthama are basically $USER is the owner and the sudo group owns it. So the postgres user should have all the permissions needed to navigate to the directory. Any thought on why this is not working?


texts: Calculating daily growth using previous row value in new column - SQL Server

texts:
I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in here

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried this using the "LAG" function but did not succeed. I tried it like this:

LastDayGrowth = DATEDIFF(DAY,
                         LAG(( CONVERT(DEC(15, 2), a.dbsize)
                               + CONVERT(DEC(15, 2), a.logsize) )
                             * 8192 / 1048576.0, 1, NULL) OVER ( ORDER BY a.database_name, ( ( CONVERT(DEC(15,
                                                      2), a.dbsize)
                                                      + CONVERT(DEC(15,
                                                      2), a.logsize) )
                                                      * 8192
                                                      / 1048576.0 ) ),
                         ( ( CONVERT(DEC(15, 2), a.dbsize)
                             + CONVERT(DEC(15, 2), a.logsize) ) * 8192
                           / 1048576.0 ))

Any tips, or advice would be greatly appreciated!


texts: SQL Server effectiveness of mirroring partial database

texts:
Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only about 100 GB can be spared at most).

So, I was wondering if it's possible to mirror partial portion of the database.

Distinguish active data, pull them into separate database (maybe at most 20 GB worth of active data)
Setup database mirroring for new database

Questions:

If partial database is mirrored, should I be worried about performance issue?
Are there other known solutions to prepare for DR situations?

It may be hard to provide best answer without knowing the detailed specs of servers, and configurations but I'd like to know how other servers are handled with limited resources.

Thank you!


texts: How to connect once for all queries in SQL Server Management Studio?

texts:
SSMS asks me to connect for every query in my project. Is there a way to connect only once per connection for all of the queries using it in my project?

The answers below (March 2014) don't answer the question. Still looking for a solution.


texts: How to design a database table in a context where the columns can potentially change with each new row

texts:
Lets consider the following task:  For a NLP task I am collecting news title in a database. I need to use this data for word frequency analysis, so I tokenize the title and store the words in the following way:
titles table:

titleID    word1    word2    word3    ....
1          0        0        1
2          1        1        0
3          0        0        0
...

Every column is a different word, and i mark it 0 or 1 according to whether or not it is present in the title.
The issue is of course that each time i add a new title, there might be a word that is new and therefore I would need to add a new column for this word.
In SQL world that means a potential migration at each insert of a new row, which is not tenable.
I could use nosql but I would still like that table to have relations with other tables in my database.
What would be the alternatives for my case? What is the common way to store NLP data in a database?


texts: How to represent the relationship between artists and albums in an entity-relationship diagram

texts:
I'm learning and practicing with the E-R model trying to design a music industry database.

With this section of the diagram I have a doubt:

I suppose an album can be had by an artist and a band, not necessary by only one of these. How can I represent this relationship?


texts: TSQL success or failure result of EXEC dynamic SQL

texts:
Based on the following code:
/* This SP takes a query and a unique temporary table name as input...
The outputs is a string with the command to create the tmp table based on the query that is given in the @SRC parameter
*/
EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
/* This executed the query that will create the tmp table and insert the data from @SRC */
EXEC(@SQLStr)

The whole stored procedure that calls these 2 lines (among many others) is in a Try Catch.
I've just run into a problem where the query in @SRC had a syntax error which called the Catch portion:
BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ERRORMESSAGE=CASE WHEN @ERRORMESSAGE='' THEN ERROR_MESSAGE() ELSE @ERRORMESSAGE END
        RAISERROR (@ERRORMESSAGE,16,1)
END CATCH

the Try has many validations and I set @ErrorMessage to personalized messages in most cases.
In this case, I would like to set a personalized message instead of getting the standard error message
(example): Incorrect syntax near 'FROM'.

I'm trying to figure out how I can get the EXEC(@SqlStr) return a successful or failure code, so I can set the @ErrorMessage before it goes into catch portion of the code.


texts: Limit connection to specific database with AlwaysOn Primary

texts:
Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that they could access the database on the secondary but not the primary (ignore failover scenarios please, not the issue).
The problem now is that I need to grant that same user access to the SQLAgentReader role in msdb so they can see jobs on the primary.  That itself is not a problem, but I will need to enable the login on the primary in which case he could then get back into the first database on the primary.
Is there a trigger I could write to tell if he is 1) connecting to that first DB and 2) whether he is on the primary and if both are true then block his connection JUST to that DB?  Maybe there is some better solution (besides the obvious use two logins with different permissions)?  So far all I have found is triggers on login to the server, not at the database level.


texts: Resynchronize transactional replication in SQL Server 2012 without doing a complete reinitialization after disk filled

texts:
We have a DB that is doing a transactional replication to one other server. There are three tables it is replicating in the DB. Yesterday the subscriber ran out of disk space. This caused the replication to fail. I tried to start it back up but the subscription failed with the error message

  Invalid object name 'MSreplication_subscriptions'.

Currently the subscription is sitting in a "running" status, but states

  The initial snapshot for publication <XXXXXX> is not yet available.

Is my only solution to reinitialize the subscription? I'm trying to avoid that if at all possible since the DB is quite large and our pipe isn't that large.


texts: WHERE Clause with pattern matching to find rows with any characters NOT in a list

texts:
I am in a SQL Server 2008 environment.

I am trying to use pattern matching in the WHERE clause to find rows where a certain column's value contains characters that are not alpha-numeric, underscore, dash, period, or space.

This is my code, and sample data, but I am not getting the results expected.

In the example data, I want to return rows 7, 8, 9, and 12, but I am getting rows 5 and 6.

If this isn't the best way to achieve the goal, I am open to hearing other methods.

I am not in an environment where I can implement regex so my solution limited to out-of-the-box functionality.

CREATE TABLE PATTERN_TEST
(
ID INT NOT NULL,
STRING NVARCHAR(40) NOT NULL
)

INSERT INTO PATTERN_TEST
SELECT 1, 'string' UNION
SELECT 2, 'STRING' UNION
SELECT 3, 'string space' UNION
SELECT 4, 'STRING SPACE' UNION
SELECT 5, 'string-dash' UNION
SELECT 6, 'string-dash space' UNION
SELECT 7, 'string "otherchar"' UNION
SELECT 8, 'string "other char"' UNION
SELECT 9, '"string"' UNION
SELECT 10, 'string_underscore' UNION
SELECT 11, 'string_underscore space' UNION
SELECT 12, '"'
;

SELECT * FROM PATTERN_TEST WHERE STRING LIKE '%[^a-zA-Z0-9_ -.]%';


texts:
I've got an older DB (postgres 10.15) that's not yet been upgraded. One problematic table had a few large indexes on it, some of which were corrupt and needed reindexing. Since it's not on version 12+, I can't concurrently reindex the table (which means I need to do it non-concurrently, which requires a table write lock) - so I wanted to know how I could do some rough calculations on how long the reindex would take so I can plan some maintenance. Most of my research ends up in the "just use pg_stat_progress_create_index! (which isn't available in 10), or people just saying to use CONCURRENTLY.
The table is ~200GB, and there are indexes are 7 indexes which are 14GB each (as per pg_relation_size). I can get ~900M/s constant read-rate on the DB for this task. Is there a simple metric I can use to determine how much data will be required to be read to reindex fully?


texts: Find out when your index was last rebuilt / reorganized

texts:
I've recently come across a database, which has very high fragmentation on large tables.

Page count > 1'000'000 with fragmentation 99,...

I would like to know how long this table has gone without a reorganize / rebuild.
That way I can review changes made on that date, to try and find a cause. (Also it'd look nice on my report "this database has not been properly maintained since...)

As I'm in the process of setting up a job to quickly reorganize the affected tables (this night probably), any solution that could offer me a history of index operations would be much appreciated. If not, simply knowing that I can check this data in the future would be very helpful.


texts:
I made an application with PHP and MySQL. I use VPS with CPU 6 cores and 16GB RAM. After a while the application is very heavy and 100% cpu is used mysql and RAM is not used at all by mysql.
Please tell me the settings on my.ini for the best results, Thank you very much


texts:
I have been using SQL Server 2012 on my laptop, but now I want to install SQL Server 2014 trial version as I am following an online course on it.

Can I go ahead with the installation of SQL Server 2014 trial with SQL Server Data Tools installation separately?  Is it advisable to do that or will it create issues and errors if I try to do so?


texts: GRANT REFERENCE on all user defined types

texts:
The original problem is: a user, who has the permission to create/alter view granted is getting this message when trying to perform either operation:

  Cannot find the type 'nvarchar', because it does not exist or you do
  not have permission.

Through some internet digging I found it may be caused because he does not have GRANT REFERENCE permission on types. However,

select * from sys.types order by name

Returns over 2000 records, and GRANT REFERENCE command accepts a single type as parameter. How can I grant this user the reference permission for ALL types?


texts: Getting count and percentage of valid and invalid records, group by day