texts: Cost Threshold for Parallelism with Maxdop = 1

texts:
Been debating with my company's app vendor regarding parallelism. They have maintained that Maxdop = 1 has significant performance improvements for the app/db but they have provided absolutely no proof of this.

I have tested various DOP/CT settings and found a 28% overall performance improvement from automated testing, when using Maxdop 4 (8 hyperthreaded cores) and CT 150.

The vendor's DBA recently said: "There is an issue with internal processes of MSSQL that are able to use this value even if DOP is ‘1’, there are SPID’s lower than 51 in a MSSQL server that is using it, ghost cleanups and like this."

I have researched this and, despite fairly scant information, Paul Randall at least says that ghost cleanup is always single-threaded, so apparently the vendor's dba is incorrect?

Any thoughts on the effect of CT when Maxdop =1?


texts: Index issue on upgrade from SQL Server 2000 to 2012

texts:
About 6 months ago, we upgraded  a large .NET project from SQL server 2000 to 2012. (i know it's 2 versions behind already, don't get me started!)

One of the stored procedures ran a query in the overnight batch process that took 55 mins since the upgrade, but jumped up to 3 hours last week. Prior to the upgrade however, the query took 1 minute. I added an index on the table as per the execution plan and it brought the run time down to 10 mins. I'm not a DBA, so I'm wondering if someone could explain why this is, as it doesn't make sense to me.

This was the query that caused the problems:

DELETE @ExcludedPersons
FROM   @ExcludedPersons
WHERE  personID NOT IN (SELECT personID
                        FROM   PersonStudy ps
                        WHERE  (ps.StudyTypeID = 1)
                        AND (ps.StudyClosedDate IS NULL))

There are 2 indexes on the PersonStudy table:

A clustered index on StudyID which is the primary key.
A non-clustered index on PersonID which is also unique.

These indexes were just carried forward from SQL 2000.  There are about 60,000 rows in the table.  There are about 500,000 rows in the @ExcludedPerson table variable (but only 1 column).

For the last few months, the sub-query in the where clause returned 1 row and the query was taking ablout 55 mins. Last week, the final person had a date added to StudyClosedDate so the subquery returned 0 rows and that was when the time jumped up to 3 hours.

The Index that execution plan suggested was to add in an index with the Keys of StudyTypeID and StudyClosedDate and include PersonID and it was this that brought processing time to 10 mins.

So, my question is this:

Why is there this much processing time on a subquery that returns 0 rows and why does the index make such a big difference?
And why the jump in processing time between the sub-query returning 1 row and 0 rows?  Is it do do with the different way indexes are handled in sql 2000 and 2012?


texts: How to retrieve data from a table with two of the columns in the table with the same value?

texts:
UserName, UserID, FollowingUserID
'RachelLim', '1003', '1004'
'YunaKim', '1005', '1007'
'AustinLee', '1007', '1005'
'BenChoi', '1010', '1001'
'RebeccaFang', '1009', '1004'
'AliceAng', '1004', '1003'

Basically, i want to find rows in which userid = followinguserid. How do i do it using select?


texts:
Platform - RHEL. Database - 11.2.0.3.

I have a diskgroup name TEST with AU as 1. I need to have the diskgroup with AU as 4 therefore I decided to drop the existing DG and recreate the new one with required AU.

I dropped the existing DG, it got dropped just fine. When I try to create new diskgoups with same name, it gives me error as below:

ERROR at line 1:

ORA-15018: diskgroup cannot be created

ORA-15031: disk specification ‘/dev/mapper/TESTDG01’ matches no disks

ORA-15025: could not open disk “/dev/mapper/TESTDG01”

ORA-15056: additional error message

Linux-x86_64 Error: 13: Permission denied

Additional information: 42

Additional information: -807646732

The header of disks are in PROVISIONED status.

What is the issue here? Can't I create new diskgroup with same name?

How to clear the disk header?


texts: In PSQL, is it possible to insert rows in two different tables with not-nullable foreign key columns referencing each other?

texts:
I want to insert a row into two tables that reference each other using not-nullable foreign key columns.

Let's say I have table A named make

make_id, bigint, (NOT NULL)
name, text
first_model_id, bigint, (NOT NULL) references model.model_id

And a table B named model

model_id, (NOT NULL)
name, text
make_id, (NOT NULL) references make.make_id

Is it possible within a transaction to insert a row into both of these tables, even though each of the rows does not exist? This is easily doable by making one of the columns NULL, inserting the first row, referencing it on the second insert, then getting the new row ID from the second insert.

I'm wondering if this is possible with columns that are NOT NULL.


texts:
Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure

user | car | seat
1    | 1   | -1
2    | 1   | -1
3    | 1   |  2

That will allow

INSERT INTO table VALUES (4, 1,  3)
INSERT INTO table VALUES (5, 1, -1)

and fail

INSERT INTO table VALUES (4, 1, 2)

Same applies for updates.


texts:
I am trying to query two tables of data and send them to a csv file for upload to a website. However i am having trouble getting the formatting to play ball. I want headings to separate into columns and then the relavant data to line up with the columns in the body of the data.

I am currently closest to achieving my aims with -

FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY ''

Which returns -

However you can see my issue. Any part numbers that have a '\' render into the wrong columns and the data does not line up with the heading.

Now please bear in mind, the tables are always updating every second, and i have no control over how the data is uploaded. I cannot remove the '\' from the part#'s.
I need a workaround that ignores the '\'.

This is quite hard to explain so...

TLDR:- I need the data to all line up like the majority of the rows in the picture, what are the 'Fields/lines terminated by' parameters?


texts: Oracle database not open and ORA-00600 error

texts:
Whenever I try to open Oracle, I get following error :
    ORA-01109: database not open

So as suggested by various blog and forum, I tried the following series of commands

c:> sqlplus /nolog
sql> connect sys/manager as sysdba
sql> shutdown immediate
sql> startup

(and startup mount, startup force etc)

I get
    ORA-01589: must use RESETLOGS or NORESETLOGS options for database open

If I try :

SQL> alter database open noresetlogs;

I get error ORA-00600: internal error code error

And if I try resetlogs after recovery, I get the following option :

ORA-01152: file 1 was not restored from a sufficiently old backup

I do not have any important data on the database, I was just starting up for a dummy project. So I don't care much about data, my target is to just work with ORACLE with some new tables.

Any help would be much appreciated.


texts: Strategy to manage foreign key from another system/database

texts:
There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients.

We use the id of the consultant in our project. So consultant id is kind of a foreign key, but from another system/database.

What happens from time to time, is that the consultant leaves the company. Sometimes a new consultant replaces this person, sometimes not.

Basically, the id that we have been using for a client becomes invalid.

If the consultant was stored in our database, we could have used foreign keys or some other internal mechanism to prevent this.

I can't imagine that we are the only project in the world with this kind of a problem.

What are the strategies to deal with this scenario? Are there any best practices?

The two systems use different databases (DB2 and Oracle). Different host. Everything is different, except that we use the client id in our project. Is there a strategy to maintain this kind of dependency?

We are DB2 and the other system is Oracle.

Our strategy is to also check the other system periodically. I am just not sure this is the most efficient way of handling this


texts: how does SQL Server 2008 handle joins internally?

texts:
for example, I have tables: User and Order

User Table:
UserId, Name, Address, Phone, Age

Order Table:
OrderId, UserId, Date, Item, Quantity

SQL:

select User.Name, Order.Date
From Order
JOIN User ON Order.UserId = User.UserId

Will the join produce a virtual table including all fields in the tables or just the two in the select?

The point to this question is if one of my fields is large (> 1GB) then I would like to place it in its own table and not leave it in  a table that gets joined many times all over the place... in order to keep the large fields from being a burden across the board.


texts: Added Timestamp Col w/Auto Initialization to CURRENT_TIMESTAMP, but doesn't set default value

texts:
I'm working on a MySQL 5.5.62 server.
I need to add an ETL control column to some tables which default to CURRENT_TIMESTAMP, and sets it's value to CURRENT_TIMESTAMP on update.
ALTER TABLE MY_TABLE ADD COLUMN DW_DTTM TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

I've done this on other MySQL servers (5.7) and it works fine - it adds the column and automatically sets the value to CURRENT_TIMESTAMP for all existing rows in the table.
On this particular 5.5 server, it adds the column but doesn't set a default value.
When I look at the table after running the above DDL statement, the column is there, but all it's values are NULL.
I know I could run an UPDATE statement to set it's initial value to now(), etc. but curious why it's not working automatically.
No other TIMESTAMP columns exist on the table.


texts:
As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.

I am using SQL Server 2008.


texts:
I have the following document:

{
    "name" : "student_1",
    "courses" : [
        {
            "name" : "Algebra",
            "grades" : [ 98, 96, 92 ]
        },
        {
            "name" : "Computers",
            "grades" : [ 80 ]
        }
    ]
}

How can I update the grades of a specified course?
For example, I want to increment some test in the course Algebra by 10 points.


texts: Access Oracle GTT table data from another session?

texts:
I have using Oracle GTT tables with condition ON COMMIT DELETE ROWS.

Now i know GTT tables data is only session specific. 1 session cannot see the GTT data of other session in General.

But is their any way to access/read GTT table that belongs to another session?


