texts: Changing Quoted_Identifier on existing Constraints / Rules / Procedures

texts:
I am currently working with an old-ish database on a 2008-r2 Server which uses a lot of objects that were created with Quoted Identifier set to off.

I am mainly looking at these types:

CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
RULE
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TRIGGER
VIEW

I am now trying to change the Quoted Identifier Setting which had me stumped right away as I discovered that I can't even alter a Constraint.

For the constraints: I am thinking that I have to somehow make a temporary clone/copy, delete the original and then recreate them using the copy and the Quoted_Identifier set to ON, but I don't really know how to do this or how to automate this as my SQL-skills are limited. Could someone help me? Or does somebody know of an easier alternative method?

Edit: Fixed a mistake, added missing information plus this:

I have discovered the INFORMATION_SCHEMA.CHECK_CONSTRAINTS Table which contains the Check Clause which I can use to recreate the constraints. But while I was trying to do so I discovered that I can't set the Quoted_Identifier Flag in this way.

To check if I was successful I use this Statement:

SELECT SCHEMA_NAME(schema_id) AS Schema_, name AS Name_, type_desc AS Typ, type, object_id,
    OBJECTPROPERTY(object_id, 'IsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsQuotedIdentOn') = 0
ORDER BY Schema_,type_desc, name

This gives me all the Objects and by using their object_id, their Quoted_Identifier "Flag".

Is my method for checking the setting flawed? Am I simply not setting it the right way? OR Is what I am doing completely stupid as it is no possible to set it for constraints?


texts:
I have ran the following SQL in psql:

CREATE USER bspu LOGIN;

CREATE DATABASE bsp OWNER bspu;

GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu;

\c bsp

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  api_key VARCHAR(100) NOT NULL,
  api_secret VARCHAR(100) NOT NULL,
  auth_token VARCHAR(128) NOT NULL
);

When I login as bspu, and try to query the users table, I get the error:

permission denied for relation users

I tried running:

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO bspu;

But it doesn't help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT:
I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.


texts: How to estimate the size of a PostgreSQL DB to be dumped through pg_dump

texts:
Similar to this question about MySQL --
I need to find out the size of a dump file prior dumping but for a PostgreSQL Database. I could not find any reference to this in the documentation


texts: Restoring a mysql database in windows 10

texts:
I am trying to restore a mysql database in windows 10 using the command below.

mysql -u root mydatabase < mydatabase.

My computer is quite fast but this is using about 99% of my disk space when I looked through my task manager. Sometime it takes about 2 hours to restore a database.

What can I do to make this faster?


texts: If not all ids are present return nothing

texts:
I have the following query

SELECT ATV.ATTRIBUTEVALUE_ID,
       ATV.ATTRIBUTE_ID,
       ASS.ASSET_ID
FROM   CLOTH.ATTRIBUTEVALUE ATV
JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
JOIN   CLOTH.ASSET ASS
ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )

which returns

ATTRIBUTEVALUE_ID, ATTRIBUTE_ID, ASSET_ID,
7                      2          9103354
8                      2          7829028
9                      2          7829028
10                     2          7829028

It should  return nothing, because 13057020 is not returned.

What this means is, when I have ATTRIBUTE_ID # and all ASSET_ID are not present (like now, 13057020 is missing) nothing should be returned.


texts:
Sorry if this is too basic for this forum, but I have had no luck in StackOverflow. I am a newbie to SQL.

I am using SQLite. I have a table where rows represent individuals in a point in time. I am trying create a column (statusLag) with the lagged values of another column (status), like so:

workerID    timeVar     status      statusLag
---------------------------------------------
1           1           0           NULL
1           2           1           0
1           3           1           1
1           4           1           1
----------------------------------------------
2           3           1           NULL
2           4           0           1
2           5           1           0
2           6           0           1

What I have tried

Approach 1: UPDATE

UPDATE myTable
    SET statusLag = (SELECT t2.status
                     FROM myTable t2
                     WHERE t2.workerID = myTable.workerID AND
                           t2.timeVar < myTable.timeVar
                     ORDER BY t2.timeVar desc
                     LIMIT 1
                    );

This does the job in the tests I ran. However, I have a very large table -- 3.2 billion rows. I could work for now with one table of 300 million rows, this query uses way too much RAM, more than what my machine (12GB) can handle. So, for all (my) practical purposes, this doesn't work.

Approach 2: JOIN

It seems to me that I could do a JOIN. Something along the lines of

SELECT t1.*, t2.status as statusLag FROM myTable AS t1
        LEFT JOIN myTable  AS t2
        ON t1.workerID=t2.workerID AND t1.timeVar=t2.timeVar+1
        ORDER BY t1.workerID, t1.timeVar ;

I am not clear whether this will do the job or not. Also, this is the first time I have tried a JOIN, and it is my impression that this query alone won't insert or update anything on myTable. Do I need to complement this query to accomplish what I explained at the beginning of my post?

Any thoughts, any help, much appreciated. I have been struggling with this for two weeks now, and I need to get this done.


texts: Create a trigger in Postgres that will insert a record to another table with added column

texts:
I have these 2 tables created in postgres,

CREATE TABLE EMPLOYEE(
   ID           INT             PRIMARY KEY NOT NULL,
   NAME         VARCHAR(100)    NOT NULL,
   ADDRESS      VARCHAR (250)   NOT NULL
);

CREATE TABLE HIST_EMPLOYEE(
   HISTORYNUM   INT             NOT NULL,
   ID           INT             NOT NULL,
   NAME         VARCHAR(100)    NOT NULL,
   ADDRESS      VARCHAR (250)   NOT NULL
);

..so whenever I want to insert, update, or delete a record in my EMPLOYEE table, that record should be inserted in HIST_EMPLOYEE table. regardless if it is single or multiple rows

..I know that there are lots of answers in this site..but the problem is I added a column in my HISTORY table HIST_EMPLOYEE..that's why I cant used this script that most of the answers posted,

insert into hist_employee select * from employee

...this is what i've started so far, but it has so many errors..

TRIGGER

CREATE TRIGGER insertHistory
    AFTER INSERT OR UPDATE ON employee   --MAIN TABLE
        EXECUTE PROCEDURE insertHistory('hist_employee');  --HISTORY TABLE

TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION insertHistory() RETURNS TRIGGER AS
$func$
    BEGIN
       EXECUTE format('insert into %I select * from %I', TG_ARGV[0] ,TG_TABLE_NAME );
       return null;
    END;
$func$
LANGUAGE plpgsql;

  NOTE:

  This trigger should be also applicable or flexible enough to be implemented on other tables with different set of columns.
  HISTORYNUM column is unique per id,
  And, is it possible to combine these 3, AFTER INSERT, AFTER UPDATE, BEFORE DELETE .. in one trigger ??

thanks to all who will respond..sorry if you find my question too vague


texts: row n was truncated; it contained more data than there were input columns: only some rows

texts:
I am running the following script to load data from a .csv file on MySQL Workbench:

SET foreign_key_checks = 0;

LOAD DATA LOCAL INFILE "C:\\movieDB\\movie_metadata_edited.csv"
INTO TABLE IMDB.TITLE
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
IGNORE 1 LINES

(Title_id, Length, @dummy, Primary_title, @dummy, @dummy, @dummy, Language, Country, Content_rating, @dummy, @dummy)
;

and it loads just fine except for it gives the following warning, for n= 65, 239, 275, 280, 689, 817, 898, 924, 954, 1100, 1216, 1502, 1552, 1776, 1785, 1799, 1847, 1890, 1957, 2035, 2108, 2323, 2371, 2437, 2505, and 2606

  Row n was truncated; it contained more data than there were input
  columns.

the file I'm trying to load is movie_metadata.csv, and my table has columns
Title_id, Title_type, Primary_title, Content_rating, Start_year, Length, Language, Country, Fancenum_in_poster.

I found a solution for when every row comes back with this warning, but no solution was found for when it's only the occasional row.

How can I fix this and get rid of the warning? If I need to fix the file, how to I find each of those rows? The first row in my database doesn't seem to correspond with the first row in the file.


texts: What is the purpose of attaching a database?

texts:
I always see this option called attach database in SQL Server. Why would someone attach a database? Why not just restore a database from the .BAK file? I guess I don't see the purpose of it.

What would be the result of attaching it? It's quicker than a restore, but what is the technical result? Can you still alter the database? Add data to it? The preferred method or not recommend it?

I have seen
Attach/detach vs. backup/restore but nothing in there explains the purpose of attaching or detaching. It basically doesn't even touch on it.


texts: Sharing MySQL data folder between Windows and Linux

texts:
I have dual boot laptop with Windows 7 and Linux. I've been working for a long time on Windows programming in php / Apache / MySql. I share NTFS partition with Linux which is mounted using ntfs-3g, it is writable and works fine. I set up Apache on Linux with configuration that uses the same www directory as Windows. I have MySQL data in custom directory on Windows... what would happen if I install MySQL on Linux with configuration that points data path to the directory that I use on Windows - will this accually work?
Thanks :-)


texts:
Can someone Explain why i get only last row ?

where as when i remove DISTINCT i can able to achieve full rows.

DECLARE @liststr VARCHAR(MAX) = ''
;WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma
SELECT @liststr as Result


texts: Backup fails unless compression is enabled

texts:
I'm trying to back up a DB to a network share. The backup fails every time with the same error:

