texts: How to access MS SQL Server databases securely without VPN

texts:
We have an application that must directly access an MS SQL database. The database is in a remote location. Therefore, at the moment, we use a VPN connection which works fine and appears to be safe. However, setting up a VPN connection for each new client is very troublesome.
Changing the application to use API call or anything similar is not possible.

Is there a way to securely open MS SQL server ports to access the MS SQL DB without using VPN? Or any other suggestion to solve that problem?


texts: Incremental backup in postgresql in windows

texts:
I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis.
I have gone through many related articles (like https://coderwall.com/p/jxebnw/incremental-backups-for-postgresql) but i found that postgresql database does not support incremental backup independently. It means that the postgresql needs third party tools like pg_rman in order to get incremental backup. Also the pg_rman tools only works on UNIX not windows.

Is it really so? I mean its hard to believe that the database like postgresql do not support incremental backup.

So my question is do postgresql support incremental backup independently? If yes then what is the procedure for achieving it.


texts: Determining Functional and Transitive dependencies from Relational Model (with no sample data)

texts:
I have a relational model which I've converted from an EER diagram...
I've been asked to find the functional and transitive dependencies but how does one do that when there's no sample data to start from?

My relational model is as follows:

Product(productID, name, manufacturer, description, categoryID, supplierID, qtyDescription, unitPrice, status, availableQty, reOrderLevel, maxDiscount)
Primary Key productID
Foreign Key categoryID references Category(categoryID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Category(categoryID, name, description)
Primary Key categoryID
Alternate Key name

Quotation(quoteID, quoteDate, empID, quoteValidity, supplierID, description, productID, qty, unitPrice)
Primary Key quoteID, productID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Supplier(supplierID, name, address, phoneNo, faxNo, contactPerson)
Primary Key supplierID

SupplierOrder(supplierOrdID, orderDate, quoteID, description, productID, qty, unitPrice, orderTotal, status, orderReceiveDate, orderPaymentDate, PaymentRefNo)
Primary Key supplierOrdID
Alternate Key PaymentRefNo
Foreign Key quoteID references Quotation(quoteID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Employee(empID, name, gender, positionID, phoneNo, homeAddress, homePhone, dateOfBirth)
Primary Key empID
Foreign Key positionID references Position(positionID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Position(positionID, title, hourlyRate)
Primary Key positionID

Payslip(PayslipID, empID, startOfPayPeriod, endOfPayPeriod, noOfDaysWorked, hourlyRate, totalPay)
Primary Key PayslipID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE

CustomerOrder(customerOrdID, empID, customerID, orderDate, productID, qty, unitPrice, discountGiven, overDueFees, cancellationFees, totalAmtDue, billingDate, dueDate, status, description)
Primary Key customerOrdID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key customerID references Customer(customerID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Customer(customerID, companyName, address, phoneNo, faxNo, emailAddress, contactPerson, gender,
maxAvailableCredit)
Primary Key customerID

CustomerPayment(paymentRefNo, customerOrderID, paymentDate, amtPaid)
Primary Key paymentRefNo
Foreign Key customerOrderID references CustomerOrder(customerOrdID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Any assistance would be greatly appreciated... I just need an understanding of whether it is possible, and if so, how? A few examples from the model would be great, and I can determine the rest through application of the theory behind the examples.


texts: Count Function with Aggregate function inside the query

texts:
Hi my actual code is below:

Select M.TicketID,M.CreatedMoment, Max(L.StatusChangeMoment)AS StatusChangeTime,
Elapsed_time_in_Hours_Minutes = CONVERT(NUMERIC(18,2),DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment))/60+(DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment)) % 60/100.0))
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc

And the Partial Results is this:

TicketID    CreatedMoment   StatusChangeTime    Elapsed_time_in_Hours_Minutes
201408231   8/23/14 8:05 AM 8/25/14 11:47 AM    51.42
2014082310  8/23/14 8:19 AM 8/23/14 12:43 PM    4.24
20140823100 8/23/14 8:38 AM 8/24/14 11:15 AM    26.37
20140823101 8/23/14 8:38 AM 8/23/14 11:58 AM    3.2
20140823102 8/23/14 8:38 AM 8/24/14 10:33 AM    25.55

Basically the statuschangetime came from aggregate function, and the last column is the difference of the 2nd and 3rd column.

I want to modify the query so the results will look like this:

Date          below24Hrs    above24hours
2014-8-23        2              3

My problem is i'm getting error when running this code:

Select
[below24hrs] = COUNT (Case WHEN (CONVERT(NUMERIC(18,2),DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment))/60+(DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment)) % 60/100.0))) < 24 THEN 1 END)
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc

It says cannot count with the MAX aggregrate function inside the query.


texts:
MySQL:

If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer?

That'll leave gaps in primary key field of such tables. Would that result in slower queries? Also, will mysql attempt to use up the gaps by inserting new items in middle (which I don't want)?


texts: Postgres fast update on non-indexed column

texts:
Step 1

We have a delete query of the form that we are trying to speed up:

DELETE * FROM table_name
WHERE col_name in ('a','b',....'zzzz');

The operation deletes between 0.5-50% of the mass of the table. col_name is an indexed (non-unique) column.

This ran extremely slowly because each delete affected the index.

Step 2

We used a non-indexed tombstone boolean column called deleted with a DEFAULT FALSE. Our query now became:

UPDATE table_name
SET deleted = TRUE
WHERE col_name in ('a','b',....'zzzz');

This definitely runs quicker (60-200%), but seems to ignore the col_name index for large IN clauses. However, since the update only applies to an unindexed column, it is fast.

Step 3

We replaced the conditional to be:

UPDATE table_name
SET deleted = TRUE
WHERE col_name = 'a'
    OR col_name = 'b'
    OR ...
    OR col_name = 'zzzz';

Even though this utilizes the index, it runs at about the same speed as the DELETE from Step 1.

Is there a fast way to delete (or mark as deleted) a number of rows based on membership within a very large IN clause?

The database needs no concurrency handling as it is accessed by a dedicated single-threaded application.

Note: Individually performing the deletes/updates was an order of magnitude slower. The IN clause generally has between 20000 and 5 million elements.


texts:
I want to give an external user (new login) read access to part of the data model from my Database. The user will copy the data model and content from my Database, and then integrate that into their own database.

Should I give the user a read access to the actual tables, or should I create views for the Tables they need to see?
If a View is the way to go, can I put unrelated tables in the same View?


texts:
We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data which has been lost.
While we were able to get the server to boot, mysql still refuses to load. The server still produces a lot of errors on startup, so I have created a new server (Ubuntu 8.10) and copied the mysql directory across to this - but again mysql will not start.

Attached is a dump of the errors shown when running mysqld --verbose, with the innodb_force_recovery=6 option .

Sometimes while running this check, it will pass the sanity check, while other times it will fail.

I have found plenty of articles on how to recover from a crash, but all assume that you can actually get mysql to start - which is not the case here. Additionally i have tried all innodb_force_recovery options, from 1-8, and none of them allow mysql to start.

Can anyone advise on how to go about recovering the data? If I browse the ibdata1 file I can see most of the data (there appear to be a few rows missing). Are there any tools that can read the ibdata1 file and extract data row by row?

All help greatly appreciated - as I am starting to fail my sanity check!


texts: Window Function to get pairs of records and compute the time difference

texts:
I'm working with an event dataset as the one in the example below:

Event_Type  Event_Timestamp         Is_Active
A           2010-10-01 00:00:00     1
B           2010-10-01 00:00:01     1
A           2010-10-01 00:00:02     0
D           2010-10-01 00:00:03     1
B           2010-10-01 00:00:04     0
C           2010-10-01 00:00:05     1
A           2010-10-01 00:00:06     1
A           2010-10-01 00:00:07     1
A           2010-10-01 00:00:08     0

The dataset is ordered by the timestamp of the events and the data grows as events happens in real time. More event types could be included in the dataset at any time (not limited to only A, B, C, D as in the example) and the same event type could appear in the table multiple times. The Is_Active boolean field serves as a way to indicate whether the event is still active (1) or not (0).

In this sense, I tried to do some transformations on this data by using SQL Window Functions. I'm not necessarily restricted to a specific product or technology, so feel free to tell how you would address the problem below.

What I want to do is dynamically pair each event of the same type when they have opposite Is_Active values and then get how long that event was active. In other words, given an event X, I would need to get the Event_Timestamp for it when Is_Active had 1 for the first time (Begin_Timestamp), and then ignore the rest of the rows for this event X with Is_Active 1 until I get Is_Active 0, so I could take the Event_Timestamp again (End_Timestamp). Then, I would continue applying this logic when I find event X having 1 in the Is_Active column again.

An example of the resulting table would be:

Event_Type  Begin_Timestamp         End_Timestamp           Duration
A           2010-10-01 00:00:00     2010-10-01 00:00:02     2 seconds
B           2010-10-01 00:00:01     2010-10-01 00:00:04     3 seconds
D           2010-10-01 00:00:03     null                    null
C           2010-10-01 00:00:05     null                    null
A           2010-10-01 00:00:06     2010-10-01 00:00:08     2 seconds

Is there any window function that would help me get these pairs of events so I could compute the duration of each event?


texts: In SQL Server, what are the typical symptoms of parameter sniffing?

texts:
There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of canonical answer as to what the symptoms of parameter sniffing are. So that's the question:

  What are the typical symptoms of parameter sniffing that would lead to its diagnosis as a potential culprit in performance?


texts: Postgres PLPGSQL 'un-stored' procedures?

texts:
I'm getting the following error when I run the query below:

ERROR: query has no destination for result data Hint: If you want to
discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL
function inline_code_block line 12 at SQL statement

I don't want to discard the results though! I want to display them. What's happening here; is Postgres refusing to provide results from a block that isn't a UDF or stored procedure? Or some other syntaxy thing?
DO
$$
declare
    tenant text;
    result1 integer;
    result2 integer;