texts:
I am currently trialing numerous database designs (to solve this problem: Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance), and my latest design in Mongo consists of using many (>20,000) collections. I am attempting a multi-collection design as I have been unable to achieve reasonable performance using a single collection, most notably when inserting any new data, which I suspect is because the index is becoming so large. From reading around design architecture, I realise a multi-collection design is not necessarily conventional, but that is not the subject of this question. However, If you have any alternative ideas, please respond to the question in the previous link.

My current design will divide my sample data set (100 million locations) into ~30,000 collections, which keeps the index small. Testing this design on a sub-set of ~150 collections (>200,000 locations), shows that my read and insert queries are running with acceptable performance, which I believe will maintain performance when scaled very large. Based on the times from this sub set, the full 100 million locations should take about 15 hours to initially insert into the database. However, when running my insert on this full sample set, after the creation of ~350 collections (in ~7 mins), I receive the error:

failed: 24: Too many open files

Upon reading other people who have run into this (often with WiredTiger), I see most people can overcome the problem by increasing the ulimit of the system:

https://stackoverflow.com/questions/31596392/wiredtiger-too-many-open-files-error-during-resync-of-a-secondary-replica-se
http://www.andrewrollins.com/2010/10/20/mongodb-open-file-limit/
https://groups.google.com/forum/#!topic/mongodb-user/vHKZODyh9cE

But for my use case I don't believe this will work, as this system will scale up to a point where I expect the collection creation routine to grow too large for any sensible upper limit, not to mention I don't want to keep all these files open! For each collection creation, 3 new files are made (1 collection, 2 indexes), and to my knowledge, there is no need to keep these files open after the collection has been made. These collections are generated with a simple loop in python to insert the data for the first time, which repeats the following tasks:

Gather data from satellite output
Determine collection name to update (upsert)
Perform update (create collection, if required)

Once a collection has been made, it will never be accessed again during this routine. So any files created/opened during the collection creation can be closed after step 3. Therefore, is it possible to make MongoDB, or the server, close any/all/specific files it currently has open? Or is there a reason I am unaware of as to why mongo keeps these files open?


texts: How should I tune Postgresql for 20 GB of RAM?

texts:
I've been fortunate enough to have the use of a 20GB Linode instance running Ubuntu 64 bit. I want to try to optimize PostGres for this service, but I don't know what I should prioritize changing.

I have several datasets of 20,000 or so rows and the calculations that are being performed are memory intensive queries (spatial analyses) with a small number of rows being written after each request. The total number of users is very small (10 - 50).

I've read through this article on the Postgresql site but I don't know enough about how this works, to know what I should prioritize. I've also looked at advice on what to change for geo type work here.

For example, I tried changing the shared_buffers to 200MB (which is much less than 75% of 20GB). This resulted in the following error message:

  Restarting PostgreSQL 9.1 database server

  The PostgreSQL server failed to start. Please check the log output: 2013-03-10 12:21:58 EDT FATAL:  could not create shared memory
  segment: Invalid argument 2013-03-10 12:21:58 EDT DETAIL:  Failed
  system call was shmget(key=5432001, size=47742976, 03600). 2013-03-10
  12:21:58 EDT HINT:  This error usually means that PostgreSQL's request
  for a shared memory segment exceeded your kernel's SHMMAX parameter.
  You can either reduce the request size or reconfigure the kernel with
  larger SHMMAX.  To reduce the request size (currently 47742976 bytes),
  reduce PostgreSQL's shared memory usage, perhaps by reducing
  shared_buffers or max_connections.    If the request size is already
  small, it's possible that it is less than your kernel's SHMMIN
  parameter, in which case raising the request size or reconfiguring
  SHMMIN is called for.     The PostgreSQL documentation contains more
  information about shared memory configuration.

I returned this to it's original value and tried changing:

work_mem = 50MB
maintenance_work_mem = 256MB

My problem is that I don't know which values I should try changing, or how I should prioritize which values are key to experiment with and test. What should I specifically do to optimize this database?


texts:
Technically this question could relate to any database that performs in a similar distribute manner but given that I am specifically working with OrientDB I thought the question should be target as such.

For want of better terminology the conventional approach (for me in any case) for a distributed database is very similar to that of the architecture supporting the logic layer. A load balancer where all the queries are directed with an auto scaling group behind the LB that uses some metrics to spin up and destroy database servers on demand. As servers are dynamically added and removed Orient uses its multicast to synchronise the instances. Something like this:

It then struck me that this model could exist is a different format where each App instance has its own DB instance. The elegance is that you still get the same redundancy, but you don't have the cost of the extra db instances and you don't have the LB failure point. I know that the ration of DB instances and App instances doesn't necessarily need to be 1:1 but there is no harm in it. It also means that the code for the DB can be managed inside the app codebase as each new app instance that spins up configures its own DB and then connects up to the others to sync using some ENV VARS passed in at build time. Probably sensible to have a single fallback instance outside the ASG.

Clearly both models have advantaged and disadvantages. I also appreciate that there would need to be some specifics ironed out but are there any significant problems with this concept? Is there perhaps a better architecture that already exists?


texts: Is it possible to block the access of a super user?

texts:
In a scenario that the databases run on the client's server.
Company "A" has a database installed on that server. The customer hires the services of a company "B", which will also install its database on the server.
If company "B" uses a super user, it can have access to the company "A" database. It may even try to harm company "A" by changing information in the database.
Is there a way to block super user access to company "A" database?


texts:
Can I stop DROP COLUMN from succeeding on particular tables in PostgreSQL 9 or 10 or later?

My application makes some assumptions about columns being forever, not disappearing. I'm afraid of later programmers forgetting about this constraint.

I know triggers can fire to control INSERT, MODIFY, and DELETE of rows. But what about DDL? Can I protect against certain changes to the table structure?

Security might be one way, assigning permissions to users and roles. But these future programmers will be allowed to add columns, only dropping columns is forbidden.

I am not concerned about malicious activity. I'm defending against the eager-beavers who may move too fast to remember the bigger picture.


texts: Best way to store images in SQL Server 2014

texts:
I am working as database developer (intern), and I have to find out the best way to store image files in SQL Server 2014. Could anyone suggest the best solution?

I have found that if the file size is less than 256KB, it's better to store it in the database; if it exceeds 1MB then it's better to store in filestream, and just store the path in the database.

My research is still on to find the way which is better in terms of performance, security. If you have any idea please share.


texts: Riak - why can't I go to http://localhost:8091/stats in my browser? (Seven Databases in Seven Weeks)

texts:
I'm trying to work through the book Seven Databases in Seven Weeks, and I'm in the process of setting up Riak. After installing Riak, starting a few servers, and putting them together in a cluster, the book says to verify the servers are healthy by checking their stats by going to the address http://localhost:8091/stats in a web browser. However, when I go to this address in my web browser, I'm unable to connect.

The book is somewhat outdated so this could be something riak used to support but doesn't anymore -- but I don't know whether that's the case. It also may be relevant that I'm doing this within a, Oracle Virtualbox VM I've installed on my Windows 7 work laptop, running Ubuntu.


texts:
I want to change my SQL Server's name.

Currently I am connecting to SERVER1\SQLSERVER. SERVER1 is the machine name. I want to change it so I could connect to SERVER2\SQLSERVER and keep SERVER1 as the machine name.

I tried:

sp_dropserver 'SERVER1\SQLSERVER'
sp_addserver 'SERVER2\SQLSERVER', local

Then I restarted the server.

If I verify I get the new server name:

select @@servername

Looks like the name was changed but i cant connect to SERVER2\SQLSERVER while the SERVER1\SQLSERVER connection still works.

What am I missing here ... and is this even possible?


texts:
I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them.

My question is what are the reasons that can cause that(all the involved columns are in the index).

For example(However I want it to be more a generic question):

...
|* 18 |      HASH JOIN              |                 |  4064K|   294M|   307M| 55108   (1)| 00:11:02 |       |       |
|  19 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 20 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|  21 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 22 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |

...
  18 - access("ITEM"."PARENTID"="HOL"."MID")
  20 - filter("ITEM"."ID1"=1110 AND "ITEM"."ID2"=1112)
  22 - filter("HOL"."ID1"=1110 AND "HOL"."ID2"=1112)

Index is (ID1, ID2, MID, PARENTID) local. partition is range on ID1, ID2


texts:
I have two tables for separate things: One table for containers, one table for items. They both have things like descriptions, title, and of course ID fields: container_ID and item_ID.

My containers will need to have items assigned to them. Say each container will hold 5 - 25 items. However some items can possibly be in multiple containers.

I am thinking the easiest would be to make a table, call it for example assignment, and it has it's own ID, and then the container ID and the item ID.

CREATE TABLE `assignment` (
  `assignment_ID` INT NOT NULL AUTO_INCREMENT,
  `container_ID` INT NOT NULL,
  `item_ID` INT NOT NULL,
  PRIMARY KEY (`assignment_ID`),
  UNIQUE INDEX `assignment_ID_UNIQUE` (`assignment_ID` ASC));

When we want to pull up one container (for example with the ID 5) we do this

SELECT item_ID FROM assignment WHERE container_ID = '5'

and we'd have all the items that belong to the one container.