BackupIoRequest::ReportIoError: write failure on backup device '\\share_name\Backups\filename.bak'. Operating system error 64(The specified network name is no longer available.).

This happens on just one database out of 88 databases on the SQL server. All of the others are being backed up daily - to the same location - with no problems.

What I have tried:

Checked share/folder permissions (all ok)
Manual backup to a network share on a completely different storage system (same error)
Adding sesstimeout to the registry as suggested here (same error)
DBCC checkdb on the offending DB (no issues)

Backing up to local disk isn't an option because there's not enough storage available locally.

Now, the strange thing is, when I enable compression the backup magically works! Whereas if I don't, I see the backup file appear on the share and after about 2 minutes the file disappears and the backup fails with the error above.

I'm at a loss and looking for any ideas about what might be causing this.


texts: Using hard-coded host name in logon trigger

texts:
I need to write a trigger which prevents certain users from logging into the production database.
We use Active Data Guard.
If I hardcode the Linux hostname in the trigger to verify the host is production, then in case of "Role Change", the trigger may not work.
Correct?

This is a three-tier system. The end users communicate via an application server, which will use a generic name.
Here is a solution I had been contemplating implementing:

Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted).
Have a DB_ROLE_CHANGE trigger which will get activated if the production now runs from standby
The DB_ROLE_TRIGGER will modify the table so that the database hostname now reflects new production server hostname
The APPSERVER and APPUSER will match OK. But since the hostname is now production - it will disallow login.


texts: Would adding indexes to my foreign keys improve performance on this MySQL query?

texts:
Consider the following query:

SELECT
  `locations`.`id` AS `location_id`,
  `locations`.`address`,
  `locations`.`lat`,
  `locations`.`lng`,
  `tickets`.`status_id`,
  `customers`.`name`,
  `tickets`.`id` AS `id`,
  `tickets`.`updated_at` AS `updated_at`,
  ( 3959 * acos( cos( radians('39.78222851322262') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.16299560000004') ) + sin( radians('39.78222851322262') ) * sin( radians( `lat` ) ) ) ) AS `distance`
FROM `locations`
RIGHT JOIN `tickets`
  ON (`tickets`.`location_id` = `locations`.`id`)
LEFT JOIN `customers`
  ON (`tickets`.`customer_id` = `customers`.`id`)
WHERE `tickets`.`client_id` = '20'
AND
  (
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
  )
HAVING `distance` < '5'
ORDER BY `distance`
LIMIT 200;

Using a profiling tool, I got this report:

Speed: 45.569 ms
Query analysis:
· Query: SIMPLE on tickets · Type: ALL · Rows: 160 (Using where; Using temporary; Using filesort)
· Query: SIMPLE on locations · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1
· Query: SIMPLE on customers · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1 (Using where)

This is a MySQL database. All the tables are InnoDB with utf8_unicode_ci. The primary keys on each table are called id and are int(11) with indexes on them.

Should adding an index on tickets.location_id and/or tickets.customer_id and/or tickets.client_id improve performance of this query at all?
Why or why not?
Are there any other fields I should consider indexing to improve the efficiency of this query?
Should I be explicitly defining my foreign keys in my database schema?

My thinking is that since I'm selecting from locations first, I would want an index on the foreign keys that are being referenced. I read here: indexes, foreign keys and optimization that MySQL requires indexes on all foreign keys. Does explicitly defining the foreign keys in an InnoDB schema improve performance? Sorry for being such a total n00b. Thanks for the help!


texts:
Can I use blank dimensions in a fact table?

For instance, total sales in Florida are 200.

Sales    City    State
100      Miami
100      Tampa
200              Florida

Reason why is the total for the city may have metrics, like number of salespeople only available in city records. Total for state may have metrics, like expenses, only available in state, and no real way to break it down to cities.


texts:
I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When action = 1, ref_transaction_id will be 0
When action > 1 (other values are 2,3,4,5), ref_transaction_id will be a non-zero integer. It actually the transaction_id of a previous record in the same table.

I want to create index to support for this query:

select *
from transactions
where `created`>="?" and `created`<= "?" and `ref_transaction_id`=0 and `action`=1

I feel like a composite index like (action, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.


texts: text unique to the grain should be stored in fact table or dimension table?

texts:
I am modeling a star schema for user reviews using the yelp data set.
Each user review has a business dimension key, user dimension key and a bunch of data associated with the review.  All of the review data is numeric except for the field where they put in their text for the review (stored in a column named text).
Does it make sense to store that text data in the fact table since it relates to the grain of the fact?  Or should it be placed in a dimension table that grows it's rows at the same rate as the fact table?


texts: Do SQL Server Express Editions support CLR procedures?

texts: Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling  and running CLR Stored procedures and functions.


texts: Should I use XML for data storage on client side?

texts:
I have to create a supplementary program that runs offline on a client machine (normally windows 7 or higher laptop). Of course, I don't want to create a pre-requisite for this program. Since this runs offline, so I can not use a database server either.

This application will store about 500-1000 entry per record and a total of max 100 records.

So, I am trying to decide on the DB tech that works offline, and does not require extra installation on the client's side. Currently I am thinking of using XML to save the data. But I am sure, there are better solutions that I am missing.


texts: MongoDB Balancer:move data inside replication set or across replication set?

texts:
After reading the document of mongodb on the balancer topic , I have two questions:

1.If I set Balancer on , will the db operation efficiency will be
    influenced ?

2.Still ,I cannot understand what data is moved from where to where by balancer.I know that for each shard ,mongodb has a replication set to back up data.Then ,what does balancer do?It move data inside a replication set(from one replication set member to another ,but both belongs to the same replication set) or it move data from one shard to another
 shard(from one replication set to another)?


texts: Join two tables and return data and count in a single query

texts:
I have two tables in an SQL Server database, one with two columns and one with four:

tbl_email_list

email_list_id int (PK)
email_list_name varchar

tbl_email-details

email_uniq_id int (PK)
email_list_id int (FK)
email_address varchar
blacklist bit

I want to retrieve data in one query which should return

All the email lists from tbl_email_list;
The total number of email_address associated with a specific email_list_id;
The total number of whitelisted email addresses (where blacklist=0);
The total number of blacklisted email addresses (where blacklist=1).


texts: How to inspect a view's structure in information schema in PostgreSQL?

texts:
Is there a way to discover the structure of a view in a similar way to how tables are described by entries in information_schema.columns? I can also use the pg_* tables.
In other words, similar to how psql has \d which describes the structures of both tables and views, I'd like to access this information via SQL queries.


texts: mysql 5.5 corrupt - missing temporary table

texts:
One of our mysql instances crashed and failed to start:

120422 10:04:13  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

I've failed to understand what should I do in order to remove this temporary table from the TABLESPACE.

How should I proceed?


texts:
I have a database with a fairly large table. We want to run grafana queries against this data, and are thinking about dumping the data each day into a separate table/database to minimize performance impact on our main DB.  Ideally, we would have some daily replication dump data as such:

database1.table --> database2.table_hist

So my question is, would it be better to create a new schema (on the same server) in order to further segregate the datasets? Would we see performance impact on database1 if we're running long queries against database2.table_hist?


texts: How could I handle heavy load and consistency for product inventory in online retailers?

texts:
I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).

Hope you guys don't mind.

We want to design an e-commerce application, and we are mental about consitent stock numbers. We don't want our customers finding out, after they have bought an item, that that item is out of stock, that's a big thing here. The average order here has about 60 different items, which will makes things even trickier.

Let's imagine these two scenarios:

1st Scenario:

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket;

4) Customer C2 gets into the website and select the same item (put in the basket), which is still marked as "in stock" (stock is still 1);

5) Customer C1 goes to checkout and confirms his purchase and the application decreases the current stock for that item to 0;

6) Customer C2 keeps buying items, let's say 35 other distinct items (it took 20 minutes to customer c2 to select the items he wanted);

7) Customer C2 goes to checkout and confirms this purchase, but now, the first item he bought is no longer available (and we CAN NOT sell it);

8) The application warns customer C2 that the first item is no longer available and that he has to check his basket;

9) Customer C2 gets pissed and close the browser without buying anything.

2nd scenario (but I think it is unnecessarily complex and buggy):

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket (and the application decreases the current stock for that item to 0);

4) Customer C2 gets into the website and see the item he/she wanted is out of stock;

5) Customer C2 leaves the website;

6) Customer C1 keeps buying items (the stock decreases for it of these items);

7) Customer C1 closes the browser;

8) Every now and then some batch routine kicks in to remove the items which had decreased the stock but didn't get bought/confirmed.

We have just a few distinct products, but we have been selling about 30.000.000 items by phone, some products get sold as much as 2.000.000 every day, so the concurrency in the row responsible for the stock of that product might get many updates at the same time, so it's important we get a good performance.

Those are usual scenario, but is there any design pattern which gives the user a better experience while keeping the stock numbers consistent and yet yield a great application performance?

Any help will be much appreciated.

Cheers


texts: Select MaxDate where hh:mm:ss makes the difference

texts:
How to select the maximum date from a table where hh:mm:ss is selected?