BEGIN

tenant='mycustomer1';
EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result1;

tenant='mycustomer2';
EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result2;

select result1, result2;

END;
$$ LANGUAGE plpgsql;

Background: I'm trying to create a set of dynamic queries that can get values from multiple PG schemas to return in a single report.
Context: I'm using Metabase to run the query, with a read only connection talking to RDS / Postgres. My plan is to store the queries as Metabase items. I was originally going to try creating a temp table to put my results in, but I'm not allowed to CREATE anything, including UDFs and stored procedures, hence my thinking of this as an un-stored procedure.


texts: Wide clustered index vs multiple narrow nonclustered indexes?

texts:
Say I have a contrived Student table like so:

CREATE TABLE Student (
    Id IDENTITY INT,
    SchoolId INT NOT NULL,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
)

Instinctively, I'd make Id the Primary Key (and thus the clustered index). However, I'd find myself searching by SchoolId so I'd make a nonclustered index on SchoolId.

How would this fare against having the Primary Key (and clustered index) to be SchoolId, Id? I will always have the SchoolId if I need to search by Id, so I'll get to use the clustered index anyways, and if I need to search by SchoolId only, the records will be physically next to each other.

If I were to do any type of searching or batch updating, they'd be on SchoolId specific records, e.g. find all kids with name/number/whatever at SchoolId. I'd never do these types of operations across multiple SchoolIds in the same transaction. Does the benefit of having these records physically next to each other make this method much better than simply having a clustered index on Id?

Are there massive downsides to using the latter? I'm still new at this and there's plenty of topics I don't fully comprehend yet (e.g. fragmentation) and how it would factor into a situation like this.


texts:
My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system.

First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg.

Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows

mysql-5.5.28-osx10.6-x86_64.pkg -> I double-clicked it to run, after clicked buttons a few times it's said to be successfully installed. However, I could not see anything in Applications.
MySqlStartupItem.pkg -> I double-clicked to run, also successfully done. However, I still could not see anything in Applications.
MySQL.prefPane -> I double-clicked it to run. Now I see a window jumped out, with a button Start/Stop MySQL Server, I clicked it, now it says "The MySQL Server Instance is running".

I'm a bit lost here:

Q1. Why until now there's nothing in the Applications? I can't see any item or folder like mysql there. Is it that every time I want to run mysql, I have to mount the installation package?

Q2. What did MySQLStartupItem.pkg install?

Q3. Are there any client side tools inside the installation package? I mean, for example, Microsoft SQL Server for Windows, there are Microsoft SQL Server; and there are Microsoft SQL Server Management Studio, which serves as a client tool to browse databases and doing queries; also, if I download "Microsoft SQL Server Express with Tools" package, it will install both. Does mysql-5.5.28-osx10.6-x86_64.dmg contain only the server, or also a client tool inside?


texts: Is there any doc listing standard  Oracle character sets correspondencies?

texts:
I'm looking for a table listing Oracle and "standard" character sets, for example:

Oracle       Standard
------------ ----------
WEISO98859P1 iso-8859-1
AL32UTF8     utf-8
etc.         etc.

Does this kind of resource exist? I couldn't find any.


texts: MySQL Query Optimization Group By with Max

texts:
I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community.

Trying to optimise MySQL query which uses Group by with Max. Trying to get latest updated frames which were active before any past date.

TABLE audit_frame_master

CREATE TABLE `audit_frame_master` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `audit_type` enum('INSERT','UPDATE','DELETE') NOT NULL,
  `frame_id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `sort_order` int(11) DEFAULT '0',
  `frame_type` varchar(45) DEFAULT NULL,
  `frame_content_type` varchar(45) DEFAULT 'default',
  `movable` tinyint(4) DEFAULT '0',
  `frame_tags` varchar(512) DEFAULT NULL,
  `active` tinyint(4) DEFAULT '1',
  `frame_hint` varchar(256) DEFAULT NULL,
  `size` double DEFAULT '0',
  `lastModified` datetime DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `updatedOn` datetime DEFAULT NULL,
  `deletedOn` datetime DEFAULT NULL,
  `optin_algorithm` tinyint(4) DEFAULT '1',
  `optin_menu` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `framemaster_frame_type_idx` (`frame_type`),
  KEY `fk_afm_frame_id_idx` (`frame_id`),
  KEY `afm_updatedOn_idx` (`updatedOn`),
  KEY `afm_composite_idx` (`active`,`updatedOn`,`deletedOn`),
  KEY `afm_covering_idx` (`active`,`updatedOn`,`deletedOn`,`frame_id`,`id`,`sort_order`,`createdOn`),
  CONSTRAINT `fk_afm_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1079822 DEFAULT CHARSET=utf8;

TABLE audit_frame_association