I am wondering if this would be an efficient way of doing it, or am I setting myself up for trouble? Is this design scale-able? What else should I keep in mind?


texts: Change clustered index without dropping the primary key

texts:
In the past, I have chosen the datetime column created as inappropriate clustered index in the table.

Now I concluded (base on the execution plan) that it would be better to choose the ID identity primary key as the clustered key, because it is referenced as foreign key very often.

I would like to drop the current clustered key and create a new, but I cannot drop the primary key, because that the full-text index is dependent on that primary key.

Can I just switch primary key to clustered index or do I need to drop the primary key and the chain of all dependent objects?

Bellow you will find table definition and clustered index definition.

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Created] [datetime] NOT NULL,
    ....

 CONSTRAINT [PK_Realty] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
...

CREATE CLUSTERED INDEX [Created] ON [dbo].[Realty]
(
    [Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


texts: Walking a BTREE index as far as possible in MySQL

texts:
Suppose one has a column of words on which one builds a BTREE index:

CREATE TABLE myTable (
  words VARCHAR(25),
  INDEX USING BTREE (words)
);

LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO TABLE myTable (words);

And now one wants to find the records which share the longest common prefix with some search query, e.g. 'foobar'.  I thought to do the following:

SELECT DISTINCT words
FROM   myTable
WHERE  words LIKE CASE
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'f%') THEN '%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'fo%') THEN 'f%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foo%') THEN 'fo%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foob%') THEN 'foo%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'fooba%') THEN 'foob%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foobar%') THEN 'fooba%'
  ELSE 'foobar%'
END

Which is fine: it's very readable and performant; and it can easily be generated in application code.

However, this search should be even simpler to resolve: just walk through the index tree according to the search term until a branch does not exist, then return all the results that branch from the current node.

Granted that walking a path through the index only once instead of multiple times is probably a needless micro-optimisation, but it feels as though it should be possible:  is it?


texts:
In Oracle SQL Developer, is there any way to include a SQL file within a SQL query? Like the command \include{myfile.tex} in LaTeX.

Example:

file1.sql contains FROM my_table;
file2.sql contains SELECT * \include{file1.sql}

Executing file2.sql from Oracle SQL Developer should be equivalent to executing SELECT * FROM my_table;.


texts: Remove ORA-01100:DATABASE ALREADY MOUNTED

texts:
I am using Oracle 11g and  there is a database create during installation name "xyz" and user "xyz".Now i created a user 'abc' and gave following permission to abc.

"CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK,
 CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM,
 CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE,
 CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE"

Now, I am trying to create database named 'abc'

At the time of creation of the database, I am get the following exception:

ORA-01501:CREATE DATABASE FAILED
ORA-01100:DATABASE ALREADY MOUNTED

Please tell me how to come out of this.
What standard procedure to be for creating database?


texts: Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

texts:
As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records.

Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were updated.

I am considering partitioning this table because I parts of it are never updated, just need to be there for reading purposes.

The primary key is an identity field, as you can see on the picture below.

questions:

1) this is a very busy table, what can I do to minimize the impact of having to lock the table and rebuild the clustered index?

2) if I leave the table as it is, how can I be sure that all the statistics for all the index were updated? Is there a script for this?

  This is the way I am currently checking when the statistics where last
  updated.

 declare @dbname nvarchar(128)

   select @dbname = db_name()

    DECLARE @SQL NVARCHAR(MAX)
    --DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @TABLEID INT
    DECLARE @TABLEID_STR VARCHAR(15)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

SELECT @vCrlf = CHAR(13)+CHAR(10);

select @TABLEID = object_id from sys.tables where name = 'MYTABLE'

SELECT @TABLEID      = COALESCE(@TABLEID,0)
SELECT @TABLEID_STR  = CAST( @TABLEID AS VARCHAR)

SELECT @SQL = ' USE ' + @dbName + ';' + @vCrlf  +
              ' SET NOCOUNT ON '  + @vCrlf  +
              ' SET DATEFORMAT DMY '  + @vCrlf  +
              ' SET DEADLOCK_PRIORITY NORMAL; '  + @vCrlf  +
              ' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ' + @vCrlf +

'SELECT o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2  ' + @vCrlf +

    CASE WHEN @TABLEID = 0 THEN '' + @vCrlf
                           ELSE ' AND ( i.object_id = ' + @TABLEID_STR + ') ' + @vCrlf
    END +

' ORDER BY o.NAME ' + @vCrlf

    PRINT CAST (@SQL AS NTEXT)
    --EXEC (@SQL)


texts: Botched migration from 9.4 to 9.5 and I'm not sure how to fix it

texts:
For reasons I can't totally explain, I had both 9.4 and 9.5 installed, which was giving me a headache as I tried to enable PostGIS, which was only installed for 9.5.

I used Synaptic to uninstall postgresql-9.4, and restarted my server with sudo service postgresql start but now I'm finding that I have a bunch of issues:

PGAdmin can't connect. I'm connecting over localhost on port 5432 -- that wouldn't have changed. The connection error says:

  Server doesn't listen
  The server doesn't accept connections: the connection library reports
  could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

In addtion, psql can't see my old databases, and it doesn't recognize any of the roles that I had set up.

I feel like I'm missing something basic here.


texts: Retrieve the triggering SQL statement as is

texts:
I am trying to get the last executed query and log it (as is, a varchar).

For example:

insert into table (firstname,lastname,birthdate) values ('donald','obama','1990-01-01')

create trigger dbo.logqueries
on dbo.users
for update, insert, delete
as
begin

    declare @query nvarchar(400)

    hopefully a simple method to recover the query as is
    without having to re-prepare the query before insert

    insert into queryLog (userId,query) values (@userId,@query)
end

This is the result I got from trying McNets' answer:

DECLARE @TEMP TABLE
(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
set @query =(SELECT EventInfo FROM @TEMP)


texts: Is there way to have logical connection in MongoDB?

texts: For example in oracle we can define tns.ora so client can connect with logical name. Although mongos can run on client servers, but when there are many different partner applications we we will need to worry about running daemon on partner machines. Is there alternative to provide mongo connection in a logical way and not necessarily physical mongo sever and port number? Thanks!


texts: Other than DDL triggers and change data capture, is there any way to track history of DDL changes in SQL server. I also want to know the client IP which made the respective DDL change. Kindly guide me .


texts: Is there a way to allow a `unique` key to be the same on a max of 3 records?

texts:
Is there a way to allow a unique key to be the same on a max of 3 records?

I have a table with urls in it. I don't want my program to put the same url in more than three records. It has a reason for three, but any more is too much. Any idea how I can make that happen?


texts: MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists

texts:
Here's a minimal case for my database scheme:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Well , And what we have inside:

    mysql> select * from test;
+-----+-------+
| id  | name  |
+-----+-------+
| 111 | AAABA |
+-----+-------+
1 row in set (0.00 sec)

mysql> select * from test2;
+-----+
| id  |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)

Now i need to read from an external csv file , with duplicate primary key , into the first table:

mysql> LOAD data infile '/tmp/tar.csv' REPLACE INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`eduman`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))

And the contents for /tmp/tar.csv:

%> cat /tmp/tar.csv
111,AAA

As i know from MySQL , it probably does a 'delete' then 'insert' , which caused the problem , it can't be deleted as the foreign key exists.
So how can i force an update to be executed , instead of a 'delete then insert' ?

Appreciate any of your responses.

From internet , i found some workable way , which suppress the checks temporarily , but is there any better response , it looks like an dirty hack , and i'm not sure if this could be a problem in multi-user environment , e.g another table with foreign key constraints may be damaged.

SET FOREIGN_KEY_CHECKS = 0;
Blabla ...
SET FOREIGN_KEY_CHECKS = 1;


texts:
Bit of background as this may be important to this error.

I have a VPS and I use the Plesk control panel, I recently upgraded my VPS, which caused my partition storage to be full, this was the first error I had, so I followed this guide to re-partition.
After re-partitioning, I was met with the following error: DB query failed: SQLSTATE[HY000] [2002] No such file or directory

Result of systemctl status mysql.service:

    mysql.service - LSB: Start and stop the mysql database server daemon
    Loaded: loaded (/etc/init.d/mysql)
    Active: failed (Result: exit-code) since Mon 2020-05-25 11:14:29 CEST; 2s ago
    Process: 22367 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
    May 25 11:16:35 systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
    May 25 11:16:35 mysql[22939]: /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too     ful...iled!
    May 25 11:16:35 /etc/init.d/mysql[22957]: ERROR: The partition with /var/lib/mysql is too full!
    May 25 11:16:35 systemd[1]: mysql.service: control process exited, code=exited status=1
    May 25 11:16:35 systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
    May 25 11:16:35 systemd[1]: Unit mysql.service entered failed state.

It's curious that it's still showing the partition is still full, I thought I'd deleted it and created a new one, so I am thinking something went wrong during the partition process.

Result of df -h:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        25G   25G     0 100% /
udev             10M     0   10M   0% /dev
tmpfs           393M   40M  353M  11% /run
tmpfs           982M     0  982M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           982M     0  982M   0% /sys/fs/cgroup
tmpfs           197M     0  197M   0% /run/user/0

result of du -sh /var/lib/mysql:

521M    /var/lib/mysql