My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned twice. What I want is to get the absolute maxDate.

   SELECT * FROM [MyDatabase].[dbo].[DogTags] t1
     JOIN
    OPENQUERY(LinkedServer, 'SELECT [TimeStamp]
    ,[Name]
    ,[Owner]
    ,[Address]
     FROM PETS
 WHERE TimeStamp IN (SELECT MAX(TimeStamp) FROM [LinkedServer].[PETS]
    GROUP BY [Name])')t2
    ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

This query returns the following table:

TAGS|TimeStamp              | Name  |Owner | Address    |
ROLF|2010-03-03 16:59:01.190| ROLF  |OLSEN | Juniper St.|
ROLF|2010-03-03 19:40:01.182| ROLF  |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD  |JOHN  | Maple St.  |

The records I want is:

TAGS|TimeStamp              | Name  |Owner | Address    |
ROLF|2010-03-03 19:40:01.182| ROLF  |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD  |JOHN  | Maple St.  |

Does anyone knows the solution how to filter the records to max time?

Thanks for the help.


texts: Linked Servers security context and transaction logging

texts:
I have many large logging tables that are populating with data constantly. The information is rarely or never read and I want to move them out of the database. So, the idea is to:

create linked server
create new database on the linked server with mirror logging tables
each night/several days/week a job will delete the records from the first database and insert them into the second one

The general goal is to reduce the first database size and put it on SSD drives and put the historical not queried data on slow storage.

The mirror tables will have less indexes and can be additionally compressed via row,page,column store or clr compressions. It will be working under simple recovery model.

I have never used linked servers and have the following questions:

If large amount of data is deleted from the first database and inserted into the second one, only the delete operation is logged in the transaction log file of the first database, right?
If I have two linked servers with many databases like this:

Server A             Server A1

Database A           Database A1
Database B           Database B1
Database C           Database C1

How can a restrict a user who has can query database A and A1, to query database B1 and C1?


texts: How to subtract the sum of columns from different tables?

texts:
I have two tables:

Withdrawals: account_id + amount
Prizes: account_id + amount

I want to calculate a balance per account, probably in a VIEW.

I could have two queries:

SELECT account_id, SUM(amount) FROM prizes GROUP BY account_id

which gives me the total prize per account, and:

SELECT account_id, SUM(amount) FROM withdrawals GROUP BY account_id

which gives me all withdrawals for these accounts.

Now, I'd like to subtract them to return balances for my accounts. What is the most efficient way to create a view like that?


texts: Is individual column and row-level encryption possible in SQL Server?

texts:
I know about complete column level encryption techniques and complete database encryption. But is it possible to encrypt individual rows and columns in SQL Server?

Thanks


texts:
I get a deadlock when a SQL Server Job runs. The deadlock occurs on a simple DELETE statement.
I would have thought there would have to be a SELECT/UPDATE query running to cause the deadlock? But looks like it is DELETE/DELETE deadlock...

What I am looking for is why I am getting a DELETE/DELETE deadlock. It is (to my knowledge) passing in different parameters.

Any ideas?
Thanks.

deadlock-list
2014-05-20 07:30:09.66 spid25s      deadlock victim=process409048
2014-05-20 07:30:09.66 spid25s       process-list
2014-05-20 07:30:09.66 spid25s        process id=process409048 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127294 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x397219620 lockMode=U schedulerid=5 kpid=3792 status=suspended spid=150 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process432e08 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=2648 ownerId=629859744 transactionname=DELETE lasttranstarted=2014-05-20T07:30:04.833 XDES=0x4c3426b50 lockMode=U schedulerid=6 kpid=5988 status=suspended spid=146 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:04.833 lastbatchcompleted=2014-05-20T07:30:04.820 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629859744 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process39ea562c8 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x13e0e4b50 lockMode=U schedulerid=2 kpid=7124 status=suspended spid=150 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2


texts:
When trying to run pg_basebackup I received the following error message:

pg_basebackup: could not set compression level -1 (null)

using the -z option. I received a similar error message when trying -Z #.


texts: Degree of parallelism and scheduler usage

texts: Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many parallel zones?


texts:
How do I achieve with MySQL to have peer to peer data shared between network? Let's say I have 5 different nodes of MySQL servers and would like the data to be shared peer to peer rather than master-slave or multi-master having all transactions data integrity in check.

If you can show steps or even guide to do this it would be really great.


texts: Is it possible to refresh PgAdmin 4 query without restarting a new query after disconnect?

texts:
Sometimes I leave a PgAdmin session alone for a while and when I come back to issue a query, I get an error

Server closed the connection unexpectedly.
        This probably means the server terminated abnormally
        before or while processing the request.
******* Error **********

So, I know I just need to reconnect but it seems like I have to close the query window and reconnect and open a new query window for things to work.

Is it possible to reconnect and use the same query window?

It has been suggested that I click the execute button again after the error comes, and PgAdmin will reconnect the instance and execute the query. However, that does not work. PgAdmin is not reconnecting no matter how many times I try to re-execute.


texts: How do I generate a tnsnames.ora file to connect to an Oracle Database?

texts:
I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find.

Do I need to generate the tnsnames.ora file myself? If so, where do I place it? If not, how does Oracle generate it for me?  If I do need to generate it, what is the appropriate syntax for the file?


texts: How to differentiate between SQL and PL/SQL?

texts:
I know the question might sound too stupid, but I never understood this part.

  SQL*Plus works with both SQL and
  PL/SQL. How do I know whether some
  code is SQL or PL/SQL? If my code has
  a for loop, is it not SQL anymore?

  PL/SQL is an extension for SQL to have
  loops, conditionals etc. Then any SQL
  code is by default PL/SQL code? Isn't
  it so?

Is there a demarcation between SQL and PL/SQL?

Two examples of differentiating b/w SQL and PL/SQL that triggerred this question:

What is the difference between these two create table statements?

https://stackoverflow.com/questions/2267386/oracle-11g-varray-of-objects/2267813#2267813


texts: How to select Overdue Rows with Date Frequencies?

texts:
+------------------------+--------+
|  Invoice_id | due_date | amount |
+-------------+----------+--------+
|  20         |2020-01-18|  1250  |
+-------------+----------+--------+
| 21          |2020-01-15|  1335  |
+-------------+----------+--------+

Get all Records with date passed n days and its multiple serires
like below...

for example n=5

SELECT * FROM `invoices`
WHERE `due_date = DATE_ADD(CURDATE() + INTERVAL 5 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 10 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 15 days)`

but i want to make it universal for any n value

  Note: mySQL version in my machine is 5.*


texts: Mariadb database with more than 50 million rows

texts:
I recently designed a database to analyze data using the clickhouse database as an engine.

Everything works perfect and the performance of the database is very good considering that the server is more or less good and with almost 40 million records.

Up to this point everything is fine, but now I need to migrate to a mariadb database in order to use the data in conjunction with the glpi database.

I've been investigating and I can't find anything that answers the following questions:

Is mariadb able to handle this amount of data, knowing that over
time we can reach more than 100 million?
Using mariadb galera cluster could you get a better performance?

I appreciate any suggestions or options you can give me.

  I'm sorry if you don't understand something, my English is not very good.

Thank you so much.


texts:
I need to implement an ETL to move from Excel to SQL Server some information. In Excel, users have some analytical functions that we would like to have also in SQL Server (sigh...)

Does anybody know if I can find these functions in SQL Server?

RSQ (calculates the r-squared value)
LOGEST (calculates an exponential curve)
LOGNORM.INV (returns the inverse of the lognormal cumulative distribution function given specified parameters: mean/average and standard deviation)
STDEV (calculates the sample standard deviation)
SUMIFS (sums values based on multiple conditions)
COUNTIFS (counts values based on multiple conditions)
SUMPRODUCT (returns the sum of the products of corresponding ranges or arrays)


texts:
I have the following 3 tables:

╔═════════════════╗
║   Companies     ║
╠═════════════════╣
║ id              ║
║ name            ║
║ type            ║
║ etc...          ║
╚═════════════════╝
╔══════════════╗
║   Contacts   ║
╠══════════════╣
║ id           ║
║ company_id   ║
║ first_name   ║
║ last_name    ║
║ etc...       ║
╚══════════════╝
╔═══════════════╗
║   Addresses   ║
╠═══════════════╣
║ id            ║
║ foreign_key   ║
║ address_1     ║
║ address_2     ║
║ city          ║
║ state         ║
║ zip           ║
║ etc...        ║
╚═══════════════╝

My problem is that both Companies and Contacts can have multiple addresses associated with them. Their ID numbers might be overlapping. What would I store in the foreign_key column in the addresses table?


texts: PostgreSQL function's input/output as JavaScript nested objects

texts:
I am looking for a simple/clean way that can be used consistently to automatically input JavaScript nested objects to user-defined PostgreSQL functions and also automatically output to JavaScript nested objects.

Shall I used nested composite types? JSON? other ways?
How to parse to/from these types and JavaScript nested objects automatically?
Any better thoughts?

Thank you.


texts: What happens if I can't rebuild indexes after setting PAGE_VERIFY to CHECKSUM?

texts:
I've come across a production SQL Server 2008 box that has quite a few databases with PAGE_VERIFY option set to NONE. I'm going to set the PAGE_VERIFY option to CHECKSUM. If I understand correctly, I must rebuild the existing indexes for CHECKSUM to affect them. Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well.

What will happen to the existing indexes if I can't rebuild them right away?
Are the existing indexes still usable after setting CHECKSUM without a rebuild?

Thanks!


texts: Table with clustered Index consuming max memory causing low PLE alerts

texts:
For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

Server: SQL server 2008R2

Databases : System and 2 user DB's with max size of DB being 54 GB

RAM: 16 GB

Max server system memory: 12.24 GB

Once i got a hint for memory pressure i used RAMMAP to find memory usage and found private process using 13.8 GB indicating a further doubt on lower memory pressure:

I analysed and found that one of the User DB A was using 8 GB RAM as per the blog from Paul Randal:Additionally i analysed for a period of time and saw  tempdb using at an avg of 3-4 GB of RAM

Then with further investigation i went and found that in that database A there was a table having a clustered index consuming on average 6-6.5 GB of the memory.( Fragmentation is not an issue as its been checked)

Now from here i am unsure how to proceed? should i go and create the missing indexes or can drop the unused? or there is something else i need to dig in before making any conclusion.

Please suggest!


texts: SQL Select a rolling count of total transactions over time

texts:
I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column.

When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.

So let's say I have a table like this:

TransactionID  | Date
---------------|-----------
             1 | 8-04-2014
             2 | 8-05-2014
             3 | 8-18-2014
             4 | 8-18-2014
             5 | 8-20-2014

I want to have a select statement that would provide me with something like

TotalTransactionsToDate | Week | Year
------------------------|------|------
                      2 |    1 | 2014
                      5 |    3 | 2014

If I could somehow also get the row 2 |   2 | 2014 meaning 2 Total, on Week 2 of 2014 that would be a nice bonus, but I don't require it.

I am using MySql 5.5.38


texts: Can we prevent table from update directly but allow update using trigger from another table?

texts:
So, let's say I have 3 tables:

stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty

What I want to do:

Prevent update stocks.stock value directly.
But allow update stocks.stock value by trigger from buy or sell table.

Is this possible? If it's possible, how? If its impossible, why?


texts: Could we delete old database and grid version?

texts:
We upgraded rac system and I want to ask, could I delete old grid and database version?

[oracle@orcl1 app]$ cd

11.2.0.3/     12.1.0.2/     Middleware/   oraInventory/

11.2.0.4/     grid/         oracle/       swlib/

I want to ask  can I delete manually "rm command line command"11.2.0.3/ and 11.2.0.4  grid files...  oracle/product/11.2.0.3 database ...

That's my inventory.xml

<?xml version="1.0" standalone="yes" ?>

<!-- Copyright (c) 1999, 2014, Oracle and/or its affiliates.

All rights reserved. -->

<!-- Do not modify the contents of this file by hand. -->

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>12.1.0.2.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME="OraDb11203_home1" LOC="/u01/app/oracle/product/11.2.0.3/dbhome_1" TYPE="O" IDX="2">

   <NODE_LIST>

      <NODE NAME="orcl1"/>

      <NODE NAME="orcl2"/>

   </NODE_LIST>

</HOME>

<HOME NAME="OraDb12102_home1" LOC="/u01/app/oracle/product/12.1.0.2/dbhome_1" TYPE="O" IDX="5">

   <NODE_LIST>

      <NODE NAME="orcl1"/>

      <NODE NAME="orcl2"/>

   </NODE_LIST>

</HOME>

<HOME NAME="OraGrid12102" LOC="/u01/app/12.1.0.2/grid" TYPE="O" IDX="4" CRS="true"/>

<HOME NAME="OraGrid11gR3" LOC="/u01/app/11.2.0.3/grid" TYPE="O" IDX="1" REMOVED="T"/>

<HOME NAME="OraGrid11gR204" LOC="/u01/app/11.2.0.4/grid" TYPE="O" IDX="3" REMOVED="T"/>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

EDIT:  I started deinstall tool in 11.2.0.3 .. I don'T want to delete grid 12.1.0.2 but I think it can delete 12.1.0.2's grid. right?

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0.3/dbhome_1
Oracle Home type selected for deinstall is: Oracle Real Application Cluster Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/12.1.0.2/grid
The following nodes are part of this cluster: orcl1,orcl2
Checking for sufficient temp space availability on node(s) : 'orcl1,orcl2'

## [END] Install check configuration ##

Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2015-11-02_08-53-27-AM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2015-11-02_08-53-29-AM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []:


texts: MySQL IS NULL / IS NOT NULL Misbehaving?

texts:
Please have look at this table:

mysql> desc s_p;

+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| id                      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| s_pid                   | int(10) unsigned | YES  | MUL | NULL    |                |
| sm_id                   | int(10) unsigned | YES  | MUL | NULL    |                |
| m_id                    | int(10) unsigned | YES  |     | NULL    |                |
| created                 | datetime         | YES  |     | NULL    |                |
| s_date                  | datetime         | YES  |     | NULL    |                |
| estimated_date          | datetime         | YES  | MUL | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Now Have a look at these queries:

mysql> select count(*) from s_p where estimated_date is null;
+----------+
| count(*) |
+----------+
|   190580 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from s_p where estimated_date is not null;
+----------+
| count(*) |
+----------+
|    35640 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from s_p;
+----------+
| count(*) |
+----------+
|  1524785 |
+----------+

The counts above are not matching. While as per my understanding:

Count with IS NULL and Count with IS NOT NULL should be equal to count when queried
without where clause.

Any idea on whats happening here?

===================================================

Update on 17th Feb 2012

Since, I found that a lot of people are asking about the kind of values estimated_date currently has. Here is the answer:

mysql> select distinct date(estimated_date) from s_p;

+----------------------+
| date(estimated_date) |
+----------------------+
| NULL                 |
| 2012-02-17           |
| 2012-02-20           |
| 2012-02-21           |
| 2012-02-22           |
| 2012-02-23           |
| 2012-02-24           |
| 2012-02-27           |
| 2012-02-28           |
+----------------------+
9 rows in set (0.42 sec)

As you can see above estimated_date either has NULL or a valid datetime values. There are no zeros or empty strings "".

Can this(original issue) happen if the index on estimated_date has some problem/s?

===================================================

Update on 18th Feb 2012

Here is the show create table output:

 | s_p | CREATE TABLE `s_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned DEFAULT NULL,
  `sm_id` int(10) unsigned DEFAULT NULL,
  `m_id` int(10) unsigned DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `estimated_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `sm_id` (`sm_id`),
   KEY `estimated_date_index` (`estimated_date`) USING BTREE,
  ) ENGINE=InnoDB AUTO_INCREMENT=1602491 DEFAULT CHARSET=utf8 |