CREATE TABLE `audit_frame_association` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `audit_type` enum('INSERT','UPDATE') DEFAULT NULL,
  `frame_id` bigint(20) NOT NULL,
  `entity_id` bigint(20) NOT NULL,
  `entity_type` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `afa_frame_id_idx` (`frame_id`),
  KEY `fk_afa_entity_id_idx` (`entity_type`),
  KEY `afa_createdOn_idx` (`createdOn`),
  KEY `afa_composite_idx` (`createdOn`,`active`,`entity_type`,`entity_id`),
  KEY `afa_covering_idx` (`createdOn`,`active`,`entity_type`,`entity_id`,`frame_id`,`id`),
  CONSTRAINT `fk_afa_entity_type` FOREIGN KEY (`entity_type`) REFERENCES `entity_type_master` (`entity_type`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_afa_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=81166 DEFAULT CHARSET=utf8;

Query

SELECT fm.frame_id
FROM
               (SELECT *,MAX(id) as id1
                              FROM audit_frame_master
        WHERE updatedOn <='2019-03-13 10:49:29'
        GROUP BY frame_id) fm,
               (SELECT *,MAX(id) as id1
                              FROM audit_frame_association
        WHERE createdOn <='2019-03-13 10:49:29'
        AND (entity_id,entity_type) IN ((707,1))
        GROUP BY frame_id,entity_id,entity_type) fa
WHERE fm.frame_id=fa.frame_id
AND fm.active=1
AND fm.deletedOn IS NULL
AND fa.active=1
GROUP BY fm.frame_id
ORDER BY fm.sort_order = 0, fm.sort_order ASC, fm.createdOn DESC;

EXPLAIN QUERY

Performance:

audit_frame_master has 1M rows and thus taking more than a minute to execute single query.

Any query changes or any db schema suggestion to improve performance would be appreciated.


texts: MySQL: How to fetch nth level child menus of a parent menu

texts:
I have a menu which is parent of multiple menu items and each child menu has their own child menus. This pattern is continued till 5-6 vertical hierarchy level.
I don't want to write a query which contains nth level subquery as below:

select id from jos_menu where parent in(select id from jos_menu where parent in(select id from jos_menu  where parent=385 and published=1))

Also, I don't want to write a script for this. There should be something in MySQL to fetch such result.

Can someone guide me about how can I achieve my result in the best way?


texts: DMV queries for capturing most frequently used queries and worst performing queries

texts:
Can anyone provide dmv query to achieve the following

Query to capture frequently used stored procedure
Query to capture frequently used ad-hoc queries
Query to capture worst performing stored procedures
Query to capture worst performing queries

Thanks in Advance.


texts:
I am a bit confused by the role attribute LOGIN | NONLOGIN, where postgres docs says:

  These clauses determine whether a role is allowed to log in; that is,
  whether the role can be given as the initial session authorization
  name during client connection. A role having the LOGIN attribute can
  be thought of as a user. Roles without this attribute are useful for
  managing database privileges, but are not users in the usual sense of
  the word. If not specified, NONLOGIN is the default, except when CREATE
  ROLE is invoked through its alternative spelling CREATE USER.

What is a possible restriction with NONLOGIN? Why would people want a role with NONLOGIN in the first place?


texts: Missing log block for availability database in SQL Server 2016

texts:
I've recently begun receiving alerts like this one:

  Always On Availability Groups transport has detected a missing log block for availability database "myDb". LSN of last applied log block is (xxxx:xxxxxxx:x). Log scan will be restarted to fix the issue. This is an informational message only. No user action is required.

According to this article, it should have been fixed in SQL Server 2016 SP2 CU 2. My environment is SQL Server 2016 SP2 CU7, so I don't understand why I'm seeing this. Anyone here knows what could be causing it or if anything is actionable, aside from opening an MS support ticket?


texts: Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?

texts:
I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated.

Using a script of Jonathan Kehayas I go through the list of query plans that used those lookups and compare this to the information sql server gives me regarding missing indexes:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
    n.query('.'),
    i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
    i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
    i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
    i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
    i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
    i.query('.'),
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS output_columns,
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS seek_columns,
    i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate,
    cp.usecounts,
    query_plan
FROM (  SELECT plan_handle, query_plan
        FROM (  SELECT DISTINCT plan_handle
                FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
        OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
      ) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
    ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);

But very often, after I extract the query and re-execute it with some valid scalar parameters, it shows that the actual query plan does not (anymore) perform a key lookup. So maybe this is some caching issue and/or it is because I changed the query by passing different parameters... I don't know.

However, if the indexes allow the query to do a nice seek operation right now, where does this plan come from?
Are those plans kind of old?
What are possible reasons?

Is it maybe time to reset this cache to be sure there is no old stuff inside?


texts: Can we use alias name in LIKE statement?

texts:
I have a query to search strings from database tables. The query is

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
WHERE   1 = 1
        AND (id LIKE 'B%'
             OR ofname LIKE 'B%'
             OR lname LIKE 'B%'
            );

Now here ofname in LIKE statement says unknown column. Is there any way to use alias in LIKE statement?


texts: How to grant multiple users privileges; MySQL

texts:
I have the following code:

GRANT SELECT,INSERT,UPDATE ON db1.*
TO ‘user’@example.com;

I am a beginner in mySQL, I was wondering if there is a method in which I can specific the privileges for every user in this domain network without having to type the above code for each user? %wildcard?

Also is there any method such as using a table where I can manage all the privileges of said users


texts:
I have recently installed the newer version of SSMS on my system. But the option to load the excel file is grey out. I'm not able to load BULK Load

I tried to run DTSWizard.exe, even it doesn't give me the option to load the data from Excel sheet.

Please find the attachment

Here are my instances installed on this system


texts: Attach database from SQL Express 2008 Edition to SQL Server 2008 Enterprise Edition

texts:
My friend is stuck on moving a database stored in SQL Express 2008 Edition in a server (called server A) to SQL Server 2008 Enterprise Edition in another one (called server B). There are two SQL 2008 editions installed in the server A.

SQL Server 2008 Standard
SQL Express 2008

SharePoint is being run in server A (WFE/Application/Database role) and there are two content databases stored in SQL Express 2008. My friend just got the administration job. He doesn't know exactly what has happened because the ex-staff resigned without any confirmation. At this very moment, he is responsible for moving these content databases from server A to server B (SQL Server 2008 Enterprise is already installed in server B). After doing many times, he still can't attach these content databases to server B. I'm trying to help him by digging into SQL Server. Some errors often occurs:

Exclusive access could not be obtained because the database is in use.
The backup set holds a backup of a database other than the existing 'xx' database

Does anyone encounter this case? Any advice is greatly appreciated.

Regards,

T.s


texts: How to re-include excluded folder in SSDT-BI / Visual Studio 2013 database project?

texts: I've selected "Excluded from project" on a folder with tables (database was imported first into project). Now I would like to re-include this folder into the project. In the "Add Existing Item" option it's not possible. Only files can be added like that. Do you know how to do that ? Is it possible or should I re-create the folder and add the files there again manually ?


texts: How to add userAdmin user in Mongodb Atlas

texts:
How to add a user with userAdmin role in Atlas?

The Cluster Security "Add New User" UI https://cloud.mongodb.com/v2/.....#clusters/security/users shows only limited set of built-in roles without any option to enter role name manually:

So I am a bit stuck here - there are no options in the UI, and non of the existing roles allow to add users in a shell.

UPDATE:

To make it crystal clear I went To "Security" tab in MongoDB Atlas cluster, from there I clicked "Add a new User", clicked "Show advanced options" and got the screenshot above.

UPDATE 2:

None of the roles in the list have userAdmin privilege.

The most "powerful" atlasAdmin covers only:

readWriteAnyDatabase
readAnyDatabase
enableSharding
dbAdminAnyDatabase
clusterMonitor

and doesn't have enough privilege to execute db.createUser() command.


texts: OLTP to OLAP process causing deadlocks and impacting customers

texts:
I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. Please assume that some of the basic tenets of OLAP+OLTP are things I am unfamiliar with.

In my organization there is a concept of "Consolidating" the OLTP data to a series of tables prefixed by conso which are essentially denormalized structures (the OLAP, I think). This occurs overnight and the jobs which perform the work are extremely slow, on the order of several hours. They also do not scale well as we add more customers the processing time has shot up.

There is also parameters to these consolidation jobs that cause them to run in a smaller context, and this is triggered when a customer updates the data for a particular metric. That is generally pretty fast.

Unfortunately, the night time consolidation jobs are causing deadlocks for some customers and other issues that make the process extremely problematic. We might wake up to alarm bells because the consolidation worked for 1/2 of the customers and not the rest.

What can be done to avoid this issue? Is there a best practice for running OLTP=>OLAP transactions? Determining the source of the deadlocks are outside of my experience and I did not write the consolidation jobs. I would leverage the person who wrote the original jobs, but they are not competent or this wouldn't be happening in the first place (I assume).


texts:
We restore a database daily that we download from an FTP site.  I would like to build a history table that tracks when the database is restored on our SQL Server.  This is easy.  My issue is sometime the download is incomplete and therefore the restore does not work and throws an error.  I would like to add logic to my history process to insert failed if the restore fails.  Not sure how to handle this logic and am looking for assistance.

This is the error we get most frequently when the failure occurs.

Error: 3266, Severity: 16, State: 1.

The backup data at the end of "F:\Backups\DB.bak" is incorrectly formatted. Backup
sets on the media might be damaged and unusable. To determine the backup sets on the
media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using
BACKUP WITH FORMAT, which destroys all the backup sets.


texts:
I've noticed recently that our resting CPU on our Aurora RDS cluster appears to be about 80%. I'm unsure when this started, as we don't have alarms/metrics in place (something I plan to rectify now).

It's a 2 node cluster db.t2.medium. Normally with a MySQL/MariaDB instance I'd troubleshoot on the actual machine, however the trade off of cloud services is that I can't really do this.

CPU:

Query count per second:

We have a script that normalises some data every 10 minutes, and the spikes I see there reflect the 10-15% jump, which I'd expect. However we appear to be constantly resting around the 70-80% mark. The update, insert and delete throughput per second never exceeds 4/s, so I can't imagine other operations are affecting it.

Why would this be, and how can this be troubleshot?


texts: How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

texts:
LOB_DATA : varbinary(max),varchar(max), image , text columns.

I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different hard disk.

I have created a new filegroup and file in this filegroup. I also used  sql command from this question. But varbinary column storage remains where it was before.

-- To move table data to new filegroup
CREATE UNIQUE CLUSTERED INDEX PK_YourTableName
ON dbo.YourTableName(YourPKFields)
WITH (DROP_EXISTING = ON) ON [NewFilegroup]

I use following sql to see filegroup of lob_data.

-- To see lob_data filegroups
SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as
FILE_GROUP_NAME, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE
type_desc = 'LOB_DATA'

I am using SQL Server 2008.

I have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Is there exists another approach?


texts:
We are debating an architecture that employs sql server 2012 and transaction replication. Idea is to offload reporting activity to a secondary server and have the ability to include/exclude what is replicated by keeping historic data on the reporting server.

Couple of questions:

Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.
If the subscriber server goes down, will the primary still function

Thanks!


texts: MS SQL Server - update row value to next row with row id

texts:
I have a table like below:

Newid1       a   b   c
   1       null  40  50
   2       null  45  55
   3       null  46  56
   4       null  47  57
   5       null  48  58

Now I want to update value in a column by updating C column value into it (For example: 50 to the second row, 55 to the third row, 56 to the fourth row ...). How can I do this with WHILE command.
Thanks all in advance.


texts: Alter table Statement Failed -Msg 207, Level 16, State 1

texts:
I wrote a script to add a column in a table and on successful execution update its values.
I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between BEGIN and END is not executing and return error . I verified there is no Database level DDL trigger to stop adding column in database.

  Msg 207, Level 16, State 1, Line 24
  Invalid column name ColumnName.

DECLARE @CloumnAdded bit
SET @CloumnAdded = 0

IF NOT EXISTS ( SELECT  1
                FROM    Sys.Columns
                WHERE   [Object_ID] = Object_ID('DBName.dbo.TableName')
                        AND Name = 'ColumnName' )
    BEGIN

        ALTER TABLE TableName  ADD ColumnName varchar(120)

        SET @CloumnAdded = 1

        select @CloumnAdded, 'step 1 '
    END

IF ( @CloumnAdded = 1 )
    BEGIN
        select @CloumnAdded,'step 2'

        UPDATE  TableName
        SET     ColumnName = '2669'

        select @CloumnAdded,'step 3'
    END

GO


texts: TSQL Extract first word as First name. If only one word or null, set to ‘’

texts:
Table column has values it includes prefix as well. I want to extract first word as First_name. If only one word or null, set to ''.

Name
----------
Abhijit K
Ravi Mahta
Dr.Rajiv
Dr sharma
Mr.Raju
Miss Anjali

Output I want:

First_name
----------
Abhijit
Ravi
Rajiv
sharma
Raju
Anjali


texts: pgAgent service for PostgreSQL does not run

texts:
OS: Windows 10

PostgreSQL 11.4

I Installed PgAgent with Stackbuilder setup.

In Windows services; PostgreServer service is running. However, the pgAgent service is stopped. When I try to start pgAdmin service from there I get this error message:

The PostgreSQL Scheduling Agent - pgAgent service on Local Computer started and then stopped.
Some services stop automatically if they are not in use by other services or programs.

I have the pgAgent service to log On As -->LocalSystem  (Note, I have also tried to change it to an windows administrator account)

When opening pgAdmin 4 I can see the pgAgent Jobs tab as well as create jobs.

Acording to the description on this page ,one should create an extension as well. However, when pgAgent was installed , there wasen't copied any extension files to %\PostgreSQL\11\share\extension so I can not create the extension in pgAdmin

Any advice on how I get the pgAgent service to run my jobs I have createn in pg Admin?

EDIT: On This thread They state that editing pgpass.conf in c:/user/user/AppData/postgresql helped. I have no such folder nore file. The only postgres related folders I've found under C:\Users is C:\Users\user\AppData\Roaming\pgAdmin

EDIT2:

I opened CMD as an admin and run cd %APPDATA% to find correct folder.
I created the pgpass.conf inside newly created folder postgresql in appdata --> C:\Users\user\AppData\Roaming\postgresql\pgpass.conf.
I added that file as a system variable PGPASSFILE (Source1 and Source2).

That did not change anything either.

Cholud I do some editting in REDEDIT? Source3


texts: Best way for processing over 150 million rows MySql

texts:
I wrote a function on nodejs to SELECT 150 million records, process the data and UPDATE those same 150 million records using
UPDATE 'table' SET value1='somevalue' WHERE idRo2 = 1;

Single Update for each row concatenate send one single query string allowing multipleStatements querys on the db connection.
I've being encountering multiple errors as

Error: Connection lost: The server closed the connection.
JavaScript heap out of memory.
RangeError: Invalid string length.
Killed process.

I think i might not be using the right technologies or programing technique
*Edit:
The data process i need to do is to take the 'wholeApiResponse' column value, parse that string and then insert the parsed values into new added columns (askPrice, lastPrice, lowPrice, highPrice, volumePrice, platformTimestamp), this end up modifying the existing row by adding new values from an existing string.


texts: When a record is locked in Oracle, can we know which record is locked?

texts:
When a record is locked ,can we know which one is locked?

How can I get the record rowid or something else info?

I can get some info by this sql

SELECT c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c.ROW_WAIT_BLOCK#,c.ROW_WAIT_ROW#
   FROM v$locked_object a, dba_objects b, v$session c
WHERE a.object_id = b.object_id
    AND a.SESSION_ID = c.sid(+)

I found a method in web to get rowid by using function DBMS_ROWID.ROWID_CREATE()

But it doesn't seem to work.


texts: MySQL - Many to Many relationship, or one to many with additional columns

texts:
Our product contains some constraints that made us contemplate which table design will yield the best performance.
Our model:

Question table which contains fields like id, body, difficulty, and tags.

Each question can have up to five different tags.

Tags table which contains fields like id, and tag name.

The question table will have significantly more reads than writes.
We considered two design options:

Many to many relations where we'll have the following tables

Question - TagsQuestion - Tags

One to many relation where we'll have the following tables

Question - Tags

In this option, the question table will contain five tags columns (TagA, TagB, etc)
The "read" queries will mostly fetch questions by tag or tags.


texts: Which database should be using for my specific goal

texts:
Introduction:
I am trying to figure out what kind of database I should use for my project to get the best speed possible.
Background:
I am not sure if MySQL is the best solution, but I will use MySQL as an example to explain the data that I currently have.  The data currently (or will) be arranged in 2 tables.
Table 1 has have 3 columns and Table 2 has have 1 column.  Both tables will have around 1 billion rows each.
The project will get the first value from Table 2 and check if it exists in the first column of Table 1.
This project is currently running on a computer which has a mechanical drive.
Rough estimates suggest at the data will use around 500GB of drive space.
Goals:

To be able to insert the data into the tables as fast as possible,
To be able to compare each column in Table 2 to see if the same value exists in column 1 of Table 1 as fast as possible.

Progress:
At the moment, I am inserting the data into a MySQL database and batching the inserts into groups of 1000, which is averaging to about 200 inserts per second.  At this rate, it will take about 2 months to generate and insert the 1 billion records..
Benchmark:
The process of generating the data and inserting the data is using around 5% CPU, around 1GB RAM of the 16GB available, and when checking the disk IO, it is averaging about 2MB/s.  So my guess is that the mechanical drive is the bottleneck.  I can constantly hear it seeking away.
Question:
With the possible mechanical drive as the bottleneck in mind (correct me if I am wrong about this being the bottleneck), is MySQL the best option to achieve the 2 goals above as fast as possible, or is there a different type of database I should be considering and using instead?


texts: Using count in triggers to update another table

texts:
Say i have a table called trx_request with the following data
id | confirmed(TIME) | finished(TIME)
-------------------------------------
1  | 12:05:00        | NULL
2  | 13:11:00        | NULL

and another table called trx_updates with the following data
request(FK) | role(INT) | added(TIME)
------------------------------------------
1           | 1         | 14:02:00

I'd like to make a trigger that after a record is inserted into trx_updates, counts how many records has the same value in the request field (such as how many '1's there are). And then, does a check to see if the count reaches 4 ('1' shows up 4 times). When it does, it updates the trx_requests.finished to NOW().
How would one make a count statement in triggers? or are triggers the way to go?


texts:
First forgive me, I am not a DBA and our company really doesnt have one. I am a developer and Googling for answers is just not helping any more. Thanks in advance.

My question is on optimal structure for our database. We have a website that allows users to search and find PDFs easily. Mostly its just meta data searching but we do allow full text searching (All words, Any Words, Exact Phrase).

There are ~3 million PDFs (growing daily) at about 2.3 TB in size. These are stored in tables corrisponding to date (File1980, File1990, File2000, etc) using VARBINARY(MAX). We didnt make it a single table because our system admin was having trouble with backups. Each of the file tables (File1990,etc) has it's own MDF file.

Our website is using Microsoft Dynamic Data and Entity framework. We search across the PDFs using one of the table value functions below that joins with the rest of the LINQ query.

ALTER FUNCTION [dbo].[udf_ContainsSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier] NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])

)