Really stuck and do not know how to go about fixing this, I would be eternally grateful for any help.


texts: Refactoring sql for tables with similar columns but different table names

texts:
I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except that the table names are different. For example:

customerTransaction2011_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2012_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2013_Tbl

|ID | T.ID | Amount |
|---|------|--------|

The SQL script I have to re-factor is below:

Select ID, Sum(Amount), '2011' as [Year]
into #Tbl_threeYear
From CustomerTransaction2011_Tbl
union all
Select ID, Sum(Amount), '2012' as [Year]
From CustomerTransaction2012_Tbl
union all
Select ID, Sum(Amount), '2013' as [Year]
From CustomerTransaction2013_Tbl;

To re-factor this, I had thought of using Dynamic SQL within a stored procedure, so that I could pass the years (2011, 2012, 2013) as parameters to the stored procedure, which would append these to the Dynamic SQL string.

But turns out I can't be given permission to create a stored procedure.

So 2 questions, please:

If I simply make use of Dynamic SQL like so

declare @sqlmain as varchar(500);
declare @tblPart as varchar(100);
set @tblPart = '2011';
set @sqlmain = 'select ID, sum(Amount) from customerTransaction' + @tblPart + '_Tbl;'

How could I perform a union over the results from exec(sqlmain) while simply changing the @tblPart variable - must be possible somehow?

Also, would you rather another way of re-factoring such a script? Since this is my first time re-factoring, I would more than welcome any suggestions/criticism. Perhaps anybody could suggest how to re-factor in basic SQL?

This is all being done in SQL Server 2005.

Much appreciated.


texts:
I am trying to understand how Delta is monitored in log-shipping. We have log-shipping monitoring in place and I am seeing delta increases sometimes for random instances and it drops once database is synchronized. My questions are:

What is Delta in log-shipping?
What is effect of Delta on log-shipping monitoring?

Please let me know if anymore information is needed.

Thank You for your time.


texts: Can I backup (Full) the Log-Shipping Secondary Database?

texts:
I have a Highly pressured Production Environment with Database name DB1. And I want to run a weekly DBCC CHECKDB process on one of the databases there.

(You can understand,I can't do it on my production environment,Performance IMPACT).

So, I thought to use one of my Log-Shipping process.
I am have a Log-Shipping process, from DB1(Primary) to DB1_LS Database(Secondary) which locate in a distant server (DWH server).

Question:

Is there a way that I can take a full backup of the Secondary database DB1_LS?

I want to be able, to restore it and run the DBCC CHECKDB on it (COPY-ONLY BACKUP ?)


texts: Redo lag on AG readable secondaries: is there a way to overcome it?

texts:
I have a quite busy (1000+ batch requests per second, 350+ workers on average) system built on sql server 2016. It's an availability group with 3 synchronous replicas.
I'd like to redirect read requests on secondaries, but due to the redo lag (and the way the app works), I'm concerned about the fetched data being stale.

I know I can get the last hardened lsn and last redone lsn from sys.dm_hadr_database_replica_states - but is there any way to wait for the redo to catch up avoiding a while loop?

Thanks!
Gabriele


texts: Using * to enforce the need to update both tables together

texts:
I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired after a 12 year run! The current DBA is a bit mystified as to what he was trying to do here? A clue might be in the note on the former REMMED out code that apparently suddenly stopped working after several years?

Any ideas as to what this technique is trying to do as to "enforce the need to update both tables together"? The new DBA seems a bit mystified by what he was trying to do?

This is a copy of the Notation and the SQL

-- the following intentionally uses * to enforce the need to
-- update both tables together

/* this stopped working!!  WHY??
Insert Into MAIN
Select *
From IPR_CIP
Where IPR_CIP.CASE_NUM Not In
  (Select CASE_NUM From MAIN); */

(The is the current deployed code:)

Insert Into MAIN
Select *
From IPR_CIP
Where IPR_CIP.CASE_NUM In
   (Select IPR_CIP.CASE_NUM
    From IPR_CIP
    Left Outer Join MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
    Where MAIN.CASE_NUM IS NULL);


texts: How can you view the IP addresses of active MongoDB Atlas connections?

texts:
Some posts show you can use db.currentOp(true) to show active connections, but when I do that I get this error

{
        "ok" : 0,
        "errmsg" : "Using $all for currentOp is disallowed in this atlas tier",
        "code" : 8000,
        "codeName" : "AtlasError"
}

Is there another way to view active connections to a MongoDB Atlas Cluster? What tier allows you to use this command?


texts: is there some way to give me the order in which inserts should be made so all FK constraint are met?

texts:
I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc.

Is there some good way (I am thinking some tool, script...) I could use that will look at the metadata of the DDL and tell me in which order should I insert data in the tables so all FK are met?

I obviously want to avoid trial and error...
The output of such script/tool would be for example:
1. insert into country/continent tables
2. insert person
3. insert ocupation/cv


texts: Catch deadlock events in a background job?

texts:
I'm currently catching deadlock events with the profiler. (deadlock, deadlock graph, deadlock chain).
So far so good, but for this, a client computer needs to be constantly running and online.
==> Is there a way to catch & collect deadlock events as a background job directly on SQL Server?


texts: Performance issue with TOP (1) on a nullable column

texts:
I need help to identify the reason for a performance issue.

Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query:

SELECT TOP (1) dd.gsp_part_price
FROM  tabdata
       INNER JOIN locsl
               ON tabdata.fisl = locsl.idsl
       INNER JOIN locgsp
               ON locsl.figsp = locgsp.idgsp
       INNER JOIN tabdatadetail dd
               ON dd.fidata = tabdata.iddata
       INNER JOIN tabsparepart sp
               ON dd.fisparepart = sp.idsparepart
WHERE  sp.sparepartname = '1270-3132'
       AND dd.gsp_part_price IS NOT NULL
ORDER BY Claim_Submitted_Date DESC

This query returns the price immediately for most spareparts but some parts need several minutes to return NULL. So this query returns 940 rows immediately for the same part as above where every gsp_part_price is NULL which is the reason for the NULL above:

SELECT dd.*, sp.*
FROM tabdatadetail dd
INNER JOIN tabsparepart sp
    on dd.fisparepart=sp.idsparepart
WHERE sp.sparepartname='1270-3132'

Why does the first query needs 3 minutes to return NULL whereas the query below returns immediately the 940 rows? I must admit that i've still problems to interpret execution plans.

Note that the first query also returns immediately if i remove the IS NOT NULL CHECK:

dd.gsp_part_price IS NOT NULL

All involved tables are linked via foreign-keys, i have tried to add an index on  gsp_part_price which had no effect.

Here's the query execution plan:

Edit: Here are the stats on the PK and FK (to parent table tabData) indexes:

DBCC SHOW_STATISTICS('dbo.tabDataDetail','PK_tabDataDetail') WITH STAT_HEADER;
PK_tabDataDetail    Nov  6 2013 10:04AM 64327191    387089  183 1   8   NO

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_fiData') WITH STAT_HEADER;
IX_fiData   Nov  6 2013 10:04AM 64327191    378150  198 0,2441889   4   NO

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_GspPartPrice') WITH STAT_HEADER;
IX_GspPartPrice Dez  3 2013 12:17PM 64788799    64788799    200 0,0002433512    7,065503    NO

The last index was added just now as mentioned above. So the actual rows are 64788799, why the great difference between the estimated and actual rows, also , why is there a difference between the query-plan's actual rows and the "real" actual rows?

Here's the complete plan: http://s000.tinyupload.com/index.php?file_id=86912774678831839436

Edit: from the comment of Mikael Eriksson:

  I think your top(1) and order by is to blame here. The index
  scan is using the index on Claim_Submitted_Date to find the first
  occurrence of a value as fast as possible. SQL Server thinks that you
  will find such value after scanning 3000 rows. Perhaps that estimate
  makes sense if you have 5000 rows in tabSparePart and you only want
  one. The thing is that if there are no rows found or you have a spare
  part that is just recently submitted, SQL Server will have to scan the
  entire index before it figures out that there is no rows to be found.

Although i still don't understand why, it seems that this is true. If i remove the TOP 1 or change it to say TOP 1000 the result comes immediately. Does anybody have an  idea what i can do? Because, if multiple rows are returned i just need the last price submitted.


texts: Identifying which values do NOT match a table row

texts:
I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.

To better explain, here's what I would do now, to check which IDs of the list "1, 2, 3, 4" do not exist in a table:

SELECT * FROM dbo."TABLE" WHERE "ID" IN ('1','2','3','4'), let's say the table contains no row with ID 2.
Dump the results into Excel
Run a VLOOKUP on the original list that searches for each list value in the result list.
Any VLOOKUP that results in an #N/A is on a value that did not occur in the table.

I'm thinking there's got to be a better way to do this. I'm looking, ideally, for something like

  List to check -> Query on table to check -> Members of list not in table


texts: Can't run pcs -f cluster1.xml stonith create with PostgreSQL 9.6 on CentOS 7.3

texts:
Follow this guide to do PostgreSQL Automatic Failover:

https://dalibo.github.io/PAF/Quick_Start-CentOS-7.html

At this step:

pcs -f cluster1.xml stonith create fence_vm_srv1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv1" ipaddr="192.168.122.1" login="<username>" port="srv1-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_srv2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv2" ipaddr="192.168.122.1" login="<username>" port="srv2-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_srv3 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv3" ipaddr="192.168.122.1" login="<username>" port="srv3-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml constraint location fence_vm_srv1 avoids srv1=INFINITY
pcs -f cluster1.xml constraint location fence_vm_srv2 avoids srv2=INFINITY
pcs -f cluster1.xml constraint location fence_vm_srv3 avoids srv3=INFINITY

What I edited:

pcs -f cluster1.xml stonith create fence_vm_node1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node1" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_node2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node2" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml constraint location fence_vm_node1 avoids node1=INFINITY
pcs -f cluster1.xml constraint location fence_vm_node2 avoids node2=INFINITY

Got error:

[root@node1 ~]# pcs -f cluster1.xml stonith create fence_vm_node1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node1" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
Error: Agent 'fence_virsh' not found, use --force to override
[root@node1 ~]# pcs -f cluster1.xml stonith create fence_vm_node2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node2" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
Error: Agent 'fence_virsh' not found, use --force to override
[root@node1 ~]# pcs -f cluster1.xml constraint location fence_vm_node1 avoids node1=INFINITY
Error: Resource 'fence_vm_node1' does not exist
[root@node1 ~]# pcs -f cluster1.xml constraint location fence_vm_node2 avoids node2=INFINITY
Error: Resource 'fence_vm_node2' does not exist

There is not this IP: 192.168.33.1, but exist 192.168.33.111.

I think the port means PostgreSQL's port, changed it to 5432. Maybe not that meaning. So what's the right meaning?

Where was wrong?


texts: Syntax error in WITH as PhpMyAdmin procedure

texts:
Good day.
I have problems creating the following stored procedure in PhpMyAdmin 4.8.5 the query is as follows.

Create Procedure sp_TPbalonmDcdos()
WITH cte AS (
SELECT categoria,ndeporte,genero,equipo1,score1,equipo3,score3
FROM score
UNION AL
SELECT categoria,ndeporte,genero,equipo3,score3,equipo1,score1
FROM score)
SELECT ndeporte,categoria,genero,UPPER(equipo1) EQUIPOS,
COUNT(*) PJ,
SUM(score1>score3) PG,
SUM(score1<score3) PP,
SUM(score1=score3) PE,
SUM(score1) GF,
SUM(score3) GC,
SUM(score1-score3) DfG,
SUM(3*SIGN(score1>=score3)) TOTAL
FROM cte
WHERE categoria = 'CAT 2'
AND ndeporte = 'Balon Mano'
AND genero = 'D'
GROUP BY UPPER(equipo1)
 ORDER BY FIELD(equipo1,equipo3,'AMARILLO','ROJO','TURQUEZA') DESC,equipo1,equipo3;

When executing this query PhpMyAdmin throws me the following error:

  MySQL said: Documentation
  1064 - Something is wrong in your syntax near 'cte ( SELECT category, ndeporte, gender, team1, score1, team3, score3 FROM sco 'on
  line 2

It should be noted that this stored procedure when I execute it in MYSQL 8 is executed perfectly.

I request your kind help.


texts: Does running pg_dump on live db produce consistent backups?

texts:
I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).