Again, I can only suspect index on estimated_date here.

Also, the mysql server version is 5.5.12.


texts:
Source:

  Next, if you can give tempdb its own disk, then configure it to almost
  fill the drive. If nothing else will ever be on the drive, then you’re
  better off setting it to be larger than you’ll ever need. There’s no
  performance penalty, and you’ll never have to worry about autogrow
  again.

Doesn't tempdb fill the buffer pool, so wouldn't this have an affect on things like the page life expectancy?  Or does this only have an effect if tempdb is actually in use?

Another way of asking this question - does the size of tempdb matter to the buffer pool or does the used size of tempdb matter to the buffer pool?


texts: Create database before restore multiple files

texts:
I've several database, most of them have multiple files (both data and logs) and multiple diff and trn backups. I've already a restore script (scheduled and working on other server) that takes all the needed files and restores them through UNC path. There is a check at the beginning that verifies that the db exists, if not, throws an error. But I want instead to create the database and then restore it. I don't want to use the MOVE option on the RESTORE statement. I've made some searches and what I've found doesn't cover my needs.

So, how can I create a CREATE statement to match what I will restore on the next step? Maybe a silly question, but can't find how to do it.

Edit

From Aaron comment I get now why I can't prevent using MOVE. But then I should create the database with all the requiered filegroups, correct? Something like this:

CREATE DATABASE [Testdb] ON  PRIMARY
    ( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA]
    ( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA2]
    ( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_INDEX]
    ( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
    ( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB,
      MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO


texts: Replace part of strings in multiple columns and tables

texts:
I have several fields in db containing url inside different strings, part of which I need to replace with another one.
F.e. the string is

abc::http://localhost:5000/pic/img/1.jpg|def:::blahblahblah_http://localhost:5000/hij/klm/hxyhgbcbxjgxz.jpeg:jgfgv/hgh/

I have many different strings like that.

I need to replace http://localhost:5000 with https://example.com but leave everything else untouched.

Also I need to do that without stopping the database / on a working database.

I was thinking about doing smth like that:

UPDATE table01
  SET field01 = replace(field01, 'http://localhost:5000', 'https://example.com')

How can I make this query include all strings in db, not only in table01 & field01, but all tables & all fields? Just want to make sure that there are no references left to this url at all.


texts:
My PostgreSQL database version is

PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

In both databases, datestyle is set to"ISO, DMY".

ATTEMPT 1

Query :

insert into station(name , date) values('test' , '21/1/2016')

Error Log:

ERROR:  date/time field value out of range: "21/1/2016"
LINE 1: select '21/1/2016'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

ATTEMPT 2

Query :

select '21/1/2016'::date

Error log :

ERROR:  date/time field value out of range: "21/1/2016"
LINE 1: select '21/1/2016'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

On one system it works and on other system it doesn't. Both have Ubuntu and the same PostgreSQL version.

COLLATE SETTINGS ON BOTH SERVERS

  Query works successfuly on this server and has below COLLATE setting

show lc_CTYPE   -> C
show LC_CoLLATE -> C
SHOW lc_time;   -> "en_IN"
select '21/1/2016'::date   -> "2016-01-21"

  Query Does not Works on this server and has below COLLATE setting

show lc_CTYPE  -> "en_IN"
show LC_CoLLATE ->  "en_IN"
SHOW lc_time -> "en_IN"
select '21/1/2016'::date  -> above error log


texts: going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

texts:
Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like

<allData>
 <product1>
  <property1>
     <subProperty1>
     </subProperty1>
     [...other sub properties]
  </property1>
 <product1>

[...product2, product3,]

</allData>

Update/ picture for question clarity (Flow I am concerned with now is Right to Left):

Is this a good way to do it?  So we have XML Data coming externally, deserialize it into C# Objects in our Server app,  then I have to make a choice of how to store it on the SQL Database...
Should I use Varbinary Data type?   Then Deserialize it when updates occur in the User APPS?

Update:
For now I will use XML as my message payload to make it easier to read when debugging and testing, then when the project is just nearing completion, I will try to switch it to binary and marshal/ un-marshal data on both sides as I think there may be some performance advantages to this.


texts:
I created a table importing an Excel sheet with 600,000 rows. Most of the queries I will run will be using C_CustomerID, they will be like the example below:

select * from testtable where C_CustomerID = 12345678

The table definition is:

CREATE TABLE [dbo].[testtable](
    [id_card] [int] IDENTITY(1,1) NOT NULL,
    [C_CustomerID] [int] NOT NULL,
    [C_AccountID] [nvarchar](255) NULL,
    [C_ProductID] [varchar](20) NULL,
    .......... more columns here ...
) ON [PRIMARY]

I created an index:

CREATE NONCLUSTERED INDEX [inx_profitability] ON [dbo].[testtable]
(
    [C_CustomerID] ASC
)

I will only import more records later on from an Excel sheet. Will export about 500,000 new records every 6 months.

Is there anything I can do to increase performance in the future once table gets bigger?

I am running SQL Server 2012 and Microsoft SQL Server Management Studio 11.0.3128.0.


texts: MySQL InnoDB or MySQL NDB Cluster which one to use

texts:
We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other features if I go with it, will it impact performance?

Note:

Most of the tables in our database has foreign key relationship.
It is a B2B application.
This is one of the deployments.
The db size is 900 MB.
As customers grow the size is expected to grow.
Both outer as well as inner Joins are used frequently.
Host machine is Windows Server 2012
The present 8000 user count is expected to grow to 100000 within 2 weeks.
Queries per second with 8000 users = 110.086.

If not NDB Cluster, then what is the best way to bring in horizontal scaling? with less pain like auto sharding, auto replication,...etc.


texts: Is there a way to 'mark' a single row in a table and automatically unmark others?

texts:
Is there a way to flag one row in a database table (MariaDB/MySQL mostly, but I just want to know), so that there is only one row in a table that has that flag? If I set that flag (for eg. 'active') for any other row, then the row which currently has the 'active' flag is automatically unset. The idea is to not have to manually unset the older 'active' row when there's going to be a new one. I just insert/update a row with 'active'=1 and it's automatically the only one.

To clarify my intent, I'm not looking for a method of doing this manually - I'm trying to find out if there is a built-in feature in a database system, since this is a pretty common use case. Hence, the post in DBA, instead of SO. I don't even know what tags to use for this question.


texts: Better way of re-arranging the indexes for better performance

texts:
I ran a script to find overlapped and duplicate indexes for a database.

Below is the sample screen for the above said.

Please advise how can I arrange them or what should be the guidelines to make a better indexes before dropping them.


texts: How to retain good records when inserting from stored procedure and still catch the error message?

texts:
I am trying to catch the error message when inserting records into a temp table from a stored procedure without rolling back everything.  To illustrate, here is what I am trying to do:

CREATE TABLE #TEMPTABLE (some fields....)

BEGIN TRY
     INSERT INTO #TEMPTABLE
     EXEC SomeStoredProcedure @param1, @param2
END TRY
BEGIN CATCH
     ' Log error message in here
END CATCH

When executing just the INSERT INTO statement outside of a try catch block from within SSMS, I get a message similar to the following:

  Msg 8115, Level 16, State 2, Procedure SomeStoredProcedure, Line 337
  Arithmetic overflow error converting expression to data type int.
  The statement has been terminated.

BUT..., I also get some records inserted into #TEMPTABLE (perfectly fine for my requirements since I can't modify the existing SP but it still returns the data needed).

If I use the try catch block, I can capture the error message but #TEMPTABLE is empty.

Is there a way to capture the error message but still retain the records in #TEMPTABLE?

Honestly, I don't really need the try catch but this behavior got me curious.

Here is a sample code to reproduce the behavior I am experiencing:

-- SomeProc declaration
CREATE PROCEDURE [dbo].[SomeProc] AS BEGIN
CREATE TABLE #TEMP(ID INT NOT NULL)

INSERT INTO #TEMP VALUES(1)
INSERT INTO #TEMP VALUES(1 / 0)

SELECT ID FROM #TEMP END
GO

The following won't insert any records on #TEMPTABLE but will return error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

BEGIN TRY

INSERT INTO #TEMPTABLE
EXEC dbo.SomeProc END TRY BEGIN CATCH
SELECT @@ERROR END CATCH

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

The following will insert a record on #TEMPTABLE but will not return any error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

INSERT INTO #TEMPTABLE EXEC dbo.SomeProc

SELECT @@ERROR  -- returns 0

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE


texts: hStmt/cursor error caused by faulty data?

texts:
Could a  busy hStmt and invalid cursor errors be caused by faulty data?

ODBC and the software that connects to the database is correct and working - i have ran a test with a clean database (that has no user data in it yet and it worked).
I have also used scripts to move data from the old database to the working one.  Errors then appear in the new database.

I'm unable to locate any problem with the database itself - the tables are all there, procedures work, primary keys, options match.

I've even rebuilt several non critical tables.

However, since it is an interconnected database, simple copy-and-paste to the new database can easily result in some unwanted ID/serial mismatch and render the new database useless.

I've noticed this behavior: ID numbers seems to skip.  For instance, it jumped from 11 to 247 - and that table requires specific user input and over 200 "unfinished" queries to it etc - this doesn't seem to be reasonable.

The hStmt error seems to generally happen when a specific value is being selected by the server software, rendering the following steps useless.

Could it be caused by corrupted data?  When it selects, its just 1 short integer.


texts:
I have a few linux machines and each of them have a few Oracle databases. On each machine we have 2 shell scripts to run the backups. The TSM scheduler calls a script which calls a different backup script for each DB on the machine.

The problem is if I create an array of SIDs and create a loop through the SIDs the backup fails.

#!/bin/bash

LEVEL="$1"

declare -a SIDS=('SID1' 'SID2' 'SID3')
su - oracle

for SID in ${SIDS[@]}
do
  ORACLE_SID=$SID; export ORACLE_SID
  export ORACLE_HOME='/u01/app/oracle/product/10.2.0.2'
  ORAENV_ASK=NO
  . /usr/local/bin/oraenv
  LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
  /usr/local/adm/backup-incrn.sh $LEVEL $SID 35
done

There is no error in the Logs on the TSM server and it is almost as if the scripts is never called. The logs that are on the machine are not updated with any error messages.

If I use this syntax the backups work perfectly

su - oracle -c "/usr/local/adm/backup-incrn.sh $LEVEL SID1 35"

su - oracle -c "/usr/local/adm/backup-incrn.sh $LEVEL SID2 35"

I think this has to be an error in my script, some variable not being set properly.

Can anyone see the obvious mistake I've made and point it out to me?

Gísli


texts: Access SQL Server from a different subnet

texts:
I'm creating a program which accesses MS SQL Server 2008, the problem is that I can run the program smoothly as an administrator account, even on different subnet computers and using terminal service (mstsc).  But the program can not successfully access SQL Server if using another account, even though I've already included the account into SQL permissions, Terminal Service configuration, add the Administrators user group.

On SQL Server Network Configuration - Protocol - TCP/IP is Enabled.

What am I forgetting to do?


texts:
I have many schemas on mysql instance running on my development pc. I've noticed that mysql is taking a large amount of ram and cpu time.

Is there a way to "disable" schemas of project I'm not working for?

I like to avoid to drop schema and then re-import when I need it. I'm searching to a way for just disable them or something similar!

Thanks
Marco


texts: Shrink a large log file for a "Throw Away" database

texts:
I have a copy of my prod database on a development server.  I am using it to test a deployment script.

But when I run it, it says that the log file is full.

I checked and it is at 10 GB (Almost the size of the full database).

I don't need this database but for a few more hours (after that it is going to get dropped).

Is there any way to get rid of the log file gracefully?  (It seems wrong to just go delete it.)

NOTE:
I have run this:

select log_reuse_wait, log_reuse_wait_desc, name
from sys.databases
where name = 'ProdCopyDatabase'

And the result is:

log_reuse_wait  log_reuse_wait_desc    name
2               LOG_BACKUP             ProdCopyDatabase


texts: MySQL Fulltext indices slow after power outage

texts:
I have some large MySQL databases that have full text indices, and after a recent power outage, they are noticeably slower, and even failing on the larger ones. Previously, these queries were a couple seconds at most on the largest databases (>1B rows), by utilizing MATCH AGAINST and limiting results. Now, they time out with "FTS query exceeds result cache limit". This error does not occur on smaller databases, but they are still slow. Result sets are minimal, and there's no way that ~200 characters worth of result data should be hitting the cache limit which is much much larger.

Profiling shows 99% of time spent on "FULLTEXT Initialization" - best reference I can find is here. My DBs are read only, so they have no writes or deletions, so that didn't help.

I can't find any further information on FULLTEXT Initialization, or how to profile that state to figure out why it's so slow.

I also have two server that are essentially clones, and both suffer from this issue after the power outage.

I've tried everything I can think of, short of totally deleting the databases and reloading data, which would take a very long time. I'm at a complete loss as to why this would be. I've tried things such as: re-install mysqlserver, analyze, check, recreating indices, explain, and profiling, without learning much useful information


texts: Array to string sometimes concatenates fewer values

texts:
array_to_string(array_agg(array_to_string(array[
            highlights,exceptions,inter_note,inter_status::text
           ,comp_note,comp_status::text,hard_note,hard_status::text]
        ,'|#|'::text))
     ,'|*|'::text) as status

By executing the above query the status should contain 8 values separated by |#| or |*|. But in some cases it returns only 3, 4 or 6 values. I need to get all 8 values weather the record is empty or not.

Example:

  original array: [|#||#||#||#|Complete|#||#|Undefined|#||#|Undefined]

  some cases: [|#||#||#|PASS|#|Complete|#|Undefined|#|Undefined]

  another case: [Complete|#|Undefined|#|Undefined]

Any idea why some array elements are missing?


texts:
Show each role permission for each database.

I found a query in an answer, but it shows only for connect(in the same row with commas)

I want to show for every privilege. If I add in the were clause they will be grouped, combined different permissions.

select d.datname,
       (select string_agg(u.usename, ',' order by u.usename)
        from pg_user u
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_users
from pg_database d
order by d.datname;


texts: Are indexes need for parts of primary key?

texts:
In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause?
CREATE TABLE my_table (
    field1   INT NOT NULL,
    field2   INT NOT NULL,

    CONSTRAINT pk_my_table PRIMARY KEY (field1, field2)
);

example query ("get all the records where field2 = my_value"):
SELECT * FROM my_table WHERE field2 = my_value;

In other words should I also include:
CREATE INDEX ix_my_table_field2 ON my_table(field2);


texts:
I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do to speed up restart: 1) issue a normal checkpoint before shutdown checkpoint, 2) flush all dirty shared_buffers to
disk, 3) record the contents of the database cache prior
to shutdown and then warm the cache again immediately after restart. Therefore, the book seems to recommend:

psql -c "CHECKPOINT"
psql -c "select pg_cache_save('mycache')"
pg_ctl -D datadir -m immediate restart
psql -c "select pg_cache_warm('mycache')"

It also showed this method to:

pg_ctl -D datadir restart -m fast

Which one should I use when restarting a busy database which gets inserts very often? One of the above or is there a better method?


texts: Get distinct count from row and display in sepearte column

texts:
I am trying to get count of section type for each section id and course id

My sections table

╔════╤════════════╤═════════╗
║ id │ course_id  │ name    ║
╠════╪════════════╪═════════╣
║ 1  │ 1          │ Quants  ║
╟────┼────────────┼─────────╢
║ 4  │ 1          │ Verbal  ║
╚════╧════════════╧═════════╝

My levels table

╔════╤════════════╤══════════════╤══════╗
║ id │ section_id │ section_type │ name ║
╠════╪════════════╪══════════════╪══════╣
║ 1  │ 1          │ 1            │ aaa  ║
╟────┼────────────┼──────────────┼──────╢
║ 2  │ 1          │ 1            │ bbb  ║
╟────┼────────────┼──────────────┼──────╢
║ 3  │ 1          │ 2            │ ccc  ║
╟────┼────────────┼──────────────┼──────╢
║ 4  │ 1          │ 1            │ ddd  ║
╚════╧════════════╧══════════════╧══════╝

I need the following ouput

╔════╤════════╤════════════════════════╤═════════════════════════╗
║ id │ name   │ count(section_type =1) │ count(section_type = 2) ║
╠════╪════════╪════════════════════════╪═════════════════════════╣
║ 1  │ Quants │ 3                      │ 1                       ║
╚════╧════════╧════════════════════════╧═════════════════════════╝

This is the query i tried

SELECT a.name, a.id, count( DISTINCT b.section_type )
FROM sections a
INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY b.section_type


texts: Replicating some tables from one postgres database to another

texts:
I've got the following situation: I have three machines running postgresql databases. One machine holds client account information (call this machine C), the other two machines hold client logging information (call these L1 and L2). The reason for the split is to separate loading over multiple machines (so some clients send logging information to L1, some to L2 ... and maybe some time L3, L4, ...).

When retrieving logging information, in principal I'd like to be able to JOIN between logging tables on Ln and the client account tables on C. In reality I can't do JOINs like this (and even if I could, I'd want to avoid loading C).

My thought is to replicate the tables on C onto each of L1, L2, ... so that I can do the joins. As far as the tables from C are concerned, C is master and L1, L2, ... are slaves. But for the other tables on L1, L2, ... these machines are masters. Its not exactly master-master replication, and is it not exactly master-slave.

Can postgres (I'm running 9.1) replication be persuaded to do this, or if not are there any other packages that would do the job. In the last resort, I can write some code the periodically sync's the tables (I can tolerate some delay), but it would be nice not to!

Thanks in advance.


texts: What are the minimum persmission required to restore mysql dump

texts:
I'm trying to create a cronjob which will be populating the data on the server from a local machine. What are the best permission which are required to restore mysql Dump

I want to restore the dump on the web server I want to know what are the least privileges that A user on the Web server should have so that It can restore the database on the web server.

1) what server is the original database on?

MySQL Local Machine.

2) What mysqldump command do you run as what user to get the sql file?

mysqldump -u user -p pass dbname > db.sql "Here i don't have permissions issue."

3) What server are you restoring the dump file to? Or is it the same machine?

Its a Webserver machine resides in the cloud. And having MySQL server here as well. There is no issue of the local and web server MySQL version.

4) If not, how are you transferring the sql dump (i.e. which user account?)
mysql -hweb.server.ip -u restore_user -phalal db < db.sql

5) What command are you hoping to run on the second server to restore the database?