AS
BEGIN
    insert into @containsTable

    select [key],[rank] from ContainsTable(File2010,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File2000,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1990,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1980,Content,@keywords)
    union
    select [key],[rank] from  ContainsTable(File1970,Content,@keywords)

    RETURN
END

ALTER FUNCTION [dbo].[udf_FreetextSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier]  NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])
)

AS
BEGIN
    insert into @containsTable

        select [key],[rank] from FreetextTable(File2010,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File2000,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1990,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1980,Content,@keywords)
        union
        select [key],[rank] from  FreetextTable(File1970,Content,@keywords)

    RETURN
END

We have been getting timeout issues on the web site and have been looking into performance. Running the database tuner and adding some indexes on a weeks worth of queries helped but not enough.

Now the questions:

Is there a better way to structure the data that would give better performance?

We use professional SQL Server 2008, would Enterprise help us in anyway?

Is there a better way for the full text search portion?


texts: Corrupted Filesystem: Possible to restore databases from the contents of /var/lib/mysql?

texts:
Using CentOS 6.4 x64.

Not sure what happened but somehow my VPS filesystem got corrupted and it is now in a permanent read-only state. I've been working with my hosting provider's sys. admin. but suffice it to say I can't run anything related to MySQL services or daemons.

I did however backup the contents /var/lib/mysql folder. I moved the DB directories from inside that to a new install of MySQL and did proper permissions and ownership. From there I was able to see the databases from the MySQL console and even view the tables but I couldn't get any further. Whenever I attempted to run a SELECT query on any of the tables I get "doesn't exist when using LOCK TABLES". Tried dumping the DB using skip-locks and all that but nothing seems to work. Select queries and any sort of dump seems to be useless.

Is there another way to repair or retrieve the contents of these tables/databases? Thank you.


texts: SQL Server: Additional cpus slow down batch

texts:
I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment.  I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it.

I've got a batch that takes around 5 minutes to run when there is a single CPU in the virtual machine.  Bumping up anywhere from 2-8 causes it to take over 10 minutes to run.  Watching the Task Manager I see that there is not much if any parallel execution and lots of context switching.  If I limit sqlservr.exe to a single CPU by setting the processor affinity in Task Manager the time drops back down to 5 minutes.

The particular batch that I'm running is makes heavy use of cursors and dynamic sql which cannot be eliminated.

The query has been profiled and optimized.  Statistics are all up to date and indexes are rebuilt.

Is there anything I can do to SQL Server to get better behavior?  This seems not right.  I would like to add additional CPU resources to the VM so that they can be used if necessary without a drastic performance hit for serialized processing.

CPU is i7-4770K with VT-x enabled both with and without hyperthreading enabled.

EDIT: Some more stats-
Running from the root partition in Hyper-V with 1 CPU takes 4:41 and with 8 CPUS takes 7:30.
Running without virtualization 1 CPU takes 4:11 and with 8 CPUS takes 5:23.

EDIT2: On the recommendation of Jon Siegel I have gotten Query Plan traces for both cases.  The query plans are long (500k lines) due to the loops and curors.  There are no obvious differences between the two and I don't see any signs of parallelism in use.

Additionally, setting MAXDOP=1 at the server level has the same effect as the affinity setting in that it limits to 1 CPU and makes the batch go fast.

Jon also made me aware of the Resource Governor feature of SQL 2012 EE which should allow the ability to limit CPUs on a per connection basis as a workaround.  The relevant setting is the AFFINITY SCHEDULER option for the resource pool.

So, at this point to sum it up mutlicore:loop*cursor*exec() = MS-SQL-no-likey (and virtualization is not helping the matter).


texts: Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

texts:
I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is

insert into ##eapb
EXEC (@remotecmd) AT [ADSQLDB3S\DEV];

But I get the error:

  OLE DB provider "SQLNCLI11" for linked server "server_name" returned message "The transaction manager has disabled its support for remote/network transactions.".
      Msg 7391, Level 16, State 2, Line 71
      The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "server_name" was unable to begin a distributed transaction.

If I remove the insert into ##eapb, I have no error.

The Linked Server has the RPC Out option set to True.


texts: Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

texts:
We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM table. The version of MySQL is: 5.1.56-community-log

For some reason, the server eventually eats all the memory it has, goes into swap and becomes very slow. Even after NOTHING else is happening on the server, it stays that way.

Here's a picture showing htop:

How we do these inserts. There's a job every hour that runs on a server and starts doing a lot of inserts. All inserts are DELAYED. Inserts are NOT extended but we use mysqli::multiple_query to send 2500 of these single statement inserts to the server in one batch.

What would also help is to look inside MySQL and see where it's consuming all that memory. We've already checked all the usual buffers and they are way below the amount of memory we have.


texts: Why am I NOT getting a mutating table error in trigger?

texts:
It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

However, I cannot understand why this demo trigger is not failing with a "mutating table" error when I perform an insert into emp using SQL Developer or SQL*Plus:
CREATE OR REPLACE TRIGGER emp_bri
  BEFORE INSERT ON emp
    FOR EACH ROW
BEGIN

  SELECT max(id) + 1 INTO :NEW.id FROM emp;
  UPDATE emp SET salary = 5000;

END emp_bri;

The insertion completes successfully with the next id value and updates all emp records. I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0. I have read about compound triggers but the sample does not use them.


texts:
I have a very simple table:

CREATE TABLE content
(
  id serial NOT NULL,
  text text,
  fullfilename text,
  CONSTRAINT "PK_ID" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE content
  OWNER TO postgres;

CREATE INDEX content_idx
  ON content
  USING gin
  (to_tsvector('danish'::regconfig, text));

Where text is a rather long aggregated text to perform full text searching.

When I run the following query it resorts to a seq scan:

EXPLAIN ANALYZE SELECT id
FROM content
WHERE to_tsvector('danish', text) @@ to_tsquery('danish','felter')

"Seq Scan on content  (cost=0.00..164.57 rows=249 width=4) (actual time=41.147..7235.823 rows=289 loops=1)"
"  Filter: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"  Rows Removed by Filter: 1149"
"Planning time: 0.366 ms"
"Execution time: 7235.914 ms"

That is absolutely not okay. But when disabling seq scans with SET enable_seqscan TO 'off' I get the following result:

"Bitmap Heap Scan on content  (cost=17.94..168.53 rows=249 width=4) (actual time=0.145..0.323 rows=289 loops=1)"
"  Recheck Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"  Heap Blocks: exact=70"
"  ->  Bitmap Index Scan on content_idx  (cost=0.00..17.87 rows=249 width=0) (actual time=0.121..0.121 rows=289 loops=1)"
"        Index Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"Planning time: 0.373 ms"
"Execution time: 0.383 ms"

What exactly is going on, and what parameters needs tuning for it to run better. I don't like the idea of removing a tool from the query planners toolbox. As it stands, the setup is running a stock PostgreSQL 9.4.4


texts:
We're currently running Postgres 10 in our infrastructure with Barman managing weekly full backups and holding onto the WALs. Because of the size of our DB, the WAL volume, and resource constraints when we need to restore and scrub a copy into non-prod it's cheaper to restore it to a staging server with reduced resources, and then clone the datadir to the actual non-prod nodes.

However, with the increase in WAL traffic over time, recently if I can't convince the requestor to go with a PITR close to the weekly full the restore process will consume a massive amount of RAM, dipping into swap, and becoming insufferably slow. Restarting it in the middle seems to reset this trend, mucking up any automation, but I'm wondering if there's a reason for this behavior or a way to address it.


texts: Can SQL Server's BCP output to multiple gzip compressed files?

texts:
I've been looking into bcp.exe on SQL Server and am wondering if its abilities are limited to what the documentation actually says...

Is there no native way to output to multiple files?

And regardless of being able to output to multiple files or not, can it output to at least compressed files?


texts: Installing GridDB on a VM hosted on Azure

texts:
So I've been trying to work with GridDB on a CentOS VM with no success. I have my VM hosted on Azure and it's CentOS 8. I'm following the steps laid out on this guide. After using wget to download the rpm package I try to install but I keep getting python failed dependency error as follows:
    Failed dependencies:
     /usr/bin/python is needed by griddb_nosql-4.2.0-1.linux.x86_64

The thing is, I have python installed and correctly configured so I do not understand why it would not run. Checking the python version shows the correct version:
$ python -V
Python 3.6.8


texts: Order by starting from "the middle" in a windowing function

texts:
What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all the way around.
What does work is separately creating a new column with the desired ordering and using that to order by, like so:
IIF(IR.[End Month Index] - ID.[Latest Month Index] - 1 < 0, IR.[End Month Index] - ID.[Latest Month Index] + 12 , IR.[End Month Index] - ID.[Latest Month Index]) AS [Upcoming Month IDX]

But it's quite ugly as I have to hard-code the number of the max element (12) and doesn't really feel like a proper solution. Is there a more elegant way to resolve this?


texts: How much does an index need to narrow the results of a search in order to be useful?

texts:
How much does an index need to narrow the results of a search in order to be useful in speeding up queries?

Some examples all across the spectrum:

A column for storing true/false values obviously has only two unique values.
A 'last name' column probably has many unique values (although it may not).
A primary key column has all unique values.

I think that the goal of an index is to quickly narrow a search to a few rows, and that therefore, the last case is the best, the second is OK, and the first is useless.

Am I correct? If so, roughly where is the line of usefulness?  For example, if an index can narrow the results to 1% of the rows, is that useful? What about 10% or 25%?


texts: Is there a more concise way in Postgres to extract a portion of a string?

texts:
I'm using Postgres 9.5. I have a table with a column that records URLs.  Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I came up with:

select substring(url, 0, case position('?' in url) when 0 then length(url)+1 else position('?' in url) end)
from article;

This seems a little wordy and I was wondering if there's a more concise way to do this. My table column is of type TEXT.


texts:
I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error:

Error report:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 951
ORA-06512: at "SYS.DBMS_ADVISOR", line 739
ORA-06512: at line 4

Does this mean the materialized view script is in the best shape, or DBMS_ADVISOR.TUNE_MVIEW is not capable to find a better solution?


texts:
We have multiple instances on single node of the AlwaysOn setup, all are configured as AOAG. Right now we are connecting using the Listener\InstanceName. The listeners for each AG is having a dedicated port designated. This is why we cannot connect just by using the Listener without the port or InstanceName.

I wanted to configure in such a way that just giving the AG_Listener would be enough to connect without using the port explicitly.

  Example : AG-SQL001 instead of AG-SQL001,Port_Number

Can this be done ? If yes then how?


texts: Database Design For Employee Business Hours

texts:
I have a simple employee database (I use mySql):

Employee (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(40) NOT NULL,
  email_Id varchar(40) NOT NULL,
  mobile_Number varchar(15) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY employee_email_id_unique (email_Id)
)

Now I need to store different Business Hours for every employee

All Monday, 9am to 5pm
All Tuesday, day-off
All Wednesday, 9am to 5pm
All Thursday, 9am to 5pm
All Friday, 9am to 5pm
All Saturday, 9am to 3pm
All Sunday, day-off

Method 1: Store open_time and close_time for every day of week if open_time and close_time is null then its is day-off

Employee_Business_Hours (
  id integer NOT NULL PRIMARY KEY,
  emp_id varchar(16) NOT NULL,
  day_of_week varchar(16) NOT NULL,
  open_time time NULL,
  close_time time NULL,
  PRIMARY KEY (id),KEY employee_id_foreign (emp_id),
  CONSTRAINT employee_id_foreign FOREIGN KEY (emp_id) REFERENCES Employee (id)
)

Method 2: Store open_time and close_time for every day of week with is_day_off flag

Employee_Business_Hours (
  id integer NOT NULL PRIMARY KEY,
  emp_id varchar(16) NOT NULL,
  day_of_week varchar(16) NOT NULL,
  open_time time NOT NULL,
  close_time time NOT NULL,
  is_day_off tinyint(1) NOT NULL, /*boolean value*/
  PRIMARY KEY (id),KEY employee_id_foreign (emp_id),
  CONSTRAINT employee_id_foreign FOREIGN KEY (emp_id) REFERENCES Employee (id)
)

Method 3: if day-off then Do not store in Employee_Business_Hours table

Employee_Business_Hours (
 id integer NOT NULL PRIMARY KEY,
 emp_id varchar(16) NOT NULL,
 day_of_week varchar(16) NOT NULL,
 open_time time NOT NULL,
 close_time time NOT NULL,
 PRIMARY KEY (id),KEY employee_id_foreign (emp_id),
 CONSTRAINT employee_id_foreign FOREIGN KEY (emp_id) REFERENCES Employee (id)
)

Which Method is best here, or maybe there is another way?


texts: How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

texts:
I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it.  The SP filters out all the calculated columns and other stuff that stops you doing an 'SELECT *' for auditing and then writes a trigger that inserts those specific columns from table into table_audit upon DELETE/INSERT/UPDATE along with some audit data such as HOST_NAME() and COLUMNS_UPDATED().  This has been little used but has generally worked for the clients who've asked for it.

In a recent round of testing I was asked to set up the auditing on a test database.  This caused inserts in our main table to fail because "string or binary data would be truncated", after investigation I found the column where the results of COLUMNS_UPDATED() was being stored was the issue.

The definition of the column was:

[UpdateColumns] [varbinary](16) NULL

Changing the definition to this has made everything work again:

[UpdateColumns] [varbinary](24) NULL

However what this highlights is that I don't understand the relationship between the number of columns in the table (95 in this case, 7 of which are calculated) and the size of the output from COLUMNS_UPDATED().  I thought [varbinary](16) = 128bits, which should be more than enough flags for 95 columns.

So my question is: what is the relationship?

Secondary question: Can I easily derive from the number of columns that will be audited a value for x in [UpdateColumns] [varbinary](x) when building the trigger or would I be better off just setting x to some larger number?


texts: PostgreSQL: Copy Table Functions to another database schema

texts:
as the title said. Can we move or copy existing table functions to another database schema?

I can get all the functions from a database schema using command below (Taken from here):

SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public';

And I also can get the function code details using command below (Taken from here):

\df+ public.<function_name>

As I need to move the database to the new server, so I need to copy all table functions inside a specific schema. Say I have a functions called update() inside a schema analytics.

Can we copy this functions to another database ?
and is there any command to see when this functions will be run?

Note that I am using PostgreSQL 11 and Ubuntu 18.04


texts:
I have a table in which I will store messages (as in chat messages).

The table will be appended to a LOT, rows being inserted as messages fly by, much more often than entries will be updated.

Older entries from the table will be archived daily (rows deleted), hopefully during quiet periods.

The table will also be read from but less than it will be written to - apps only need to read when push notifications went missing, though managers may want to review messages sent by employees for example during legal proceedings)

So what is the right index type for a write heavy table that will be read a fair amount but less often than it is written to, but should also be fairly efficient for delete operations?  Which of the standard B-tree, hash, GiST, SP-GiST, GIN, and BRIN is the most optimal for a table that will mostly be INSERTed and also batch DELETEed from, but needs to still perform reasonable for SELECTS and is too large for sequential scans.

The DBMS is PostgreSQL 9.5.

Three columns of the table will be used for select, being the timestamp, the recipient (userid) and also the rowid (artificial PK) column.