My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to find out when disaster strikes.

Postgres documentation http://www.postgresql.org/docs/8.3/static/app-pgdump.html doesn't say anything about this.


texts: PostgreSQL Function with OUT Parameter as JSON

texts:
Can I create a function in PostgreSQL DB which returns an Integer and also has a JSON OUT parameter?
I'm facing issue while creating the same.
Error Message :
ERROR: function result type must be json because of OUT parameters
CREATE FUNCTION pms.func_test(OUT p_json json)
    RETURNS integer
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
BEGIN
    SELECT to_json(JSON_BUILD_OBJECT( 'ID', 10 ))
    INTO p_json ;

    RETURN( 1 ) ;
END ;
$BODY$;

ALTER FUNCTION pms.func_test()
    OWNER TO postgres;

Regards
Manoj


texts:
I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:

CREATE OR REPLACE FUNCTION create_table_type1(t_name VARCHAR(30)) RETURNS VOID AS $$
BEGIN
    EXECUTE "CREATE TABLE IF NOT EXISTS t_"|| t_name ||"
    (
    id SERIAL,
    customerid INT,
    daterecorded DATE,
            value DOUBLE PRECISION,
    PRIMARY KEY (id)
    )"
END
$$ LANGUAGE plpgsql

Then call it like:

SELECT create_table_type1('one');

Is it possible?


texts:
So I currently have this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

That query outputs this table:

Voorstellingsnummer  COUNT(r.Voorstellingsnummber)
-------------------  -----------------------------
4                    1
5                    2

The other query is this one:

SELECT v.Tijd, v.Voorstellingsnummer
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'

...which outputs this:

Tijd                 Voorstellingsnummer
-------------------  -------------------
2019-03-24 11:12:00  5
2019-03-24 15:12:00  4

Considerations

I need to add a COUNT(v.Voorstellingsnummer) column to the last image the same way as it is in the first one.
And then I need to add those COUNTS together to give a totalcount.
That total count should be lower than a set number and if it's higher it should not be shown.

When I do this:

SELECT v.Tijd, v.Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'

...it returns this:

Tijd,                Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
2019-03-24 11:12:00, 5,                   2

...but instead it should show both the times form the second picture both with a count of 1.

Those counts of 1 should be combined with the other table so the final counts of
Voorstellingsnummer=4 should be 2
and Voorstellingsnummer=5 should be 3

Can anyone tell me how to do this?

Further information

Basically if the table reserveringen look has (Voorstellingsnummer = 4) 6x
and the table voorstelling has (Voorstellingsnummer = 4) 2x
the count outputted by the query should be 8x.

However, currently if (Voorstellingsnummer = 4) appears 3x in the table voorstelling, but has 0 appearances in the table reserveringen it won't be counted.

So what I'm thinking is running this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

for the table reserveringen and the table voorstelling. That should result in two tables. The result of the query for table voorstelling should look like this (using number previously mentioned):

Tijd         Voorstellingsnummer   COUN
----------   -------------------   ----
something    5                      1
something    4                      1

..and then the result for the query for table reserveringen should look as follows:

Voorstellingsnummer   COUNT
-------------------   -----
5                      2

That's because table reserveringen has no Tijd column.

Those two tables should then be combined into the following one:

Tijd         Voorstellingsnummer    COUNT
---------    -------------------    -----
something    5                      3
something    4                      1

Basically both Voorstellingsnummer 4/5 approve of the condition Vestigingsnaam='Amsterdam' AND Filmnaam='Avengers'; however only Voorstellingsnummer 5 appears in the reserveringen table. Because of that I need to know how much appearances Voorstellingsnummer = (Some number) has over both tables.