I am executing the command mentioned in the 4th question on the local machine. which restores the dump remotely.

Now  I am having issue of the restore_user permissions I don't want to give the maximum permission to the restore_user only the necessary permissions should be there as the data is much sensitive.


texts: SQL Server: Stored Procedure to add parent & child records

texts:
My SQL is pretty good, and I have programming experience in a number of languages. However, I have only dabbled in stored procedures for SQL Server.

I would like to write a procedure to add a parent record with child records in a related table. This is the sort of thing you would do to add an invoice in an invoice table and invoice items to an invoiceitems table.

I know the process would be as follows:

Create the invoice record
Get the primary key of the invoice record (presuming it’s auto-numbered)
Loop: add multiple invoiceitem records, with the foreign key set to the primary key in step 2 above.
(Possibly) update the parent invoice parent record with anything derived from the child records (such as a total).

I can handle most of that with what I already know. However:

how can I send the procedure the data to be processed?
inside the procedure how do I loop through the data?


texts: How to recover the database with old data?

texts:
I am using PostgreSQL. I have 4 database servers.  Each server has same database and same schema. Mistakenly I restored the database util05db backup onto the util01db database.

How can I get the util01db before restored?


texts: Oracle auditing on specific user activity

texts:
I want to be able audit user 'SCOTT' on 'SELECT, INSERT, UPDATE, DELETE' dml operations across all objects in the database SCOTT has access to.

I'm familiar with dbms_fga but I can't achieve what I'd like to using the add_policy arguments (it wants to be more fine-grained than I need it to be).

I've also tried switching on the audit_trail parameter:

alter system set audit_trail=os scope=spfile;
bounce
alter system set audit_file_dest='/u01/app/oracle/auditing';
audit session by SCOTT;

but all this seems to do is create files which log login/logoff activity (and doesn't seem to be restricted to SCOTT either).

Any ideas if it's possible to audit a specific user on all activity that takes place in their session?


texts: emergency-mode repair failed, nothing to do?

texts:
A production server got I/O disk error's I tried to fix it with dbcc but it got unexpected errors and crashed after a few minutes. so I took the file down and copied it over to a new machine.

I have no recent backup available fast, (its a online backup that takes very long to download).

File activation failure. The physical file name "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mydb_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
DBCC results for 'mydb'.

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3724880; actual 0:0). It occurred during a read of page (1:3724880) in database ID 5 at offset 0x0000071aca0000 in file '\mydb.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The emergency-mode repair failed.You must restore from backup.


texts: sp_WhoIsActive vs sp_BlitzFirst @ExpertMode = 1

texts:
I use sp_WhoIsActive and sp_BlitzFirst (with ExpertMode paramter set to 1) to get an overview of what is happening on the SQL Server in real time. I find these to be an upgrade on sp_who, sp_who2 etc...

Can anyone explain what, if any difference there is between the two. They seem to show the same thing, albeit with a slightly different set of columns. Also, BlitzFirst shows some other information in it's output as a separate output table

In this video, Brent shows his triage process in which he uses both sp_WhoIsActive and sp_BlitzFirst which makes me think there must be some difference but unless I missed something in the video I can't see why sp_WhoIsActive is required


texts:
I have a table with the following structure;