id UUID
userid UUID FK->users(id)
timesent TIMESTAMP
message VARCHAR

Rows does not need to be updated once inserted.

As a bonus question:  I forsee a future business requirement to also store read acknowledgements for messages, which if stored in the same table would mean updates to existing rows.  I'm leaning to storing those in a separate table dedicated to that purpose though, but open to suggestions on how to implement that.

Edit:

Two type of retrieval are required -

SELECT where userid= X and timesent >= X and timesent <= X
SELECT where id = X

I assume I would want an index on each of id, userid and timestamp.  I further think partitioning to ease archival as described in the Postgres Manual and in this blog post appears like it would help very much in this situation.

But now I have even more questions:  besides the "What type of Index should I use on each of the select columns" I now also want to know "Do I implement constraints and indexes on the master table or on the partitions?"


texts: Select those entries where the relationship of the columns f1:f2:f3 is roughly number1:number2:number3

texts:
I have three columns f1, f2 and f3

f1  f2  f3
1   3   5
5   7   3
...

My goal is to select those entries where the relationship of f1:f2:f3 is roughly(+-2) number1:number2:number3

Example:

SELECT those rows where the relationship is roughly 5:7:4 -> Result 5 7 3.

What does the SELECT command look like?


texts: What is Fastest way to shrink a datafile?

texts:
I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the subject of my question).
Before that, I'm going to move almost all objects from the main datafile of 4To to multiple datafiles on the same filegroup so I can get back the free space on the 4Tb file.
After moving objects, free space take like forever to be reclaimed with a dbcc shrink file with blocs of 256Mo.
What's the fastest way to get that space back to the OS?
Is there a way to use a backup restore to shrink the file?


texts:
I'm researching why the following query consumes a lot of CPU. Please chime in and suggest any improvements/recommendations that can be made or why this query seems to consume high CPU. Or if there is anything structurally wrong in the query design.

(
        @fromDate DATETIME
        ,@toDate DATETIME
        )

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT TOP (25) [T1].[C13] AS C1
    ,[T1].[C14] AS C2
    ,[T1].[C15] AS C3
    ,[T1].[C16] AS C4
    ,[T1].[C17] AS C5
    ,[T1].[C18] AS C6
    ,[T1].[C19] AS C7
    ,[T1].[C20] AS C8
    ,[T1].[C21] AS C9
    ,[T1].[C22] AS C10
    ,[T2].[Name] AS C11
    ,[T2].[ID] AS C12
FROM (
    SELECT TOP (25) [T3].[NetObjectType] AS C13
        ,[T3].[NetObjectID] AS C14
        ,[T3].[NetObjectValue] AS C15
        ,[T3].[EventID] AS C16
        ,[T3].[Acknowledged] AS C17
        ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18
        ,[T3].[EventType] AS C19
        ,[T3].[Message] AS C20
        ,ISNULL([T4].[BackColor], 0) AS C21
        ,[T5].[NodeID] AS C22
        ,0 AS C23
    FROM dbo.Events AS T3
    LEFT JOIN dbo.NodesData AS T5 ON [T3].[NetworkNode] = [T5].[NodeID]
        AND 0 = 0
    LEFT JOIN dbo.EventTypes AS T4 ON [T3].[EventType] = [T4].[EventType]
        AND 0 = 0
    WHERE [T3].[Acknowledged] = 'false'
        AND [T3].[EventTime] >= @fromDate
        AND [T3].[EventTime] <= @toDate
    ORDER BY [C18] DESC
    ) AS T1
INNER JOIN dbo.Sites AS T2 ON [T2].[SiteID] = [T1].[C23]
ORDER BY [C6] DESC

This is an external vendor query. I have no authority to make changes, only suggestions.

Have to have TOP (25). The results are displayed on a website and they are only interested in the last 25 events and it updates on a schedule.

Here is the estimated execution plan:


texts: Return results of two MySQL query in only one

texts:
I am running two seperate queries to get statistics from 3 different tables:

contacts
campaigns
sent_messages

The first query counts all contacts that are marked as "stop" in the contacts table (results are grouped by month and limited to last 12 months).

SELECT TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
FROM contacts
JOIN campaigns
ON campaigns.list_id = contacts.list_id
WHERE STOP = 1 AND campaigns.user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

The other query counts the status codes of sent messages (also grouped by month and limited to last 12):

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

Now I'm having a hard time grouping these two queries in a single one. I have tried the following but it takes ages (full table scan) and returns uncoherent results.

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
SUM(IF(stop = 1, 1, 0)) AS stopped
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
JOIN sent_messages
ON campaigns.id = sent_messages.campaign_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

So I'd like to know why I get these results (maybe the second JOIN ends up creating duplicates and everything ends up being counted several times?), and how could I fix that (if at all possible).

For reference, the tables involved:

CREATE TABLE contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    list_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    attributes VARCHAR(10000),
    stop BOOLEAN DEFAULT 0,
    PRIMARY KEY (id),
    INDEX contact_ind_list_id (list_id),
    INDEX contact_ind_stop (stop),
    UNIQUE KEY contact_index_telephone_and_list (telephone, list_id),
    CONSTRAINT contact_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE campaigns (
    id INT UNSIGNED AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    list_id INT UNSIGNED,
    name VARCHAR(50),
    text VARCHAR(500),
    send_date TIMESTAMP,
    sent_date TIMESTAMP,
    sender_name VARCHAR(11),
    status TINYINT UNSIGNED DEFAULT 0, -- 0 draft, 1 to be sent, 2 sent
    batch_number SMALLINT UNSIGNED DEFAULT 0,
    PRIMARY KEY (id),
    INDEX campaign_ind_owner (user_id),
    INDEX campaign_ind_push_id (push_id),
    UNIQUE KEY campaign_ind_list_and_name (list_id, name),
    CONSTRAINT campaign_fk_owner
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT campaign_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE sent_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    campaign_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    status TINYINT NOT NULL, -- 0 = waiting / 1 = received / 2 = npai / 3 = net_error (error_credits, unknown_error)
    message TINYINT NOT NULL,
    PRIMARY KEY (id),
    INDEX sent_messages_ind_campaign (campaign_id),
    UNIQUE KEY sent_messages_ind_campaign_and_telephone (campaign_id, telephone),
    CONSTRAINT sent_messages_fk_campaign
        FOREIGN KEY (campaign_id)
        REFERENCES campaigns (id)
        ON DELETE CASCADE
);


texts: Find the average number of n:m connections of two tables?

texts:
I want to know to how many lists a user is assigned in average.

User
====
id
name

Lists_Users
===========
user_id
list_id

List
====
id
title

Is there a good way of receiving this number via SQL?


texts: Permission Error when trying to import SQL

texts:
On an attempt to import an SQL into a Database I get this error.

* Error 0xc002f210: Preparation SQL Task 1: Executing the query "IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name..." failed with the following error: "CREATE TABLE permission denied in database 'WebsiteDatabase'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)


texts:
I have the following issue: I have two tables

Article
id     state
Guid    int

and

Article_history
id   state   datechange
Guid  int     datetime

First table store porduct information, second fills then product state changed. E.g.

Article
id     state
1       1

Article_history
id   state   datechange
1     1      2019-11-10
1     2      2020-01-15

I need to create a report for each article in several years period with one month step:

date         id   state
2019-11-01   1    1
2019-12-01   1    1 --There is my problem
2020-01-01   1    2

I create a query to Article_history table, but since my app create record only on change Article state not every month is presented in Article_history. In my example I actualy don't have record for 2019-12-01. I need to fill the time gaps for each product with the last known state.

I can't think about method to fill this gaps withot using cursor for each month in the given period but it takes to much time.
Please, give me advice.

UPD: Mock

 Create TABLE Article
(
    id int,
    state int
)

Create TABLE Article_history
(
    id int,
    datechange datetime,
    state int
)

Insert into Article (id, state)
VALUES (1,1)
,(2,1)
,(3,2)

INSERT into Article_history (id, datechange, state)
VALUES (1,'2019-11-10',1)
,(1,'2020-01-15',2);

My query:

 With theDates As
(
    Select DATEFROMPARTS(2019,11,1) as theDate
    Union All
    Select DateAdd(MONTH,1,theDate)
    From theDates
    Where DATEADD(MONTH,1,theDate)<=GETDATE()
)
Select
    Convert(date,dt.theDate,104) as 'date'
    ,arth.id
    ,arth.state
From theDates as dt
left join Article_history as arth
on arth.datechange>=DATEADD(month, DATEDIFF(month, 0, dt.theDate), 0) and arth.datechange<=EOMONTH(dt.theDate)
Option (Maxrecursion 0)

My Result:

date         id   state
2019-11-01   1    1
2020-01-01   1    2

I want to get

 date         id   state
 2019-11-01   1    1
 2019-12-01   1    1
 2020-01-01   1    2

December is missing

To clarify my problem: record from Article can be not in Article_history at all, if so I want to put this record in every month with some defoult values.


texts: Difficulty removing files from filegroup

texts:
I am trying to delete all files from my database from an unused filegroup. There are two files totaling aroun 1.5TB.
I can see that there is now approx 400mb still in these files but I cant see what it is or how to remove it.

select * from sys.allocation_units a --where data_space_id = 6
inner join sys.partitions b on a.container_id = b.hobt_id --type = 1
where data_space_id = 6

--gives me the following result set

SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'72057781944647680' AS [allocation_unit_id], N'1' AS [type], N'IN_ROW_DATA' AS [type_desc], N'72057777021059072' AS [container_id], N'6' AS [data_space_id], N'0' AS [total_pages], N'0' AS [used_pages], N'0' AS [data_pages], N'72057777021059072' AS [partition_id], N'741132535' AS [object_id], N'9' AS [index_id], N'1' AS [partition_number], N'72057777021059072' AS [hobt_id], N'0' AS [rows], N'0' AS [filestream_filegroup_id], N'0' AS [data_compression], N'NONE' AS [data_compression_desc] ) t;
SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages], [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows], [filestream_filegroup_id], [data_compression], [data_compression_desc]
FROM #tmp_GridResults_1