Note: Reserveringen means reservations in dutch, and voorstelling is show.
Some shows might have reservations but some might not. Because the show without reservations should still be listed (as it's not full yet).


texts: Is a Booking System where User and Participant are Different Entities Correct?

texts:
Background

I am building a event booking system for a youth organisation. The events that can be booked are targeted in general at children under the age of 18 (i.e. which in Europe means that you especially need the approval of a legal guardian), but are not limited to them, as there are also events that can accept participants of all ages.

Personas

Types of people this application could be:

Parents (P) - parents or legal guardians that want to sign-up their child to an event.
Underage participants (UP) - participants that view our events and would like to sign-up for them. Given that they are underage we require to contact their parent or legal guardian.
Participants of age (OP) - participants that don't require any permission from another person, and can freely sign-up to our events.

This is informal and used only to better explain the types of users that could use the website.

Requirements

If a parent makes the booking from his user account, he'll have to enter his child's details.
If a child makes the booking from his user account, he'll have to enter the parents email address for him to sign him up.
If a participant of legal age makes the booking from his account, there is no requirement for the contact details of a legal guardian.
Children don't always have email addresses.
I don't require any other information from the child to uniquely identify him.
The application uses Django, so I need a USER table that handles the authentication. For this table the email is a required primary key.

Setup

I came up with the following setup.

USER TABLE

email - CHAR PK REQUIRED
password - CHAR REQUIRED

USER_PROFILE Table

user_id  - FK on USER REQUIRED
phone - CHAR REQUIRED
name - CHAR REQUIRED
surname - CHAR REQUIRED
dob - DATE REQUIRED

PARTICIPANT Table

id - UUID PK REQUIRED AUTO
supervised_by - FK on USER REQUIRED
email - CHAR
phone - CHAR
medical_conditions - CHAR
medications_to_take - CHAR

EVENT Table

Event specific information not relevant for the scope of this question

BOOKING Table

account_booking - FK on USER REQUIRED
participant - FK on PARTICIPANT REQUIRED
event_id - FK on EVENT REQUIRED

Remarks

the USER Table is used first and foremost as authentication
the USER_PROFILE Table extends the USER Table with contact information

Now a user of our site, which can be either P, UP, OP wants to make a booking for an event. If he's:

a P the web form will direct him to enter also the contact information of his child.
a UP which wants to make a booking for himself, will be required to share his booking with his parent, for him to sign him up.
a OP which wants to make a booking for himself, then the application will check his dob and based of it allow his sign-up.

Problem

I have the above setup and I see the following problems with it:

For the case of the OP making a booking, I duplicate data in the USER_PROFILE Table as well as in the PARTICIPANT Table.
Participants that are now UP but could later be OP have duplicated information, similarly to the previous point.
I am not really sure this is the best setup, as I fell there could be a simpler, more elegant solution.


texts: Is it safe to move big table to another tablespace while working with it?

texts:
I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat tables to another tablespace, vacuuming them and returning back.

Can I do it safely while not turning off services working with that table?  It is around 15 separate one row inserts  and ~7 select operations per minute.

And few more insignificant questions:

What manuals/guides or parameters settings would you advice for autovacuuming table, that works in such load.
Is it better to pass old data delete operations with frequency of inserts or delete big stacks of rows (every day,week, month,etc..)?


texts:
I update statistics on a weekly basis using Ola Hallengren's solution.

Based on the article below, I am considering enabling the Auto Stats Async
and turning on trace flag 2371.

Databases With Large Tables Should Use Auto Update Stats Async Feature

Right now, the async option is false for my database:

Also, from Important Hotfix for SQL Server 2008 and Newer:

  This setting reduces unpredictable query performance by allowing
  statistics to be automatically updated asynchronously while your
  currently running queries continue to use the old statistics until the
  updated statistics are ready to use. The alternative to this (which is
  the default) is for query execution to halt (only for queries that use
  statistics for that object) while the statistics are automatically
  updated synchronously for that object. Depending on the size of the
  object, and on your hardware and I/O subsystem, this could take
  anywhere from a few seconds to many minutes.

Question:

In a test environment, what could be a good and simple test to do before/after setting Auto Update Stats Async to ON?

In what circumstances I am more likely to benefit from auto update stats async on?

Databases 500GB+, big tables.


texts: How to Decrypt Stored proc in Azure Managed SQL Server

texts:
I have created Managed SQL Server in Azure and encrypt some stored procedures. Can any one help me to decrypt the same.
Thanks in advance.


texts: SSAS tabular browse Excel relationships not working

texts:
I'm currently building an education data warehouse.

I have three factless fact tables for distinct set of grades due to the fact I have no measures (I'm not aggregating anything), the grades are completely unrelated to each other hence why they're in different tables. I'm only slicing student dimension by grade fact tables.

In Excel, when I drag a student onto a row and then grades into columns, I would expect to see grades for the student.  However, I am just seeing all students/all grades. The relationships do not seem to be working even though I can clearly see the relationships in the tabular diagram view.

Any ideas as to why this is?

As you can see below, SSAS has imported the relationships; however when I browse in Excel there does not seem to be any relationship between the data. If I filter by one row for student and select grades, I just get all grades not just the grades relevant for that student.

If I drop student keys in the row section and grades in the column section, it will just display all/all.  If I change the student key filter to display a few records nothing seems to change, so SSAS is fairly useless to me at the moment.


texts: To automatically stop/disable transactional replication whenever table get altered

texts:
I have set up a transactional replication from multiple Databases (6 Different Server) to 1 consolidated database. The schema is identical. Segregation is done by the column SiteName .
My problem is :

Every time when a table is altered , the MSIns,MSUpd and MSDel stored procedures at subscriber get altered and MSUpd and MSDel SPs where condition get changed based on only ID (PK) , and it overwrites the SPs where my condition was based on ID and Sitename.
Thus , the destination table data gets updated for all the rows based on First database ID.

Example :
If we alter table employee and add/alter column with update command  for all 6 sites ,
when 1st site is applying the changes it's taking where condition based on ID only and if ID is 2 all the records will updated in destination DB with ID 2.

Is there any way to disable the replication as soon as table get altered so that i will have time to change the stored procedure at subscriber to add Sitename in where condition .


texts: Auth failing when using Devart ODBC connector to mLab

texts:
I'm trying to connect PowerBI to a mLab-hosted Mongodb instance.

I first tried installing the MongoDB BI-connector, but after installing it didn't show up on the drivers list in Windows ODBC manager (neither 64 or 32 bit)

I then tried installing Devart's trial version Mongo ODBC driver. This showed up in Windows ODBC manager, but when I tried setting up and testing a connection I get Authentication Failed.
I tried setting authMechanism=SCRAM-SHA-1 in the Advanced Connection Options but it didn't help.
I also tried adding a new user on mlab.

Any suggestions on figuring out where the problem is would be appreciated!
Thanks.


texts:
I have 4 queries I want to combine in one to reduce DB calls. Each interval list the products for that time interval and is used for a specific sub treatment.

select * from product where add_time >= NOW() - INTERVAL '12 hour' (get product add the last 12h)
select * from product where add_time >= NOW() - INTERVAL '24 hour' (get product add the last 24h)
select * from product where add_time >= NOW() - INTERVAL '7 day' (get product add the last week)
select * from product where add_time >= NOW() - INTERVAL '30 day' (get product add the last month)

Is it possible? what will the query look like and how to access each group interval (12h, 24h, 7 day, 30 day) to process the result?

PostgreSQL 9.5.12


texts: Are individual queries faster than joins?

texts:
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?

First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

I have tried to put one extremely simple example together:

SQL Fiddle

Schema Setup:

CREATE TABLE MASTER
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);

CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);

INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');

CREATE SEQUENCE SEQ_DATA_ID;

INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);

Query A:

select NAME from MASTER
where ID = 1

Results:

| NAME |
--------
|  One |

Query B:

select ID, VALUE from DATA
where MASTER_ID = 1

Results:

| ID | VALUE |
--------------
|  1 |   1.3 |
|  2 |   1.5 |
|  3 |   1.7 |

Query C:

select M.NAME, D.ID, D.VALUE
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1

Results:

| NAME | ID | VALUE |
---------------------
|  One |  1 |   1.3 |
|  One |  2 |   1.5 |
|  One |  3 |   1.7 |

Of course, I didn't measure any performance with these, but one may observe:

Query A+B returns the same amount of usable information as Query C.
A+B has to return 1+2x3==7 "Data Cells" to the client
C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.

Generalizing from this (as far fetched as it is):

A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?


texts: What Host Name is my application connecting to SQL Server on

texts:
We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster.

So the broken cluster is running on a single node "NODE1" on IP "10.1.1.10".

The old SQL Cluster Name has been added as a static entry into DNS "SQLClusterName" and its pointing to NODE1's IP address 10.1.1.10.

For the Migration I am going to use the old cluster name "SQLClusterName" so that we don't need to change a bunch of applications connection details.

The issue I have is that there is an application that uses a database on this server and we are unable to change the app at all. I am trying to make sure that said app is connecting on "SQLClusterName" and not "NODE1".

Is there a way without checking the actual application to see what Host Name the application is connecting through to SQL Server on? For the life of me I cant seem to find this information anywhere.

Thanks.


texts:
When to use a multidimensional relationship in an ERD? My textbook is in Dutch, and the two paragraphs that explain about multidimensional relationships is too vague and brief. I don't understand when it is applicable to use it, and I can't find any information on this subject on the internet.
My guess is that my textbook is naming the concept different from what it's real name is, since I can't find any information on it. In my textbook it shows the following diagram:

Unfortunately, this is the cardinality notation my textbook and teacher are using, I don't know how it translates to any different notations, but to explain how it works: T = mandatory, O = optionality, n, m, k all mean many.
It works in the opposite direction as most notations: All teachers can teach 0 or many subjects and classes, all subjects are teached 1 to many times by teachers and to classes, all classes are teached 1 to many subjects by 1 to many teachers.
Maybe if someone knows its true name I can do some research on this concept...


texts: Azure SQL cross-database queries returns too many rows with joins

texts:
I have a problem with Azure SQL cross-database queries, it looks like if we join data across databases the join is not distributed as input to the external database.

Currently I tried to implement a simple example and therefore I used this starting point:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-getting-started-vertical.

As preperation I created 199999 CustomerInformation entries and 200 OrderInformation entries.

Following query works as expected the CustomerInformation Table returns just one row:

SELECT o.OrderId, c.CustomerID, c.CustomerName
  FROM OrderInformation o
  JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
 WHERE c.CustomerID = 2

But any query with a join that does not specifiy the CustomerID returns all the rows from the Customers Database.

SELECT o.OrderId, c.CustomerID, c.CustomerName
  FROM OrderInformation o
  JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
 WHERE o.OrderId = 1870

Therefore the queries are really slow because the servers transfer to many rows. Do I miss some setting or is it not possible to separate this data with the suggested queries.

Thanks for your help.


texts: Return every instance of a string contains search

texts:
I have this sample table and data

CREATE TABLE [dbo].[SampleTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TextColumn] [nvarchar](1000) NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[SampleTable] ON

INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (1, N'This is the first Test string')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (2, N'This is the second one')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (3, N'This is the first really long string of text that should be included in the result set.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (4, N'This is the second long string that will not be returned in the result set')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (5, N'This is a really really long result set that should also be first included in the result set.  It has a seperate sentence in it as well.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (6, N'Now this is a really really first one.   It is so long that I have forgotten how long it really was.   Well it could be really long but first lets do this.  ')
SET IDENTITY_INSERT [dbo].[SampleTable] OFF

With The Below Query

;with cte as (SELECT
      a.Id ,
      a.TextColumn,
      CASE When LEN(TextColumn) <= 60
          Then TextColumn
          ELSE LEFT(TextColumn, 60) + '...'
          END As Target,
          charindex('first', TextColumn) as SearchPos
  FROM
       SampleTable a
       WHERE Contains((TextColumn), '%first%'))

select *, case when SearchPos > 20 then substring(TextColumn, SearchPos - 20, 20) + 'first' +
substring(TextColumn, SearchPos + len('first'), 20) else substring(TextColumn, 1, SearchPos-1) + 'first' + substring(TextColumn, SearchPos + len('first'), 20)
 end as NewString
from cte

I am trying to return every instance of a word with a Contains search.  But the string could be in the middle of a large sting.  So I am returning the string and a given length to the left and right of the searched string.  Sort of like a summary.  If the word occurs multiple times it is only returning the first instance and moving on to the next instance.  How can I get it to return all instances even multiple ones in the same cell.  So the item with Id of 6 should return twice not once because I am looking for the string 'first'.  I am using Sql Server 2012. The New String Column Is What I am shooting for.


texts: I want to create store procedure of selecting record of last 3 months from 3 tables

texts:
CREATE OR REPLACE PROCEDURE data.select_data_last_three_month()
LANGUAGE plpgsql
AS $procedure$
declare
v_time1 timestamp;
Begin

    select to_timestamp(to_char(current_date-94,'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss') into v_time1;

    select q1.*,q2.*,q3.* from
    (select * from public.table1 where time >'v_time1' order by time desc) q1, --'2020-05-25 00:00:00'
    (select * from public.table2 >'v_time1' order by time desc)q2,
    (select * from public.table3 time >'v_time1' order by time desc) q3
    ;

 END;
$procedure$
;

call data.delete_interface_tcp_data_three_month_back()

If I call this procedure it gives this error:

SQL Error [22007]: ERROR: invalid input syntax for type timestamp with time zone: "v_time1"


texts: Return hours, minutes and seconds left if column wasn't updated

texts:
I currently have this query:

UPDATE MyTable
SET    next_update = now() at time zone 'utc'
WHERE  next_update::timestamp < now() at time zone 'utc' - interval '1 day';

Which updates the column next_update if a day has passed since the last next_update was set. But in the case the value couldn't be updated (a day hasn't passed yet) How can I return the hours, minutes and seconds left for update?


texts:
How can i insert missing dates from a query i created.  The result below:

Date          Frequency
2014-05-18    5
2014-05-20    7
2014-05-25    7
2014-05-27    6

I want the result to have missing dates with 0 value as shown below:

Date          Frequency
2014-05-18    5
2014-05-19    0
2014-05-20    7
2014-05-21    0
2014-05-22    0
2014-05-23    0
2014-05-24    0
2014-05-25    7
2014-05-26    0
2014-05-27    6

Please note that I have read only access to the server.


texts: SSRS2016 external parameters for mobile report

texts:
How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report?

The same scenario is possible for paginated reports as it always was. It's one of the key functionalities.

In my scenario I need to integrate the reports into an existing application. So some settings like USERID or LANGUAGE must be passed to the integrated report to initialize it correctly. It's not tolerable to expect a user of the application to choose a language every time he/she runs a report inside of the application...

Besides setting parameters for the datasets within the mobile report I could not find any option to take values from external / url...?
Any solutions?


texts: Drop and Recreate All Objects in Database Project

texts:
I have a database project in Visual Studio Team Services Git, and want to deploy into database on actual server.

(a) Is there setting in publish profile,  to drop all objects and recreate?
(b) or is there setting to create whole DDL script, rather than finding schema comparison discrepancy? Want to conduct this from Visual Studio, I know SSMS has option to Generate Scripts for all objects.

Want to conduct for all tables, sprocs, views, not just simple example below. Plan to redeploy and repopulate data warehouse everyday.

Example, say this in Source control database project.

Source Control:

create procedure dbo.SelectTestOne
as
select 1

And actual server on localdb is

Local Server Discrepancy:

create procedure dbo.SelectTestOne
as
select 2

Predeployment Script:

If I create a Script.PreDeployment, which drops all objects,

drop procedure dbo.SelectTest

The Final publish profile Script, will still do an alter, instead of recreate.  So question is how do I drop all objects and recreate them? (I know Redgate has this option)

drop procedure dbo.SelectTest
GO

GO
PRINT N'Altering [dbo].[SelectTest]...';

GO
ALTER procedure dbo.SelectTest
as

select 1
GO
PRINT N'Update complete.';

GO


texts:
My database application runs many different queries. I have set log_min_duration_statement to 1000. But queries logged there are not always slow, most times they just take a few ms.

Is it possible to get a statistic of all queries, and how often they took longer than 1000ms?


texts:
I have deleted some tables on a SQL server. Suddenly the size of the database jumped by 100Gb (whereas the deleted tables were taking 10% of that..).

I have read good and bad things about SHRINK hence to test I restored a copy of this database to another server and SHRINKed it: the massive unallocated space was released. However I am reading that SHRINK may cause the database to be fragmented:
- How can I avoid that,
- Or is there an easy way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?


texts: Database master key and certificate for backup encryption - SQL Server

texts:
I'm trying to understand backup encryption and I have created a master key (in the master DB) and used a password as follows:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test123!';
GO

Then I create a certificate as follows:
CREATE CERTIFICATE CertName
WITH SUBJECT = 'DB Backup Certificate';
GO

I backup this certificate as follows:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk',
ENCRYPTION BY PASSWORD = 'bkPofcert!')