CREATE TABLE `operations_copy` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `zoneId` int(11) unsigned DEFAULT NULL,
  `machineId` int(11) unsigned DEFAULT NULL,
  `shiftId` int(11) unsigned DEFAULT NULL,
  `statusId` int(11) unsigned DEFAULT NULL,
  `actualCycle` float(10,4) NOT NULL,
  `targetCycle` float(10,4) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=185538661 DEFAULT CHARSET=latin1;

I have nearly 200m records in this table.

I am frequently performing the following queries on this table;

1 - Operations By Zone

SELECT zoneId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `zoneId`,`statusId`
ORDER BY NULL;

2 - Operations By Machine

SELECT machineId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId`
ORDER BY NULL;

3 - Operations By Shift

SELECT shiftId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `shiftId`,`statusId`
ORDER BY NULL;

I am currently looking at 30s for query #1.

I have enabled caching, which reduces the query time dramatically after the initial query, but I still want to optimise the initial query.

Studying has shown me that I need to make better use of indexes (ref: https://www.slideshare.net/billkarwin/how-to-design-indexes-really, MYSQL docs and more). So I want to check the following indexes will be the best to optimise each query;

#1

KEY `operations_by_zone_query` (`date`,`zoneId`,`statusId`,`id`)

#2

KEY `operations_by_machine_query` (`date`,`machineId`,`statusId`,`id`)

#3

KEY `operations_by_shift_query` (`date`,`shiftId`,`statusId`,`id`)

Any help will be much appreciated. Thanks.


texts: Handling this multi-threading scenario in the database?

texts:
I am working on sample bus ticket booking application,in I am facing these two multi-threading scenarios.

Scenario 1
1: Let's say there are two users, they both pick a particular seat and submit form. Now since a seat can be allocated to one user only, I can solve this race condition through select .. for update query along the lines of

select * from bus_seats where seat_id = x for update;
update bus_seats set user_id  = y where seat_id = x;

For first user it will lock the row and we can allocate the seat to him, and for the second user, the transaction will be blocked and when it gets unblocked, it will know that the seat is already taken , hence there will be no conflicts.

The other scenario which I am not able to resolve is this. Say we don't allow the users to pick their seats but instead pick the first seat randomly that is not taken. Say

select top * from bus_seats where user_id is null for update;

This should return a non taken seat and we can allocate it to the customer. However, say there are two concurrent users and 10 seats are available. In this scenario, there is a possibility, second user might get blocked because of same row being assigned to him as user1. But, since 10 seats are available, it should not happen.

So can anyone explain to me,how do I handle locking in the second scenario? Is using select..for update correct in this scenario?


texts: How to audit users connections via ODBC to all dbs in SQL Server 2008 R2

texts:
I am not a trained DBA, but I manage a software application that uses SQL Server for the back end. The applications manages "projects" and each project has its own database.

Because of the limitation in reporting through the application, we set up a generic SQL Server user account with read-only permissions to all the project databases. Users utilize that account to connect through ODBC and execute queries.

Now I would like to audit those connections - when they are happening and to which database.

Can anybody assist me with this?

I have administrative access to SQL Server itself. I have a very basic knowledge of SQL syntax. I am completely ignorant on the auditing capabilities of SQL Server, though.

Thanks in advance!


texts: Complex `UNIQUE INDEX` scenario - Games, teams, players, positions!

texts:
I have a table

CREATE TABLE (game, team, player, position);

If I create a unique index on (game, player, position) I get a table where in the same game, the same player can't play the same position for both teams. That's useful, but it's still possible for someone to insert a row where the same player plays two different positions, one on either team.

Is it possible to write some sort of unique index or constraint that makes it impossible for the same player to play for both teams in a game?


texts: SQL Data Warehouse - Is it possible to switch a whole table to a partition?

texts:
Probably a stupid question, but I wanted to know if it was possible to switch a whole table into a partition of a larger table?

I'm guessing not, as check constraints aren't available to constrain the table on the partition boundary.


texts: How to find physical_device_name on backup by extending great query from Ozar?

texts:
Found this on an older blog post at BrentOzar.com:

SELECT  d.name, MAX(b.backup_finish_date) AS last_backup_finish_date
FROM    master.sys.databases d WITH (NOLOCK)
LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name AND b.type = 'D'
WHERE d.name <> 'tempdb'
GROUP BY d.name
ORDER BY 2

Pretty great query!

Question: is there is an elegant way to join on msdb.dbo.backupmediafamily and return the physical_device_name for those databases that DO have recent backups? Result set should still return the ones without backups.

My attempts thus far stink :-)

Link to blog post:
https://www.brentozar.com/archive/2012/03/how-talk-your-boss-about-world-backup-day/

Thanks!


texts: How can i tune query without creating index

texts:
I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not willing to create any additional index due to Very large table size and about 8 indexes already existing.

Actual table is partitioned and has over 4 billion rows and is 5 TB in size. I am using the demo to reproduce the behavior for below table def:--

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
    [DemoID] [int] IDENTITY(1,1) NOT NULL,
    [SomeData] [sysname] NOT NULL,
    [Lastseen] [datetime] NULL,
    [DataKey1] [char] NOT NULL,
    [DataKey2] [char] NOT NULL,
    [RandomColumn] [nvarchar] NOT NULL,
    [CaptureDate] [datetime] NULL,
    CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED
    (
        [DemoID] ASC,
        [CaptureDate] ASC
    )
    ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

None of the available indexes in the database has the columns as key columns or in included part of the index.

Query run is below

SELECT distinct RandomColumn,Lastseen from  [dbo].[DemoPartitionedTable]
WHERE
Lastseen >='2019-09-26 00:00:00.000'
and Lastseen<'2019-10-04 00:00:00.000'

The plan for above is

The plan actually on my server with massive data on that large table makes the query go parallel and has Re partition stream before hash match operator in above plan and gather streams after that. From estimated plan i see estimated rows is over 65 million .

I tested in dev with index which definitely make this faster by creating leading column as last seen and random column in included.

But is there a way i can make this faster without adding index, changing anything to query?


texts: Can I refactor this query to get it to run in parallel?

texts:
I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). The server has access to 8 cores.

  UPDATE dbo.Deidentified
     WITH (TABLOCK)
  SET IndexedXml = dbo.ReplaceMultiWord(IndexedXml),
      DE461 = dbo.ReplaceMultiWord(DE461),
      DE87 = dbo.ReplaceMultiWord(DE87),
      DE15 = dbo.ReplaceMultiWord(DE15)
  WHERE InProcess = 1;

and ReplaceMultiword is a procedure defined as:

SELECT @body = REPLACE(@body,Names,Replacement)
 FROM dbo.NamesMultiWord
 ORDER BY [WordLength] DESC
RETURN @body --NVARCHAR(MAX)

Is the call to ReplaceMultiword preventing forming a parallel plan? Is there a way to rewrite this to allow parallelism?

ReplaceMultiword runs in descending order because some of the replacements are short versions of others, and I want the longest match to succeed.

For example, there may be 'George Washington University' and another from 'Washington University'. If the 'Washington University' match were first, then 'George' would be left behind.

Technically I can use CLR, I'm just not familiar with how to do so.


texts: Google cloud MySQL replication with partition

texts: I recently altered a table in MySQL 8.0 to with new Primary key and  created partitions with them to improve the query performance. which worked fine in main instance. My assumption was that the Partitions and primary key will get transferred to the read replica as well. But It didn’t. even the table structure in the read replica shows the old primary key. will there any significant delay in getting this transferred?


texts: Postgres ignoring wal_level setting in conf

texts:
I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for wal_level in the configuration file.

I have set in the config file:

wal_level = hot_standby
max_wal_senders = 5

However when I went to start Postgres I ran into the error:

  WAL streaming (max_wal_senders > 0) requires wal_level "archive", "hot_standby", or "logical"

If I set max_wal_senders = 0, the server starts fine, but running "show all" in psql shows that wal_level = minimal.

 -bash-4.2$ grep wal_level postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical

-bash-4.2$ psql -c 'show "wal_level";'
 wal_level
-----------
 minimal

Any idea why it would be ignoring the configuration value?

Nothing of note in the Postgres error log:

Oct 13 11:03:53 postgres[12324]: [3-1] LOG:  database system was shut down at 2016-10-13 10:55:35 UTC
Oct 13 11:03:53 postgres[12324]: [4-1] LOG:  MultiXact member wraparound protections are now enabled
Oct 13 11:03:53 postgres[12328]: [3-1] LOG:  autovacuum launcher started
Oct 13 11:03:53 postgres[12321]: [3-1] LOG:  database system is ready to accept connections

Just the one wal_level config line and archive_mode is enabled and configured to rsync archive files to the slave. I've uploaded the config here: http://pastebin.com/Vi3vByc1

I've checked and it's definitely loading the correct config file.

-bash-4.2$ psql -c 'show config_file;'
             config_file
--------------------------------------
 /data/pgsql/9.5/data/postgresql.conf
(1 row)

-bash-4.2$ grep wal_level /data/pgsql/9.5/data/postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical


texts: Use Group Managed Service Account for SSRS

texts:
We have a production environment running SQL Reporting Services 2014. The service is running as a group managed service account domain\SSRSProdSvc$
We are now trying to change our test environment to use managed service accounts for SSRS, however I can't change it.
If I go into Reporting Services Configuration Manager and try and change the service account I get an error

Setting Windows Service Identity to Windows Account
The Windows account information used to run the Windows service has failed to update. Previously designated settings are being used instead.

If I go into Windows Services I can change the account to domain\SSRSTestSvc$ and the service starts, however in the Reporting Services Configuration Manager it still comes up with the previous domain account.
I'm not sure how it was configured on Live, but when I look at the Reporting Services Configuration Manager it's service account is domain\SSRSProdSvc$
Any ideas how to fix test?


texts: How do I prevent PostgresQL users/roles from having access to any administrator functions?

texts:
When I create a user with CREATE ROLE user123 LOGIN on my system it is able to login and immediately perform \du and interact with pg_stat_activity, and probably many other things.

I have programmatically connecting software that should be performing a specific task. I don't want it to be able to do anything else, including spying on the connections, userIDs, user permissions or other things.

How do I prevent all unspecified access?


texts: Mongo Collection `Size` is *larger* than `storageSize`?

texts:
I recently compacted my collection using the command:

 db.<collectionName>.runCommand( "compact" )

And now my collection size seems to be bigger than size on disk!

SECONDARY> db.<collectionName>.stats()
{
"ns" : "<databaseName>.<collectionName>",
"count" : 2937359,
"size" : 5681676492,                   # 5.6 GB
"avgObjSize" : 1934.2805874256433,
"storageSize" : 4292853728,            # 4.2 GB
"numExtents" : 2,
"nindexes" : 2,
"lastExtentSize" : 2146426864,
"paddingFactor" : 1.669999999836597,
"flags" : 1,
"totalIndexSize" : 220735648,
"indexSizes" : {
    "_id_" : 162326304,
    "e_1_" : 58409344
},
"ok" : 1

}

I don't understand how this is possible. Aren't all mongodb collections backed-by-disk at all times?

Can anyone explain these results?


texts: MySQL privileges for roles aren't applying to users?

texts:
I've done what I consider to be sufficient research with no answers to justify posting this question here.  I am attempting to grant users permission to execute certain stored procedures.  And because I have to grant permission for several of these roles, but not all of them, I've chosen to do this using roles.  But when I grant one of these roles to a test user and try to call a stored procedure, I see this error message:

1370 (42000): execute command denied to user 'me'@'localhost' for routine 'db.MyProc'

The SQL script to create a role looks like this:

CREATE ROLE 'MyRole';
GRANT USAGE ON db.* TO 'MyRole';
GRANT EXECUTE ON PROCEDURE db.MyProc TO 'MyRole';
-- grant execute on more procedures
FLUSH PRIVILEGES;

And then I grant the role to the test user and set it as default:

GRANT MyRole TO 'me'@'localhost';
SET DEFAULT ROLE MyRole TO 'me'@'localhost';
FLUSH PRIVILEGES;

Naturally, I do all of this as root.  I think that the grants have worked because of the output of SHOW GRANTS.

 SHOW GRANTS FOR MyRole;
+--------------------------------------------------------------+
| Grants for MyRole@%                                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `MyRole`@`%`                           |
| GRANT EXECUTE ON PROCEDURE `db`.`MyProc` TO `Contributor`@`%`|
|(grant execute on more procedures)                            |
+--------------------------------------------------------------+

SHOW GRANTS FOR 'me'@'localhost';
+---------------------------------------+
| Grants for contrib@localhost          |
+---------------------------------------+
| GRANT USAGE ON *.* TO `me`@`localhost`|
| GRANT `MyRole`@`%` TO `me`@`localhost`|
+---------------------------------------+

I have found no one else with this problem.  And nothing I read from MySQL documentation suggested that this would not work.  What is going wrong?

I'm using MySQL 8.0.11 on Windows 10.


texts: Why is SQL Server Recommending I Create this Index?

texts:
I have the following query:

Select SubId, Max(ReadTimeLocal)
From dbo.PanelWorkflow
Where ProcessNumber IN (802,1190,1605,1620,1645,1660,1695,1790,1990,2690,2795,2990,3090,3290,3590,3790,4190,4390,4590,5000,5200,5400)
Group By SubId

I have the following index on the dbo.PanelWorkflow table:

CREATE NONCLUSTERED INDEX [IX_PanelWorkflow_ProcessNumber_Lineage] ON [dbo].[PanelWorkflow]
(
    [ProcessNumber] ASC,
    [Lineage] ASC
)
INCLUDE ( [SubId],[ReadTimeLocal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The query plan from SSMS looks as follows:

SSMS is recommending  I create the following index (with an impact of 54.3)

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[PanelWorkflow] ([ProcessNumber])
INCLUDE ([SubId],[ReadTimeLocal])

Does anyone know why it's recommending I create an index that essentially exists already, and is not using the one that does exist?

There are 830 million rows in the table. Using SQL Server 2012.


texts: Storing currency with variable floating point in SQLite

texts:
We are working with currency and handle currencies from various countries. As a result, we may work with currencies that require no decimal values, two decimal places, three, sometimes four.

We also store taxes that are derived from individual items, and we usually store them with higher precision than the currency specifies (eg: taxes for Canadian dollars would be stored to 4 decimal places)

What would be an appropriate way to store these values? I have read that many people store them as integers in the lowest denomination (eg: dollars would be stored in cents), but the 4-decimal taxes would not work out very well.


texts: Creating Symmetric Key from external provider

texts:
Knowing the security objects hierarchy, we can see that in order to create a Symmetric Key and encrypt data, we need to create:

Database Master Key which is protected by password
Certificate which is protected by the Database Master key
The Symmetric Key itself, which is protect by the Certificate

In Always Encrypted, we can use EKM module to simplify this hierarchy. For example, we can store the certificates in the Windows Certification Store and these certificates are protecting our encryption master keys.

I am wondering, if I want to use not Always Encrypted built-in encryption functionalities, can I use EKM module to create and manage my symmetric keys (like it is shown on the diagram). In the CREATE SYMMETRIC KEY documentation we have provider option, but not enough information about possible providers and examples.

I am interesting in storing the Certificate which is protecting the Symmetric keys or the Symmetric keys in external storage, because in such way the data is separated from the keys and in the database we are storing only references to the keys (like in always encrypted). Windows Certification Store will be best option for me, but Azure Key Vault or something else will work as well, I guess.


texts: SQL Server: how to monitor activity in several databases

texts:
I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

number of users accessing the db per day
number of inserts per table per day
number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.


texts: Network connection from old application to SQL Server 2008 R2 lost once in a while

texts:
I have put this question on serverfault.com as well, but since the problem concerns connecting to SQL Server, I will also put it here.

We are trying to migrate an Axapta 2.5 database from SQL Server 2000 to 2008 R2. We are testing performance etc. on the new server, but once in a while, these two events are thrown on the Axapta server:

  Object Server SandBOX: The database reported (session 11 (CSS)):
  [Microsoft][ODBC SQL Server Driver]Communication link failure. The SQL
  statement was: ""

and

  Object Server SandBOX: Fatal system condition - quitting for restart
  due to lost database connection.

after which all clients have to reconnect.

The Axapta 2.5 server is a virtual server (VMWare) running Windows 2003 SP2.
Info from vSphere:

VM Version: 7
CPU: 1 vCPU
Memory: 1024 MB
VMNetwork: VMXNet 3

The SQL Server is also a virtual server (VMWare) running Windows 2012 R2.
Info from vSphere:

VM Version: 8
CPU: 4 vCPU
Memory: 73728 MB
VMNetwork: VMXNet 3

There are no traces of an error in either the event log on the SQL Server or in the SQL Server Logs.

When searching the internet for a solution, I only found one with this exact problem, and it had no answer (how to troubleshoot sql 2008 dropping connections on sqlservercentral.com). I mostly see suggestions around the virtual network adapter or settings on the virtual server, for more or less similar problems.
I'm probably forgetting a lot of information, but I'm actually a DBA, so bear with me.

I know very little of virtual machines and network. Thanks for your help.
Could anyone point me in a direction with this issue?


texts: Table size grows indefinitely after deleting and then inserting the same data

texts:
I create a table and populate it with data:

create table tab
as
select 'value' || level val
from dual
connect by level <= 10000;

If I then delete all rows and insert the same data:

delete from tab;

insert into tab
select 'value' || level val
from dual
connect by level <= 10000;

commit;

The table size increases every time delete-then-insert is performed. Executing these operation multiple times causes the table to grow to many times it's original size.

I would expect the size to remain (approximately) the same. Why is this happening?

A delete marks space as free, it doesn't actually shrink the segment. A subsequent insert should first use available empty blocks below HWM.

I can recover the space using alter table tab shrink space.

I tested in Oracle 12 and Oracle 18c.


texts:
Im attempting to deal with a corrupted database with little success, when I try to alter this table I am giving the error
 Column count of mysql.proc is wrong

Looking up this error online gives me many results all saying the same variation of, you must run
mysql_upgrade

However when I run that I am greeted with
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are  now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

However again another problem occurs any time I try to log into mysql from the command line, all attempts to log in respond with
Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

This does make sense as localhost/phpmyadmin works just fine and I can create new tables or databases but I just cant alter the corrupt database
Any help is appreciated this is XAMPP on Ubuntu


texts: Amazon Aurora MySQL conditional comment queries

texts:
How does Amazon Aurora handle conditional queries based on MySQL version? I was unable to find any documentation on this.

For example, the code below inserts into a database for MySQL 5.6.4 or newer. What is result in Aurora?

I could spin up an instance but wanted to read the documentation about this to understand what incompatible features there are.

/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('supports_full_text', '1')*/;