DROP TABLE #tmp_GridResults_1
GO

x

select * from sys.allocation_units a --where data_space_id = 6
inner join sys.partitions b on a.container_id = b.hobt_id --type = 1
inner join sys.objects c on c.object_id = b.object_id
where data_space_id = 6

--returns nothing

So it appears I have an object in the database with no rows taking up 400mb which has no record in sys.objects

I have tried adding a small file to the filegroup and performing an emptyfile operation on the files I want to remove and then performaing a dbcc shrink emptyfile however i get "page could not be moved because it was deallocated during shrink" The page refers to the object contained in allocation units but not in sys.objects

DBCC checkdb fails with "CREATE FILE encountered operating system error 665 (could not be completed sue to  a file system limitation) while attempting to open or create the physical file

Any ideas on how to progress, I would really like to remove these files or at least shrink them down to a much smaller size


texts: How to properly design this type of relationship

texts:
I am building a model with the following requirements:

Items may have attributes
Attributes may be of type: 'Choice', 'FreeText', 'Number'

I am not quite sure how to relate the item to attribute properly. I currently have a table: 'ItemAttribute' which has both the item key and the attribute key and then just a value field which currently is simply a varchar field.

However, I would like to keep the integrity between the 'Choice' and the ItemAttribute which my current design does not help with.

How would you design this?

Thanks so much for your suggestions

Current Schema


texts: Using queries instead of loops to modify database principals

texts:
I need to check for the existence of certain users on some database servers, each of which has over 1,000 databases, and create the users if they don’t exist and assign proper database roles to them.  I already do this currently by using loops (no cursor though!) and dynamic SQL, but I was wondering if there is any way to do it by using queries.  I’m relatively new to writing T-SQL and – to me – dynamic SQL seems ugly.  It seems to me that if there were any way to resolve a variable inside of a query, it would make things easier.  For example, I could use the following query as the inner query of a correlated query where the outer query got a list of databases from master.sys.databases:

select name
from @dbname.sys.database_principals
where type = ‘G’ and name like ‘%SomeUser%’

This doesn't work... but is there any way to do something like this without dynamic SQL?


texts: Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

texts:
I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following:

Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 15:11:21)
Retrieving the total amount of queries within each hour interval for given timestamps. (For example in here, every day from July 13 to 31, total amount queries between hours 10 and 15 are retrieved in hourly intervals (10:21:09 to 11:00, 11:00 to 11:59, 12:00 to 12:59...,15:00 to 15:11:21)

The query is this:

;WITH cal AS
    (
        SELECT generate_series('2-2-2011 00:00:00'::timestamp,
            '1-4-2012 05:00:00'::timestamp,
            '1 hour'::interval) AS stamp
    )
, qqq AS (
        SELECT date_trunc('hour', calltime) AS stamp
            , count(*) AS zcount
        FROM mytable
    WHERE calltime >= '13-7-2011 10:21:09'
        AND calltime <= '31-7-2011 15:11:21'
        AND calltime::time >= '10:00:00'
        AND calltime::time <= '15:59:59'
        AND date_part('hour', calltime) >= 8
        AND date_part('hour', calltime) <= 15
    GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp
    , COALESCE (qqq.zcount, 0) AS zcount
FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '13-7-2011 10:00:00'
    AND cal.stamp <= '31-7-2011 15:11:21'
    AND date_part('hour', cal.stamp) >= 10
    AND date_part('hour', cal.stamp) <= 15
ORDER BY stamp ASC;

And a snippet of the result is like this: (zcount values vary, this is just a by-product of copy-and-paste during editing the question)

stamp                 zcount
"2011-07-13 10:00:00"  123
"2011-07-13 11:00:00"  338
"2011-07-13 12:00:00"  184
"2011-07-13 13:00:00"  298
"2011-07-13 14:00:00"  162
"2011-07-13 15:00:00"  293
"2011-07-14 10:00:00"  216
"2011-07-14 11:00:00"  392
"2011-07-14 12:00:00"  268
"2011-07-14 13:00:00"  483
"2011-07-14 14:00:00"  327
"2011-07-14 15:00:00"  419
.
.
.
"2011-07-31 10:00:00"  216
"2011-07-31 11:00:00"  392
"2011-07-31 12:00:00"  268
"2011-07-31 13:00:00"  483
"2011-07-31 14:00:00"  327
"2011-07-31 15:00:00"  419

My query works well for inputs in which the hour of the first timestamp is smaller than second one -e.g. in here, 10 AM for first timestamp's hour and 3 PM for second timestamp's hour.

However, when I want to retrieve total counts of query per every hour interval for inputs such as 07-13-2011 22:11:43 and 07-31-2011 04:06:04, - so that I get query counts between 10:11:43 pm to 10:59pm, 11pm to 11:59pm,...,3:00am to 3:59, 4:00am to 4:06:04am for every single day,  I'm having problems.

I need to retrieve the total counts of queries like the following for each given day in input:

    stamp                 zcount
    "2011-07-13 22:00:00"  123
    "2011-07-13 23:00:00"  338
    "2011-07-14 00:00:00"  184
    "2011-07-13 01:00:00"  298
    "2011-07-13 02:00:00"  162
    "2011-07-13 03:00:00"  293
    "2011-07-14 04:00:00"  216
    "2011-07-14 22:00:00"  392
    "2011-07-14 23:00:00"  268
    "2011-07-15 00:00:00"  483
    "2011-07-15 01:00:00"  327
    "2011-07-15 02:00:00"  419
    "2011-07-15 03:00:00"  121
    "2011-07-15 04:00:00"  231
     .
     .
    "2011-07-30 22:00:00"  392
    "2011-07-30 23:00:00"  268
    "2011-07-31 00:00:00"  483
    "2011-07-31 01:00:00"  327
    "2011-07-31 02:00:00"  419
    "2011-07-31 03:00:00"  544
    "2011-07-31 04:00:00"  231

What do I need to change in my query in order to make sure it works for both timestamp cases as I presented above?

I currently use a workaround for the second case but I would like to handle all of it in a single query.


texts:
My senior management would like to have some counts of database objects (databases, tables, rows, stored procedures, etc) to show complexity to the executive committee. Does anyone know of a quick way to get this information? I'm going after system tables to get the info and some use of sp_MSforeachdb, but I'm hoping maybe someone has a query stored somewhere they can share. Of course it's needed by tomorrow and it's already 3:43 pm. We're using Microsoft SQL Server 2008 R2. And if anyone has ever been in this boat before, is there anything else your management wanted to see?
TIA


texts: Enable Lock Pages in Memory for SQL Server on VM

texts:
We have several SQL 2005 instances that are in a process of being virtulizaed and upgraded to SQL 2008 R2 running VMWare vSphere 5.1.

Our VMWare admin told me that in our org they do not over allocate memory to the guests. Per him though VMWare recommend to enable "Lock Pages in Memory" for SQL server running on virtual machines.

Does "Lock Pages in Memory" is enabled for SQL Server running on virtual machines?


texts:
http://sqlfiddle.com/#!9/df6245
How to delete all rows Where brand not equal to 107
for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it will delete both two rows of GRPCD 33111 because there is no brand 107 value.
Please help
Edit
This query is working perfect someone can give me better query.
DELETE FROM `products_cross` WHERE GRPCD NOT IN (SELECT GRPCD FROM products_cross WHERE brand=107)


texts: Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL

texts:
I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application.

Is there any way to get only the values of those fields instead of getting values of all fields of SHOW SLAVE STATUS.

My OS is windows.

I am using MySQL 5.0.24


texts:
I need to return only the 1 row of the MAX(ID), but my query is not returning such.

This is the result set that I get returned

id      bbid    indate      st  val
200817  16543   01/01/16    1   65.4
2017    16543   01/01/16    1   36.52
1995    16543   01/01/16    1   18.51
20932   1234    03/01/16    1   55.3
22324   1234    03/01/16    1   16.52
55      8232    05/01/16    2   100

This is what I WANT returned

id      bbid    indate      st  val
200817  16543   01/01/16    1   65.4
22324   1234    03/01/16    1   16.52
55      8232    05/01/16    2   100

(Only one row per bbid, and if bbid has multiple entries then pull the data from MAX(ID))

Here is sample DDL to help troubleshoot

Create Table #One
(
  id int
  ,bbid int
  ,val varchar(100)
)

Create Table #Two
(
  bbid int
  ,st int
  ,indate datetime
)

Insert Into #One Values
('200817','16543','65.40')
,('2017','16543','36.52')
,('1995','16543','18.51')
,('20932', '1234', '55.30')
,('22324', '1234', '16.52')
,('55', '8232', '100')

Insert Into #Two Values
('16543', '1', '2016-01-01')
,('1234', '1', '2016-03-01')
,('8232','2', '2016-05-01')

And this is the query I wrote that produced the incorrect results...

Select
t1.id
,t3.bbid
,t3.indate
,t3.st
,t1.val
FROM #One t1
JOIN
(
    Select
    id
    ,MAX(ID) As "Maxid"
    FROM #One
    GROUP BY ID
 ) t2
ON t1.id=t2.Maxid
INNER JOIN #Two t3
ON t1.bbid = t3.bbid

How should this query be altered to return my desired result set'?


texts: Is better to create a table for each room or make a single table that handles all the rooms in my case?

texts:
I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie.
The process is that 2 users join a room and do some stuff like chatting (calculated an avergate of 50-80 messages per room) and after all users disconnect from room, remove it.

I have done it this way:

When user creates a room, I'm creating a table with roomName and users start doing things and when all disconnect, remove the table.

After a chat with a friend, he told me that is ugly to have a database for each room and it will become very slow when many users are connected in the same time and that I should use a single table and indexes to make it faster and salable and that I also must keep a history of conversations to be able to offer to let's say police if there are asked or stuff like that.

The idea seems ok but don't know if it worth modifying code and what I've done until now. Each room user also must make a request each 2-3-4 seconds and ask for new messages(based on a timestamp sent by the user) and I don't know what to do.

My question is:
Is it better to have a single table to manage all the rooms or it's okay to make a table for each room and after disconnecting to move in a single history table?

Thanks


texts: Difference between row-based and statement-based replication in MySQL?

texts:
What is the actual difference between row-based and statement-based replication. I am actually looking in terms of replication's effect on the slave.

If I am using row-based replication then what is the effect on the slave and if I am using statement-based then what is the effect?

Please also take the following parameters in consideration:

   replicate-ignore-db  and replicate-do-db

Thanks....!


texts: Restore Latest Copy of Full and Differential database From Azure Blob Container

texts:
I'm trying to work out how to Restore the Latest Backup of a Database that is held on Azure Storage Blobs. This container contains other backups from different databases on a server but I'm only interested in Restoring the Latest Full and Diff Backup for a particular Database

I need to do this automatically via powershell or something and schedule it from a job.

I've looked at:
Restore-SqlDatabase but can't see how it will use Azure credentials
DBATools.io - Again nothing to pass in Azure credentials
AzCopy - How do you get the latest Backup?
Azure PowerShell - Again as above.

Any pointers would be greatly appreciated.

Thanks In Advance.


texts: I'm going to start using Mongo as a database but I will not have it clustered at first.  Can I use Mongo on a single machine, then later come back, cluster it and capitalize on the sharding?  Or can I set it up for a cluster with only one machine until I have more to add to the cluster?


texts:
I am about to build our first RAC development environment, and as I'm new to RAC and have not used ASM I wanted to keep things as simple as possible.

I was under the impression that ASM is a pre-requisite of RAC, but reading I've done recently implies it's optional (although ASM offers better performance particularly on reads).

So is a non-ASM RAC environment supported by Oracle? We are using 11.2.0.4 on a RedHat Linux 6 environment.


texts: Oracle profile: PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

texts:
Let's say I have configured PASSWORD_REUSE_TIME=1 and PASSWORD_REUSE_MAX=5 for Orale user profile. My initial understanding was that it should prevent the user from using the same password until he cycle through the 5 different passwords AND at least one day elapsed since this initial password was used.
But here  Oracle states that: "If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED". Link mentioned is for Oracke 8, can somebody explain whether it's applicable to 11g too? Can I use both parameters in a way I described in Oracle 11g?


texts: Why doesn't FIND_IN_SET return on NULL values

texts:
Can anybody explain why FIND_IN_SET isn't returning the row with the NULL value? To me it seems obvious, the string "test", isn't part of NULL, so it should return it?

http://sqlfiddle.com/#!9/389043/2

CREATE TABLE test (
  id INT AUTO_INCREMENT,
  result VARCHAR(255),
  PRIMARY KEY (`id`)
);

INSERT INTO test (id, result) VALUES (1, NULL), (2, 'abc'), (3, 'abc,test');

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result);


texts: Using one sequence across all tables in PostgreSQL

texts:
I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.)