Then I take a DB backup using this certificate, delete the DB, certificate and the master key and try to restore the DB which is not possible (I do understand this).
So I create the master key, restore the certificate from the certificate backup that was taken above and then it lets me restore the DB successfully.
My issue is that even if you provide a different password than what you originally used to create the master key (e.g. 'newpassword123!' instead of 'test123!', it will still let you restore the certificate and the DB. Isn't the certificate encrypted by the master key? If so, how does it let you proceed even with a different password for the master key? Also if this is the case, why do we need to backup the master key? When we can just create a new one with a different password and all works well.
I also tried to restore this DB on another instance (after creating a new master key with a different password and after restoring the certificate as per above) and it still allowed me to restore this DB. So this also eliminates the possibility that it lets me restore the encrypted DB (even with a different master key) due to the master key being encrypted (and hence "recognised") by the service master key - since on the other instance the service master key should be different.


texts: Use "LEN" function in "WHERE" clause in "CREATE UNIQUE INDEX"

texts:
I have this table:

CREATE TABLE Table01 (column01 nvarchar(100));

And I want to create a unique index on column01 with this condition LEN(column01) >= 5

I tried:

CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE LEN(column01) >= 5;

I got:

  Incorrect WHERE clause for filtered index 'UIX_01' on table 'Table01'.

And :

ALTER TABLE Table01 ADD column01_length AS (LEN(column01));
CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE column01_length >= 5;

Produces:

  Filtered index 'UIX_01' cannot be created on table 'Table01' because the column 'column01_length' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.


texts: Storage and presentation of database data across multiple languages

texts:
Apologies if this has been asked before.  After extensive searching I cannot find anything that serves as a solution (a similar answer is posted below).  I am afraid this is an open-ended (broad) question simply because I don't know what I don't know.  Please don't downvote if not suitable for this site; some pointers in the right direction would be much appreciated instead.

We have a web application running on SQL Server 2012 with ASP.Net.  There are around 150 tables and 600 stored procedures.  We have successfully managed to manage data across three different languages historically, fortunately because each client only required data in their own language.

For example:

Client enters database data from web forms in their own language (using nvarchar/UTF16 across all respective fields)
ASP.Net uses resource files to provide the interface in the local language

We now have a prospect who requires a standard set of global database data to be available in 20 languages.  While the interface translation (although a big job) isn't technically difficult, I am struggling to see how this is achieved in SQL Server, and would appreciate any input from people who have achieve this historically.  Given that enterprise-level companies such as Oracle and SAP must do this regularly (I assume), I'm sure it's possible somehow.

A related answer, albeit for MySQL seems to be feasible...

  Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables.

  create table books (
      book_id int,
      book_name varchar(200),
      description varchar(500)
  )
  create table books_language (
      book_id int,
      language_id vachar(10),
      book_name varchar(200),
      description varchar(500)
  )

  This returns all records including default language.

  select book_id,
         isnull(books_language.laguage_id, 'default')
         isnull(books_language.name, books.name) as name,
         isnull(books_language.description, books.description) as description
  from books
       left join books_language
       on books.book_id = books_language.book_id

...but I can't figure out the following practicalities, assuming my database doubles to 300 tables in size.

Because each user requires data in their own specific locale, how do you pass every query without doing this dynamically, for the application to bind to the respective display fields?
How do you handle a table having 20 data fields, rather than just one as in the example?
How do you deal with text searches, when the table required for the data may differ every time?

Or is there a better way entirely?

I've tried widgets such as Google Translator and Bing, but they don't work properly.  While they both struggle with data from partial page reloads, they just translate everything, and often not very well.  I've also read about plug-in software, although these were for Oracle only.

Examples

Take a skills database for example, where peoples' skills are recorded and managed.  The person table won't obviously need translating, and date formats (e.g. date of birth) would be handled by the interface (.Net based on browser settings).  However, the skills table is more complex:

Id int,
SkillTitle nvarchar(100),
Description nvarchar(max),
Objectives nvarchar(max)

Then there's the table that defines abilities against the skills:

Id int,
SkillLevel int,
Title nvarchar(100),
Description nvarchar(max),
ContextualData nvarchar(max)

Basically in my database there's about 70 tables where text-based data would need to be managed in different languages.  Some have a couple of text columns, and some as much as 10.


texts: Feasibility of Automated and Continuous Performance Tuning in the future

texts:
First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, create/modify indexes, update statistics, etc which is a tedious task.

My question is very open, but I'd like to know if there is already a DBMS that automatically and continuously tunes itself (create/modify indexes, update statistics, etc) based on the data that lives in the database and its usage? If there isn't anything like that would we expect to see automated systems like that in the future? Given the increasing rate at which we're accumulating data these days seems like it will be a must in the near future.

What are the current impediments or negative side of doing that?

I understand that modifying the queries and stored procedures to be optimal may be hard to automate an more of a human task. But I believe good level of tuning automation could be done on the database side.


texts: mapping/translation table design for one to one or multiple to one condition for mapping

texts:
I am trying to come up with a flexible design to allow mapping a code or a combination of codes from one system to a single code in another system for a given type of data.  For instance for group type 'customer level' codes 'abc' and '123' from system 'A' equal code '5' in system 'B'.  Another possible is that group type 'shipping code' code 'xyz' from system 'A' could be a direct mapping to code '99' in system 'B'.  I would like to have a flexible design that allows for the multiple conditions to one value and a one to one value mapping without having to create a new table for every condition if possible.  Is that possible?

I could just create a flat table that has a column for each system's codes and use a delimiter in the multiple code column, but that seems a bit messy.  There will not be hundreds of mappings but probably about 30 or so.  Thank you for any assistance in advance.


texts: how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

texts: I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in both server is transcredit. around 500SP are there in 252 server. can anyone help me?


texts:
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p

Restore to 8.0 fails on MySQL database due to ISAM tables.
Restoring individual databases, except MySQL, works as expected.
Once I have an 8.0 backup I'll upgrade the 18.04 server to 20.04 with MySQL 8.0
Is it possible to modify the MySQL 5.7 dump to load on 8.0?


texts: DynamoDB: searching with primary key or range key, which way is faster?

texts:
To those familiar with DynamoDB, I'm trying to make a table where items only have 2 entries. For example, . Most of the time I'll be searching the user_id with a facebook_id on hand.

Amazon's DynamoDB only provides the ability to search based on 2 keys, the primary key and the range key.  I have 2 options; either making the user_id or the facebook_id the primary key.  Primary and Range keys differ on the matching algorithm used.

I'm just curious which way is more efficient in this case?  Searching with the primary key, or searching with the range key, or is there really no difference?

Could we add DynamoDB as a tag?  As DynamoDB is fairly new, I'll not be surprised if no-one knows the answer.


texts: Meaning of different icons in the SSMS Object Explorer

texts:
I have noticed that the Database symbol to the left of the Server name in the object explorer tends to differ without any obvious reason. Two servers have a database symbol with a Green arrow on them and another server has a database symbol with a blue question mark (?). I use windows authentication on all servers. What is interesting is that I have two virtual desktops open (all connected to the same servers, but only one VDesktop has the blue icon/symbol. the rest are Green with a white arrow on them).
I just noticed this and was curious if anyone knew what they meant?

Just curious, because the Blue Question Mark is on my production server. Just want to make sure its no cause for concern. Sorry if the answer is very obvious


texts: Should I check for existence of temp tables in Stored Procedures?

texts:
Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them?

Similarly, is there a case when explicitly dropping them at the end of the Stored Procedure is preferable to letting SQL Server clean them up?


texts: MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

texts:
In MySQL documentation it's written:

  If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.

Why statement-based logging format is not working with READ COMMITTED or READ UNCOMMITTED?


texts: Is setting a foreign key as primary key in a One-to-One relationship a good idea?

texts:
I have a table user and a table user_statistic with a one to one (1:1) relationship.

Note that we have an identifying relationship between user and user_statistic.

Which is best practice for user_statistic primary key ?

Use an auto increment id as primary key
Use the user foreign key as primary key
Use both as a composite primary key (feels like a bad idea but in an identifying relationship, foreign key must be part of the primary key...)


texts: Is there anything wrong with splitting a complicated view into many small ones?

texts:
I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values such as probability_of_buying_premium_membership. I have around 20 such columns of varying complexity (both in terms of lines-of-code and also computational complexity), ranging from a single line of SQL to several dozen. Right now they are all in one monster view.

Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Ie, break it down into views called
customer_life_time_value, customer_purchases_per_week and so on, and then recreate the monster view by joining 20 views? It seems like there shouldn't be a performance hit because of the joining, as it's over a indexed primary key. Many of the columns/views will perform similar calculations (purchases_per_week and purchases_per_quarter would look very similar), but it seems like the DB should be smart enough to share computation if I am selecting from the joined view.

I am using Postgres, but interested for answers in general.


texts: What relationship type to use for this messaging system?

texts:
I am trying to make a small messaging system. It should have these properties:

One user can send a message to one or many users
Messages can be sent only to registered users (i.e. to an existing 'idUser' record)
There should be tracking of sent messages (who sent the message and to whom)

Here is how the current database design looks like:

Table Private Message stores only the content of the message.

Table User Private Message is used to keep track of sent messages. It has columns "idUser from" and "idUser to" which indicate the user that sent the message and the user (can be more than one) that received the message.

I am having difficulties with choosing the right type of relationship (if the whole concept is correct in the first place) for the tables "User" and "User Private Message".
I assume that there should be two separate relationships for the tables 'User' and 'User Private Message', like this:

One-to-one relationship, that connects User.idUser and User Private Message.idUser from
One-to-many relationship, that connects User.idUser and User Private Message.idUser to

Could you please advise me on what relationship to use here. If you have any suggestions on how to approach this type of problem easier, I would appreciate that even more.

Thank you all for your time.


texts:
I had DB Vertica 8.0.
Create schema WAREHOUSE with table include field by type LONG VARCHAR.
Now I tried to execude SELECT for example

SELECT * FROM WAREHOUSE.ALL_EVENTS a
WHERE
a.original_data like '%d963%'

returned error

SQL Error [4286] [42883]: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
  [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
    com.vertica.util.ServerException: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown

In Oracle I used dbms_lob package for CLOB fields.

Vertica have simular package for LONG VARCHAR types?

How "LIKE" by LONG VARCHAR?


texts: How to change DB2 Demo license to a free license (Express Edition)

texts:
For a PoC (Proof of concept) I downloaded DB2 which comes with a 90 day trial license.

Since we would like continuing to use the DB2 for further demonstrations I would like to import a license file for a free DB2 version (DB2 Express Edition).

How can I do that?

Get the free license file
import it in our system


texts:
I have an sqlite table Task(type INT ,due_date LONG) where due_date is stored in epoch

TYPE     DUE_DATE
----    ----------
 1      1582038293
 1      1485034292
 2      1082048291
 3      1982088290

I want to optimize a query to retrieve all type 1 tasks with a given range and type 2 & 3 tasks that are after some date.

Assuming :from and :to are variables -

SELECT * FROM Task WHERE (type=1 AND due_date >= :from AND due_date < :to) OR
(type=2 AND due_date >= :from) OR (type=3 AND due_date >= :from)

Would it be more optimal if I create an index for columns type & due_date?
Should I use 3 separate queries for each type if I use index?


texts: I need a help to get data from a table which has id,serialnumber,process(prcs) and result

texts:
The query able to get the recent failed process(prcs) of that serialnumber,if the recent process(prcs) is Pass, it will not show. But my problem is if the Last process is PASS,do not show the serial number.Now im able to get the recent Failed process but i want to ignore it if the last process is pass.Thank you

The Query i tried-:

SELECT tt.id,tt.serialnumber,tt.prcs,tt.result
FROM tblTest tt
INNER JOIN
    (SELECT serialnumber,prcs MAX(id) AS MaxID
    FROM tblTest
    GROUP BY  serialnumber,prcs) groupedtt
ON tt.prcs = groupedtt.prcs AND tt.id = groupedtt.MaxID
WHERE result='FAIL' AND tt.sn='A01'
ORDER by serialnumber


texts: Optimum design / structure for product data database?

texts:
What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values common for all variations of a given product. A typical example of product variations & attributes table is in the image below:

(The 'ATTRIBUTE X' is here to give you and idea of a different attribute type than for 'Product 1').

Would it be practical to try to 'group' some of the attributes (to save time and minimise errors when creating new product entries)? If so, how complicated would it need to be?

If it's relevant - I'm thinking of using mySQL engine, but I'd be happy to see any applicable example from other engines.

EDIT :
More information: at the moment we have over 400 variations in a spreadsheet, each one about 15-20 attributes, so that would give about 6000-8000 'data points'. It would be great to be able to list all possible values for a given attribute (to create a dropdown menu when adding new entries), and also to have a choice of possible attributes for a given product. It would be great to be able to specify which attributes are applicable for product variations of each product.