This helps prevent security problems for a multi-tenant application that joins tables incorrectly.

While a uuid could be used for this, bigint is half that size, helping save index space.

My understanding is that sequence generation is very fast in PostgreSQL.
The only downside I can see is widening the type of tables could get by with 4 byte integer IDs, but thanks to PostgreSQL upsert behavior (generating a new value whether or not it's needed), I rarely use int IDs anyway.
Is there an issue with approach? I haven't found anyone advocating for it, but I consider the join safeguard to be quite nice.


texts: Unable to alter partition table to set logged

texts:
I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.

Afterwards, I ran an alter table set logged on the parent table but the table still shows as UNLOGGED along with all its children.

Is this broken?

You can execute these commands to see the issue:

CREATE UNLOGGED TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text)PARTITION BY LIST(cust_country);

CREATE UNLOGGED TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');

CREATE UNLOGGED TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');

CREATE UNLOGGED table customers_def PARTITION OF customers DEFAULT;

INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');

INSERT INTO customers VALUES (4499,'Tony','Arizona','USA');

\d+ customers
checkpoint;
alter table customers set logged ;
\d+ customers;

Throughout the display of the table details the UNLOGGED status doesn't change when running the ALTER TABLE command against the parent table. But even when all the child tables have been SET LOGGED, I'm still not able to change this on the parent table. Tools like pg_partman will look to the parent table to create the child tables with the "correct" properties.


texts:
currently to stop overlapping timestamps of specific event_type in following table:

    CREATE TABLE foo (
        id                    UUID PRIMARY KEY,
        event_type            TEXT,
        from                  timestamp WITH time zone NOT NULL,
        to                    timestamp WITH time zone NOT NULL,
        deleted               timestamp WITH time zone DEFAULT NULL
    );

i'm using:

    ALTER TABLE foo
        ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
        USING gist (event_type WITH =, tstzrange(from, to, '[)') WITH &&);

Above works fine.

Question:

How do i make it so that the constraint does not kick in if the foo had deleted
value populated? so that i can create delete one and create a new one with same dates without the constraint stopping me.
I only want it to stop user from creating overlapping timerange entries of same event_type only if they're not deleted.


texts: MySQL Matching records across tables discrepancies

texts:
I have a database called match, within this database I have a number of tables. Of which in particular I need to marry up two datasets in different tables to identify email addresses that are not contained within the other. I recently had to marry some data so I built a Perl script in terminal to do this, sadly the file I was trying to match had email address as mixed case so I have a discrepancy of around 2,000 rows. Rather than rebuilding the script and waiting hours for it to run using Perl I opted to load it into tables to make my life easier.

The tables in question are called:

clickwithcust - customers I want to exclude - 18,978 records in total.
clickwithoutcust - contains all email addresses from both  clickwithcust and the remaining dataset that i need. - 20,932 records in total.

I ran a query which gives me everyone that has an email address and customer key:

SELECT *
FROM `click_withcust`
INNER JOIN click_withoutcust
ON click_withcust.`email`=click_withoutcust.`email`;

Resulting in 18820 unique rows

Then I wanted to find the inverse of this query so I created the following query:

SELECT *
FROM `Click_withoutcust`
WHERE NOT EXISTS (SELECT DISTINCT `email`
FROM click_withcust WHERE `Click_withoutcust`.`email`= click_withcust.`email`);

This should return the unique number of missing email addresses from the table click_withoutcust. Sadly I get a figure of 2,175 records when in theory I should get 1,954.

I'm at a bit of a lost end here and could really do with some help.


texts: JDBC connection error for SQL Server Database

texts:
I have a SQL server names as MS8AT09\AT09. I am able to login to the server through Microsoft SQL server Management studio. While trying to login to same server through JDBC I am facing the error as

  "The TCP/IP connection to host XXXXXX, Port 1433 has failed.  Verify
  the connection properties.  Make sure that an instance of SQL Server
  is running on the host and accepting TCP/IP connections at the port.
  Make sure that TCP connections to the port are not blocked by a
  firewall".

Please help.


texts: Increasing enough RAM for OS from Total Physical RAM

texts:
Here is my situation:

Total Physical RAM : 128 GB

Assigned to SQL Server : 122 GB (Lock pages in memory for SQL Server Account)

So for OS we have close to 6 GB left now.

Now we are facing issue like below:

  SQL Server:Buffer Manager Page writes/sec has exceeded the threshold
  of 90

Now we are planning to decrease the assigned SQL Server Maximum memory from
122 GB to 111 GB.

I used below links to set maximum server settings for SQL Server.

https://github.com/bornsql/scripts/blob/master/max_server_memory.sql

https://bornsql.ca/s/memory/

Will, increasing Physical RAM for OS level help us ?

When I checked the Task Manager process utilization SQL Server doesnot seems to be using a lot of Memory.
It is servicehosts and Microsoft Monitoring tool that consumes along with SQL Server occupies second and third position.

I know there are plenty of forums which will help to understand the memory logics in SQL Server but may I know where should I start learning about SQL Server Memory utilization.

How can I find what is consuming the most memory in SQL Server ?

Please advice me ?


texts: Why does query error with empty result set in SQL Server 2012?

texts:
When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail since both should have empty result sets. Any help/insight is appreciated.

create table #temp
(id     int primary key)

create table #temp2
(id     int)

select 1/0
from #temp
where id = 1

select 1/0
from #temp2
where id = 1


texts: MySQL check duplicate with group by using wildcard?


texts: How to check multidimensional array of arrays contains array?

texts:
I have an array of arrays [ID, TYPE].

All values are integer. Array can be integer[] or jsonb type.

SELECT array[[442,2],[443,2]]

SELECT to_jsonb(array[[1,2],[3,4]])

How can I check this array(s) contains array [443,2]?

EDIT
One of the way to check described here.

1# SELECT array[[442,2],[443,2]] @> array[443,2] -- returns True
2# SELECT array[[442,2],[443,2]] @> array[2,443] -- returns True too

For my case, I want get False in 2#


texts: Duplicate UNIQUE KEY error after ROLLBACK in MySQL

texts:
I have a table like this

mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id_seudonimo | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| seudonimo    | varchar(45)      | NO   | UNI | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

Let's assume that it is empty so autoincrement is 0. For example:

SET AUTOCOMMIT=0;
BEGIN TRANSACTION;
INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');
ROLLBACK;
SET AUTOCOMMIT=1;

As far as I konw, the rollback does not affect the autoincrement. So if I insert a new value the autoincrement will be 2 instead of 1. But if I try to insert 'Agatha Christie' again, I have the following problem:

INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');

#1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo'

That is not what I expected. I expected this:

+--------------+--------------------+
| id_seudonimo | seudonimo          |
+--------------+--------------------+
|            2 | Agatha Christie    |
+--------------+--------------------+

What's wrong?