texts:
I need to log events of admin changing someone's password, and user changing his password. How do I do that?
I've found a nice solution based on Service Broker, however the resulting table only contains the login of account that made the change, not the affected account.
Could anyone tell me how to alter this part

INSERT dbo.PasswordChangeLog(LoginName)
SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');

So that I'd insert the object of password change, too? I know I will have to ALTER dbo.PasswordChangeLog to add an extra column, of course.


texts:
I've a table the includes two columns ID and age. When view the table, the columns names are in single quotes. That's, 'ID', 'AGE'.
If I tried to do a simple select statement for ID:
SELECT ID FROM table1

I'll be getting an error message of "invalid identifier 'ID' "
What's the solution here?


texts: How does mydumper's ''--use-savepoints" option reduce metadata locking

texts:
MyDumper 0.6.1 add a new option --use-savepoints. From the manual, it means:

  Use savepoints to reduce metadata locking issues, needs SUPER privilege

I don't understand. How does it "reduce metadata locking issues" and why does it need "SUPER privilege"? As I think, metadata is essential to prevent other DDL modifying table structure.


texts:
I would like to ask couple of questions regarding my schema table design.

I have the following data coming from an external database where I do not have any control over it.

PROD_NO  PROD_NAME         COMPANY_ID   COMPANY_NAME    PROD_MARK_NO    PROD_CATALOGUE  PROD_TYPE   PROUDCT_EXPIRY_DATE
MXTP     PLANT_FERTILIZER   BRZL-98     XYZ CO.         MX-FRT          FERT-1          PERISH-01   30-09-2018
RXLG     FARM_FERTILIZER    BRZL-102    ABC CO.         RX-FRT          FERT-6          PERISH-06   31-05-2016

User would like to verify the product information and add two more fields

Verify_date
Comments

How could I normalize the data I receive and put into relational tables?

PROD_NO, COMPANY_ID, PROD_MARK_NO,PROD_CATALOGUE and PROD_TYPE will be always unique.


texts: SQLCMD cannot find the SQL Server remotely

texts:
I am trying to use SQLCMD to remotely detach a database but for some reason it can't find the server. From the workstation, I can connect to the database instance (which is the default instance) using the Host Name through SSMS successfully. The error is:

TCP Provider: No such host is known.
Sqlcmd: Erorr: Microsoft SQL Server Native Client 10.0 : A network-related or instance-
specific error has occurred while establishing a connection to SQL Server. Server is not
found or not accessible. Check if instance name is correct and if SQL Server is
configured to allow remote connections. For more information see SQL Server Books Online.

So the first thing I did was to make sure that the instance is accessible via SSMS (it is), and that my CMD prompt is Administrator (it is). Then I checked to make sure the instance has TCP enabled (it does) and allows remote connections (it does).

What am I missing? The commend I use, minus the script, is:

SQLCMD -D (database) -S TCP:(hostname) -Q (query)


texts: How to add a column with computed columns with values of row on Sybase IQ

texts:
How to add a computed column to a table referring a field from current row at Sybase IQ 16

Here is want I want to achieve:
When inserting a new row (or updating a row) I want to have a column updated based on a function referencing another field. To make an example

I've got this table:

CREATE TABLE "myTable" (
    id integer,
    sales integer,
    bonus integer
)

Where bonus should be calculated as sales * 10 -- remember, just an example.

I was thinking about adding some trigger functions doing this, but then I found computed columns -- unfortunately wasn't able to get this running.


texts: In real terms, how much effect does a bad fillfactor have on performance?

texts:
There's a storm brewing, or maybe I should say firestorm.  A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit.

A list of fires to be put out has been arrived at, and one of them is "incorrect fill factor".  Someone in their infinite wisdom had decided to recreate a multi-billion-record table with over 10 indexes without specifying the FILLFACTOR for the indexes during the recreation (showing in sys.indexes as 0%).

I have always though that page splitting is bad, but in real terms, how bad is bad? Is it worse than not having the index at all? Is there a concrete argument for putting out this fire before the others? Note: it will take a significant amount of time for each index rebuild.

FYI - SQL Server 2012 Enterprise Edition


texts: Check If Record Was Inserted Or Updated In Table

texts:
I have created a trigger that if the length of [name] <= 2 run an update statement.  This trigger fires 98% of the time, which leads me to believe the 2% of the time it does not fire means that the record was Updated() not Inserted()

This is sample DDL and my trigger, is there an issue with my trigger syntax?  Or should I be looking at a way to determine if the record was updated?

Create Table TwoNumericNames
(
  name varchar(500)
)

Insert Into TwoNumericNames (name) Values
('AA'), ('RR'), ('CC'), ('MM'), ('EE')

CREATE TRIGGER trgUpdateName
ON [TwoNumericNames]
FOR INSERT
AS
Begin

UPDATE [TwoNumericNames]
SET [name] = CASE
                WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
                WHEN [name] LIKE '%RR%' THEN 'Raw Red'
                WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
                WHEN [name] LIKE '%MM%' THEN 'My More'
                WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
            END
WHERE LEN([name]) <= 2

END

EDIT
Perhaps an AfterInsert() trigger would work better.  Is this the way it should be set-up?

Create Table TwoNumericNames
(
  id int,
  name varchar(500)
)

Insert Into TwoNumericNames (name) Values
(1,'AA'), (2,'RR'), (3,'CC'), (4,'MM'), (5,'EE')

CREATE TRIGGER [dbo].[trgUpdateName] ON [dbo].[TwoNumericNames]
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- get the last id value of the record inserted or updated
  DECLARE @id INT
  SELECT @id = [Name]
  FROM INSERTED

  -- Insert statements for trigger here
  UPDATE [dbo].[TwoNumericNames]
  SET [name] = CASE
                WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
                WHEN [name] LIKE '%RR%' THEN 'Raw Red'
                WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
                WHEN [name] LIKE '%MM%' THEN 'My More'
                WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
            END
  WHERE [id] = @id

END


texts: show an array of the highest to lowest with column it belongs to from another table

texts:
I have two tables, one for suppliers_list and the other for purchase_orders. I want a query on the suppliers_list table to get the list of suppliers_id and suppliers_cat with a while loop and another query for the second table purchase_orders to get the count of column po_id from the purchase_orders table where suppliers_id in suppliers_list table is equals to suppliers_id in purchase_orders order by the highest count of column po_id. On the purchase_orders table, there is no suppliers_cat column and I want the output to show an array of the highest to lowest with it supplier_cat. Below are my two tables
suppliers_list TABLE

suppiers_id   suppliers_cat
-----------   -------------
    1              A
    2              A
    3              B
    4              C
    5              D
    6              C

purchase_orders
 TABLE

po_id         suppliers_id
-----------   -------------
    1              1
    2              1
    3              2
    4              3
    5              4
    6              5

I want the output to be like below:
C - 2
A - 2
D - 1
B - 1

From above you can see that it shows from highest to lowest with their suppliers_cat first then the count of po_id which is how many times it shows on the table.


texts: Simplifying query by adding Computed Column, performance impact?

texts:
The dev team came across the following query (might be incomplete; it's retyped from a screenshot) which had caused slowness:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END AS cog,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END AS coeff
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END

I'm wondering... if I create a view like such:

CREATE VIEW pwlmy_dcatalog_cog AS
  SELECT *,
    CASE
      WHEN costprice>=basiccostprice THEN costprice
      WHEN costprice<basiccostprice THEN basiccostprice
    END AS cog
  FROM pwlmy_dcatalog

I should be able to simplify the query to the following:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog AS coeff,
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog_cog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog

Do you think the simplified query will have a significant impact to overall performance?

Note: I don't dare add a computed column into the pwlmy_dcatalog table; codes relying on that table might break spectacularly.


texts: Using both DISTINCT and ORDER BY in json_agg

texts:
I'm trying to get a sorted aggregate of unique values in postgresql. SQL Fiddle

The result should be a json array of unique objects, sorted by index field. The value field can be used as unique key in this case if needed.

The structure and data is similar to this:

CREATE TABLE test (
  index INTEGER,
  value INTEGER,
  tag VARCHAR
);

-- In the real query, this table is the result of a join.
-- That is the explanation for duplicated rows.
INSERT INTO test VALUES
  (1, 1, 'a'),
  (1, 1, 'a'),
  (1, 1, 'a'),
  (2, 1, 'a'),
  (3, 2, 'a')
;

Getting an aggregate of distinct rows works:

SELECT json_agg(DISTINCT test.*) FROM test GROUP BY tag;

[{"index":1,"value":1,"tag":"a"}, {"index":2,"value":1,"tag":"a"}, {"index":3,"value":2,"tag":"a"}]

Order by without distinct also works:

SELECT json_agg(test.* ORDER BY index) FROM test GROUP BY tag;

[{"index":1,"value":1,"tag":"a"}, {"index":1,"value":1,"tag":"a"}, {"index":1,"value":1,"tag":"a"}, {"index":2,"value":1,"tag":"a"}, {"index":3,"value":2,"tag":"a"}]

The problem happens if using both:

SELECT json_agg(DISTINCT test.* ORDER BY index) FROM test GROUP BY tag;

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list Position: 42

If I try to add the ORDER BY column I get an error, since json_agg expects only one value:

SELECT json_agg(DISTINCT (SELECT test.index, test.*) ORDER BY index) FROM test GROUP BY tag;

ERROR: subquery must return only one column Position: 28


texts: Shall I use enum when are too many "categories" with PostgreSQL?

texts:
Problem

I plan to load a CSV with more 10 million records into PostgreSQL v12.1, one of its columns has "categorical" values, so creating an enumerated type for it seems to be a good choice, but it holds 208 categories.

The shortest field is 2 and longest is 11 character long. The Average of all fields is 2.4. The character encoding is UTF8, but all characters are ASCII.

Questions:

Which type should I use enumerated or varchar?

Additional info

I discard char because the official PostgreSQL documentation states the followig about char, varchar and text:

  Tip: There is no performance difference among these three types, apart
  from increased storage space when using the blank-padded type, and a
  few extra CPU cycles to check the length when storing into a
  length-constrained column. While character(n) has performance
  advantages in some other database systems, there is no such advantage
  in PostgreSQL; in fact character(n) is usually the slowest of the
  three because of its additional storage costs. In most situations text
  or character varying should be used instead.

An enum value in PostgreSQL occupies 4 bytes on the disk (see 8.7.4. Implementation Details). Considering this and 2.4 average string length using the enum type would lead a slightly higher disk usage (Short stings in PostgreSQL needs one extra byte disk space). Still I have the intiution that using enum is a better choice, because its implementation makes many operations faster against it.


texts: Using SQLite ODBC without installing a driver

texts: I am currently trying to access a SQLite database via an ODBC connection but the system that I am working on is extremely locked down and it is therefore not possible to install the drivers. Is there any way to bypass this?


texts: Statistics histogram AVG_RANGE_ROWS discrepancy

texts:
According to MS docs, the description for AVG_RANGE_ROWS is:

Average number of rows with duplicate column values within a histogram step, excluding the upper bound. When DISTINCT_RANGE_ROWS is greater than 0, AVG_RANGE_ROWS is calculated by dividing RANGE_ROWS by DISTINCT_RANGE_ROWS. When DISTINCT_RANGE_ROWS is 0, AVG_RANGE_ROWS returns 1 for the histogram step.

I'm looking at the last line and if that is indeed the case, I'm curious to know why I'm seeing a value for AVG_RANGE_ROWS that is not equals 1 when DISTINCT_RANGE_ROWS is 0 in the histogram steps.

The statistic in question is a column stat created by SQL Server when the auto create statistics option is turned on. I'm on an older version of the database, but on the latest patch - SQL Server 2014 SP3, CU4+GDR (12.0.6372.1).
It is a bit unfortunate that we almost had a meltdown last week because of a sub-optimal query plan. The end result result was large scans and bloated memory grants. Resampling the statistic with a higher percent value sort of resolved the issue for us for the time being but I'm mostly curious to know if there are exceptions around the initial statement or a known issue (perhaps addressed using a trace flag?) and how do I prevent this from happening again for auto created stats where we have no control over the sampling size?


texts:
Trying to figure out how find out which user records have at least 1 similar option enabled and then display these.

Tables:

user (id, username)
thing (id,title)
uthing (id, uid, tid)

Uthing table has a record of what options each user have selected.

uid = user.id
tid = thing.id

I need to compare the options (uthing) from user id 1 with all other users.

How can find users that have 1 or more options(uthing) of USER ID 1 and return these users with the options found?

This query will give me all options for user id 1.

select thing.title,thing.id as thingid
from thing join uthing on uthing.tid = thing.id where uthing.uid = 1;


texts:
I have two indexes on a table with 786602 rows:

CREATE NONCLUSTERED INDEX [IX1]
ON [dbo].[O] ([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX2]
ON [dbo].[O]([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The following queries both use IX2:

select [c1], [c2], [c3]
from [O]
where A = 9 AND B = 10

select [c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10]
from [O]
where A = 9 AND B = 10

Disabling IX2 shows that query 1 then uses IX1 with the same performance.

Why does the first query not use IX1 as it seems to PERFECTLY match the requirements?

Is SQL Server really that smart and does not use it to show me that it can be removed?  :-)

Is it save to just DROP X1? (that was my original intention so I started testing the usage and just wondered why X1 is never used)


texts:
I think the setting of thread_pool_size = 500  has been installed at my site with the wrong understanding of its application.

Our host server has only 40 CPUs.
Isn't this setting going to be counter-productive?

However, it looks to me like we might benefit from setting it slightly higher than our CPU count.

Am I correct in thinking that we should not set it (much) higher than our CPU count, and never higher than 128, the limit posed in the manual?


texts: Is it possible to make an Update query right after Insert returned new Id's

texts:
I am using MS Sql Server 2008R2.

For example I need to insert many rows inside TableA and I want to make an Update another TableB with newly inserted Ids from TableA.

Is it possible to do in one SQL query?


texts: Too Many database connections on Amazon RDS

texts:
We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze.  The freeze occurs because the query causes the number of database connections to go up to 400+ and basically anytime the site goes over 100 database connections the site slows down terribly and just doesn't respond.

We are running Amazon RDS using MySQL Red Hat Linux

We have a large enough EC2 on the front end app server, and a large enough RDS.

The way we are fixing this issue now is to find the offending query, and kill it.  Once the query is killed...our database connections drop to around 20 which is the normal amount you see when monitoring the site statistics.

Is there a way to stop the offending query and kill it before it runs too long and consumes the connections?  I am trying to automate the killing of the bad query before it happens, or at least realize after 30 seconds its a bad query and kill it.


texts: Adding an additional "order by" column gives me a much worse plan

texts:
in other words, how can I get rid of the sort operator on the picture below?

the picture above shows the execution plan of the following 2 selects together:

                SELECT   TOP 1 so.OrgType,
                              ch.Status,
                              rcs.DBSstatusDescription,
                              cid.ApplicationId
                   FROM   tbl_application_crb_initialData cid

                   INNER JOIN  tbl_season_organisationId so
                          ON   cid.OrganisationId = so.OrgId

                   LEFT JOIN  tbl_crbHistory ch
                          ON  cid.ClientReference = ch.ClientReference

                   LEFT JOIN  ref_crbStatus rcs
                         ON   ch.Status = rcs.statusId

                   ORDER BY  cid.DateAdded DESC, ch.DateAdded DESC

                SELECT   TOP 1 so.OrgType,
                              ch.Status,
                              rcs.DBSstatusDescription,
                              cid.ApplicationId
                   FROM   tbl_application_crb_initialData cid

                   INNER JOIN  tbl_season_organisationId so
                          ON   cid.OrganisationId = so.OrgId

                   LEFT JOIN  tbl_crbHistory ch
                          ON  cid.ClientReference = ch.ClientReference

                   LEFT JOIN  ref_crbStatus rcs
                         ON   ch.Status = rcs.statusId

                   ORDER BY  cid.DateAdded DESC--, ch.DateAdded DESC

the only difference is that on the second query, there is only one column in the order by.

would it make a difference, as I am using top 1?

I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.

if anything else would help to get rid of that sort just let me know, tomorrow I will post all the possible info.


texts: getIndexes is not a function on mongo shell

texts:
I'm trying to get the index of a collection.

> db.version()
3.2.6

> db.getCollectionNames()
[ "last_aggregated_stat_ids", "mapps", "stats", "system.indexes" ]

> db.mapps.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "app_development.mapps"
    }
]

> db.stats.getIndexes()
2016-07-20T16:51:04.601+0200 E QUERY    [thread1] TypeError: db.stats.getIndexes is not a function :
@(shell):1:1

Any ideas of what can happen?


texts: Trying to run query and getting error: Error 1064 (42000)

texts:
I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine.

I am getting  this error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'IN (SELECT
  DISTINCT(flwusertest_users.ID),flwusertest_users.display_name,flwuser'
  at line 1

Here is the code I am using:

select
  flwusertest_users.ID,
  flwusertest_users.display_name,
  flwusertest_users.user_email
from flwusertest_users
INNER JOIN flwusertest_usermeta ON flwusertest_users.ID=flwusertest_usermeta.user_id
WHERE (flwusertest_usermeta.meta_key='_um_last_login'
         and flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132')
AND NOT IN (
  SELECT DISTINCT(flwusertest_users.ID),
    flwusertest_users.display_name,
    flwusertest_users.user_email
  from flwusertest_users
  INNER JOIN flwusertest_em_bookings ON flwusertest_users.ID=flwusertest_em_bookings.person_id
  WHERE flwusertest_em_bookings.booking_status=1);

Any help is much appreciated.


texts:
Trying to wrap my head around what's going on under the hood when integration services is installed, and how that relates to SQL instances on the same machine.

Currently we are aiming to have a full BI stack with each component (SQL Server containing DW, SSAS and SSIS) on separate VMs so resource requirements can be siloed. We want to use the project deployment model for SSIS so are targetting SSISDB rather than MSDB, but the server with SSIS installed doesn't have a SQL instance to host SSISDB, so currently it's installed on the SQL box.

My question is, is it necessary to install a SQL instance on the SSIS box in order to host SSISDB or can it reside just within the integration services instance somehow? If so, does that mean that connecting to the SSIS instance through SSMS is just a legacy feature to manage MSDB or file system packages?

This isn't a question about licensing, it's about infrastructure.


texts: Best methods for ingesting XML into Oracle 11gR2

texts:
I have a large Oracle 11g Instance. I now have a requirement to convert large amounts of XML into records in several related tables.

The xml will arrive frequently at irregular intervals. The documents will vary in size (100megs avg)

The XML is well defined and static in structure.
The documents contains primary elements which should become the records.
Each element has two child elements for related entries.

So for a element that looks like this

 <element>
    <pet>
        <row0>1</row0>
        <row1>Dog</row1>
        <row2>Rex</row2>
        <row3>ownerRow3</row3>
    </pet>
    <owner>
        <row1>Bob</row1>
        <row2>banker</row2>
        <row3>1</row3>
    </owner>
</element>

would be come one "row" in two tables.

What is the most reasonable ( time/money vs "best" ) solution for this relatively simple (and common) task ?

I am not opposed to writing my own "digester" but I do not want to overlook simple solutions ( commercial or free)

My familiarity with available Oracle tools is low and Web searches are not providing a reasonable answer.


texts:
I recently inherited a SQL Server 2014 installation and I'd like to install updates. The virtual machine runs Windows Server 2016 standard. The server has about 50+ databases with 400Gb of data. I'm a developer and don't have much DBA knowledge.

Current plan is to update SQL Server to 2014 SP3 (right now it's RTM). Are there any problems I should be aware of?

I do plan on trying it out on a non-production server first - but unfortunately can't get the same exact Windows version.

Is there some script or utility to healthcheck SQL Server installation? I doubt that manually running sql statements will be very thorough. I only found Brent Ozar's blitz so far.


texts: how do I duplicate a partitioned table in SQL Server?

texts:
I have a database with several tables that are heavily partitioned and I'm trying to optimize part of an ETL operation that involves one of those tables. The table has 1347 partitions involving 35 different file groups. I want to recreate the table in a different database but I don't see a way to get the partition scheme definition or function definition for my new database (it's not included when I script the table out in SSMS). I can get other pieces of the puzzle with the query below but I can't seem to find a way to bring it all together! I've created the new database with all the filegroups and such to match the original.

select distinct
ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue
    from sys.indexes i
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
    join sys.partition_functions pf on pf.function_id = ps.function_id
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('PartitionedTable')

The database is on SQL Server 2016 SP1 CU7 Enterprise Edition


texts:
How do I back up and restore a SQL Azure database?

For that matter, how do I back up and restore an entire Azure account?  Let's suppose I am working at a company and I buy a 3 month trial account before my company comes onboard and says that there is a corporate account for me to work on?  How do I move all my stuff from one account to another?


texts: Cache lookup failed for function 1 while activating PostGIS

texts:
I'm using PostgreSQL 10.5 on a remote server (Debian). I created a database inside then I attempted to enable PostGIS but it wasn't successful.

I had this error : cache lookup failed for function 1.

  PostGIS is a spatial extension used in GIS to allow a DataBase
  Management System manipulate geographical data.

If you have an idea about how to solve this issue, I would be pleased to learn from you.


texts:
MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute distance for like 30k rows if we only need 20.

Well, even after that, queries still take 13 seconds.

SELECT TB.ID ,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong)
  AND MATCH (TA.FullTextSearch) AGAINST ('re*' IN BOOLEAN MODE)
AND TA.Prominent >15
ORDER BY
  Distance

Number of matches are 32 by the way.

Showing rows 0 - 29 ( 32 total, Query took 16.1845 sec)

Also an even simpler query where we do not compute distance also take 16 seconds

SELECT TB.ID
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong)
  AND MATCH (TA.FullTextSearch) AGAINST ('res*' IN BOOLEAN MODE)
AND TA.Prominent >15

Actually if I do not compute distance query is reasonably fast and I can tell client to compute distance anyway.

I can understand if mysql cannot do nearest neighbor search. What I am doing now is a simple search points in a box here. What am I missing?

The explanation for the query is:

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

It basically says only one row is computed for each of the process.

number of data is 1.6 million, but number of data in a box is only 30

Similar query without spatial index took 62 seconds.

SELECT TB.ID
FROM `tablebusiness` AS TB
JOIN tableauxiliary AS TA ON TA.BusinessID = TB.ID
WHERE TB.Latitude
BETWEEN - 6.1928749092968
AND - 6.1771250907032
AND TB.Longitude
BETWEEN 106.7651250907
AND 106.7808749093
AND MATCH (
TA.FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)
AND TA.Prominent >15

So yea there are improvements by using spatial. But not as well as I wish.


texts: Is the following table for a simple music database in 3NF? If not why?

texts:
Im new to databases and I'm struggling to get my head around third normal form (3NF). I have been given a csv file where each record has fields song, album, artist and numerous tags.  I have put the data in a database and want to know if it's in 3NF. The headings of the tables I have inserted the data into are below:

 | Track_Id | Track_Name | Track_Artist | Track_Album |
 | Tag_Id | Tag_Name |
 | Track_Id | Tag_Id |

I believe the candidate keys in the first table are Track_Id and the combination of {Track_Name, Track_Artist, Track_Album}.

I know alternatively I could have a separate song table with a foreign key pointing to an album table which in turn has a foreign key making a reference to an artist table, but is this necessary?


texts: Having a unique ID for 2 different tables

texts:
We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'.
The ID is therefore unique per both tables (combined).
How can we create a constraint for the ID such that the ID appears only once for both tables?

We are using SQL Server.


texts:
I am using MS Sql Server Mgt Studio.

I select a database.
I right click and select Report / Standard Reports / Index Usage Statistics
I get Error: Incorrect syntax near '('

I check some of the other reports display ok.  Others, for example: Top transactions, All Block Transactions,  I get Error: Index(zero baseD) must be greater than or equal to zero and less than the size of the argument list.

Any ideas what could be wrong?


texts: Can someone explain why left joining two views in mysql is so slow?

texts:
Here is a question I asked yesterday - https://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow.

I got a good answer that helped me but I don't understand why the LEFT JOIN is so much slower than the lookup.  The LEFT JOIN was 16 seconds - and I am pretty sure my tables are at least 90% optimized - and when doing the lookup it is just .14 seconds.   When I LEFT JOIN tables it is not this slow so why views?


texts: How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

texts:
Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function already available or I need to do it myself? How to do it?

I need it for Jaro-Winkler string similarity function, for which order of strings/words is important...I need tsvector only because of language support for lexemization. Actually, I just need to leximize text, e.g.

select array_to_string(tsvector_to_array(to_tsvector('english', 'I just want to lexemize this string and keep the original order of lexemes...')), ' ');

This gives me leximized text which is OK, except lexemes are ordered alphabetically instead their original relative order in original text...


texts:
I have 3 tables that are currently defined as:

Is this the best way, or should I use an associative table?

I put the relationships backwards, 1 group can contain many users and many students.  There is no other relationship between the user and the student other than the group.  This is for a tracking program where the user or users are taking a student or group of students through a check-in procedure.


texts: How does SQL Server calculate the initial size of a (compressed) backup?

texts:
When a backup is created, SQL Server guesses(?) the a size of the intial backup file. Later on, maybe when it appends the log the size will be re-adjusted, sometimes multiple times until the final size is reached.
The bigger the difference, the longer the backup takes. (compared to another backup which size isn't adjusted that much later on).
Example:
Database1 (size 500GB, 70 GB log used) is backed up with compression.
The .bak file is created with 85 GB in size, after some time the CPU usage goes up and I can see that the .bak file is re-adjusted to 136 GB, this happens again until the final size of 178GB for the backup is reached.

When these re-calculations happens the average backup speed in MB/s is reduced compared to other backups on the same machine. Is the only coming from appending and clearing the log? Or is it also because of the different data types used in the database? Meaning that they are compressed with different compression rates, or anything?

I came to the topic, because I knew that my backups take around 30 minutes, but now it took 1,5 hours even though the database didnt grow to 300% of it size. It grew only 30%.

Using wait stats I could see that besides BackupIO I was also waiting for CPU (SOS_SCHEDULER_YIELD) at some point.

Machine Details:
VMWare 6.0
32 GB of Memory (Max memory 28GB given to SQL Server)
2 logical CPU
Max Degree of Parallelism (1, it's a Sharepoint 2013)
SQL Server 2014
Windows Server 2012 R2
running on an SSD Raid (5)

Of course I have instant file initialization enabled for the user who runs the backup.


texts: How to get more information from dm_exec_query_stats

texts:
I am running SQL server 2012 on windows azure VM (windows server 2012)

I suspected that some of my web appliactions are attacked via SQL injection,

So far i could not trace the relevant text with my app's logs or with SQL Profiler (found nothing), yet i have a high ammount of traffic so it was not easy to search it and the proccess might have been wrong.

Anyway, i used the following query :

select *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
where text like '%the_sql_injected_text%'

And did find the following :

set ansi_warnings off
DECLARE @T VARCHAR(255),@C VARCHAR(255)

DECLARE Table_Cursor CURSOR FOR
select
    c.TABLE_NAME,c.COLUMN_NAME
from INFORMATION_SCHEMA.columns c,
INFORMATION_SCHEMA.tables t
where c.DATA_TYPE in ('ntext','text')
    and t.table_name=c.table_name
    and t.table_type='BASE TABLE'

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor
INTO @T,@C

WHILE(@@FETCH_STATUS=0)
BEGIN
    EXEC('UPDATE ['+@T+'] SET ['+@C+']=CONVERT(VARCHAR(8000),['+@C+'])+''</title>
Payday lenders and asking payday max loans be the amount you experience it.
'' ')

    FETCH NEXT FROM Table_Cursor
    INTO @T,@C
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

I am intersted in knowing more about the source of the query, is there any more information I can get from it ? How should I go on from here ?

The information I am after may be an IP address, a user name or anything else that can help me find the source of this attack.


texts: How to recreate an Index with the same settings?

texts:
I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.

I do not have the previous version in source control
I didn't find most of them in sys.indexes DMV (FILLFACTOR is there, but not ONLINE)
Finding the index in Object Explorer and scripting it out fills default values for the ONLINE setting (and omits DATA_COMPRESSION)
I didn't find the property in the OBJECTPROPERTY DMF
Brent Ozar's sp_BlitzIndex also doesn't provide some of these advanced settings
sp_helpindex provides me with the same limited information as sp_help over the parent table.
I cannot test if WITH (DROP_EXISTING = ON) recreates the index with the same settings, because I can't find the settings anywhere.

Any ideas?


texts: Performance problems with TOP and user defined functions in views

texts:
I have problem with performance of queries on simple query on view.

To keep things simple, assume my view is defined as follows:

CREATE VIEW [mon].[ViewDevicesWithGroups]
AS
SELECT     Id, Name, Deleted, mon.SlowFunction(Id) AS Groups,
FROM         mon.Devices

And query I'm trying to optimize looks like this:

SELECT TOP 10
Id218_0_, Groups, Name218_0_, DeletedD6_218_0_ FROM (
    SELECT this_.Id as Id218_0_, this_.Name as Name218_0_, this_.Deleted as Deleted218_0_, Groups, ROW_NUMBER() OVER(ORDER BY this_.Name) as __hibernate_sort_row
    FROM mon.ViewDevicesWithGroups this_
    WHERE (this_.Deleted = 1 and not (1=0))) AS query
WHERE query.__hibernate_sort_row > 100000
ORDER BY query.__hibernate_sort_row

My problem is, SQL server keeps executing this query in REALLY wrong order - SlowFunction is really slow, and should be delayed as long as possible:

As you can see, this query was generated by ORM (NHibernate in this case), and I can't change it directly (otherwise this problem would be trivial to solve by just moving function call to outermost select).

Can performance of my query be improved without changes in application code (maybe I kind of hint on view/function)? Because that query is abstracted deeply inside DAL in my application, and changing it would require A LOT of work.


texts: How do you create a new database with new sid on Oracle's pre-built 11g virtual machine?

texts:
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

I noticed the local pre-built database is on port 1521 using SID:XE by default.

Also, in order to access it from an external oracle Java driver I had to use the credentials "hr"/"oracle", which is the default credential for this virtual machine.

My question: Is there a wizard I can use to create a brand new database? Or, if there are commands that do this, what documentation shows how to do this?

I want a new database, with a new username and password, with a new ORACLE_SID.

How do I do this? Is there a link to oracle documentation that shows me how to do this? This virtual machine uses Oracle 11g.

Ideally, I would like to connect to this new database with a Java program using new credentials like:

username:newuser
password:newpass
localhost
1521
ORACLE_SID=newsid


texts: SQL select for a specific node all subnodes

texts:
I'm using mySQL on phpMyAdmin, my table is

| code | name | parent |
|------|------|--------|
|  10  |node10|   5    |
|  5   |node05|   2    |

where 5 is the parent of 10 (which is also a node), I need to select for a specific node A all childs and subchilds until the last leaf, I tried to do that but I've got also 2 levels of subchilds (for example for node 2 I've got 5 and 10)

SELECT *
FROM `composant`
WHERE parent IN(
    SELECT code
    FROM `composant`
    WHERE parent=2 OR code=2
)

so how can we get loop to get all the subnodes ?


texts: Do I need to set tcpip_socket = true to allow remote connections to a postgresql database?

texts:
I am trying to enable remote connections to my postgresql 9.1 db running on ubuntu 12.04.

This tutorial says to "set tcpip_socket to true" -- but it is for a centos server. I am on ubuntu.

I don't see this line included in my postgresql.conf.

$cat /etc/postgresql/9.1/main/postgresql.conf | grep tcpip_socket

And when I add it in, I get the error:

 "FATAL:  unrecognized configuration parameter "tcpip_socket"

Do I need to tell postgres 9.1 to "turn on" tcp_ip connections if its running on ubuntu 12.04?


texts: Are there any variables and formula that can help me calculate CPU/Load value of MySQL ?


texts: Sql Server 2019 Query Hint Placement confusion

texts:
I'm using SQL Server 2019 (CU4) and I'm encountering some strange issues when trying to use a query hint for the legacy cardinality estimation due to a poorly performing Table Value Function.  I'm not sure whether I can create a minimal verifiable example of my problem, but I wanted to put this question out in case anyone else has encountered this issue.  Consider the following query selecting rows from a temp table and invoking a TVF (dbo.AccountHasHomeownerPolicy):
SELECT ST.MONTH_END_DATE
      ,ST.RUN_DATE
      ,ST.ACCOUNT_NUMBER
      ,ST.POLICY_CONTROL
      ,ST.LOB
      ,ST.POLICY_NUMBER
      ,ST.DEC_AMEND
         ,ST.TRANSACTION_LUW
      ,ST.ITEM
        ,HO_EXISTS = AccountHasHomeownerPolicy.AccountHasHomeownerPolicy
      ,AGENCY_HO_EXISTS =
        (CASE
             WHEN EXISTS
            (SELECT 'Y'
           FROM [SCSQLPC].[dbo].MIP_AUS_POL_WRITEN APW
          WHERE APW.LOB='AU'
            AND APW.POLICY_NUMBER=ST.POLICY_NUMBER
            AND APW.LOB_WRITTEN='AH'
            AND APW.EFFECTIVE_DATE.Date=ST.EFFECTIVE_DATE)
          THEN 'Y'
          ELSE 'N'
          END
        )
  FROM #INFORCETABLE ST
  OUTER APPLY dbo.AccountHasHomeownerPolicy
   (ST.ACCOUNT_NUMBER,ST.EFFECTIVE_DATE) AccountHasHomeownerPolicy
   OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

In my testing, I always issue DBCC FREEPROCCACHE before each test run. (I on an isolated test server)
When I specify the HINT, I can display an estimated execution plan which shows everything using the legacy estimator.  However, when I actually run the query and then run sp_blitzcache in another window, the plan that is generated is using cardinality estimation of 150 (SQL 2019) and the query performs poorly.
If a remove the query hint from the main query and put it in the actual TVF, the plan that is generated (as seen from sp_blitzcache) shows the TVF using the legacy cardinality estimator and the query performs quickly.
My expectation would be that the query hint placed at the bottom of the main query (and without any additional query hints) would be in effect for all processes involved for that query, but that does not appear to be happening in this case.


texts:
I have this project I am working on, below is the sample of the text file I need to load into my database.

routes.txt:

"route_id","origin","destination","distance"
1,"ABE","ATL",692.000
2,"ABE","DTW",425.000
3,"ABE","ORD",655.000

flights.txt:

"carrier","flight_number","route_id","departure_time","arrival_time"
"AA",43,1051,"1100","1438"
"AA",43,1182,"1523","1730"
"AA",44,3477,"0710","1527"
"AA",45,1921,"1830","2152"

Here are the tables I created:

create table flights(
  route_id INT NOT NULL,
  carrier  char(2)  NOT NULL,
  flight_number int  NOT NULL,
  departure_time date NOT NULL,
  arrival_time date NOT NULL,
  primary key (route_id),
  index(route_id) )

create table routes(
  route_id INT NOT NULL,
  origin   char(3)  NOT NULL,
  destination  char(3)  NOT NULL,
  distance  decimal(8,3) NOT NULL,
  primary key (route_id),
  index(route_id) )

Here is the the commands I executed to load the data:

LOAD DATA infile '/var/lib/mysql-files/routes.txt'  INTO TABLE routes
  fields terminated by ','   lines terminated BY '\n\r' IGNORE 1 LINES
  (route_id,origin,destination,distance);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

from the result above you see the data are not loaded.

What could be the reason?

I am using MySQL 5.7


texts: Why is this query with WHERE, ORDER BY and LIMIT so slow?

texts:
Given this table posts_lists:

                      Table "public.posts_lists"
   Column   |          Type          | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
 id         | character varying(20)  |           | not null |
 user_id    | character varying(20)  |           |          |
 tags       | jsonb                  |           |          |
 score      | integer                |           |          |
 created_at | integer                |           |          |
Indexes:
    "tmp_posts_lists_pkey1" PRIMARY KEY, btree (id)
    "tmp_posts_lists_idx_create_at1532588309" btree (created_at)
    "tmp_posts_lists_idx_score_desc1532588309" btree (score_rank(score, id::text) DESC)
    "tmp_posts_lists_idx_tags1532588309" gin (jsonb_array_lower(tags))
    "tmp_posts_lists_idx_user_id1532588309" btree (user_id)

Getting a list by tag is fast:

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui');

Bitmap Heap Scan on posts_lists  (cost=1397.50..33991.24 rows=10000 width=56) (actual time=0.110..0.132 rows=2 loops=1)
  Recheck Cond: (jsonb_array_lower(tags) ? 'qui'::text)
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on tmp_posts_lists_idx_tags1532588309  (cost=0.00..1395.00 rows=10000 width=0) (actual time=0.010..0.010 rows=2 loops=1)
        Index Cond: (jsonb_array_lower(tags) ? 'qui'::text)
Planning time: 0.297 ms
Execution time: 0.157 ms

Getting a list ordered by score, limit 100 - also fast:

EXPLAIN ANALYSE
SELECT *
FROM posts_lists
ORDER BY score_rank(score, id) DESC
LIMIT 100;

Limit  (cost=0.56..12.03 rows=100 width=88) (actual time=0.074..0.559 rows=100 loops=1)
  ->  Index Scan using tmp_posts_lists_idx_score_desc1532588309 on posts_lists  (cost=0.56..1146999.15 rows=10000473 width=88) (actual time=0.072..0.535 rows=100 loops=1)
Planning time: 0.586 ms
Execution time: 0.714 ms

But combining the above two queries is very slow:

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui')
ORDER BY score_rank(score, id) DESC
LIMIT 100;

Limit  (cost=0.56..33724.60 rows=100 width=88) (actual time=2696.965..493476.142 rows=2 loops=1)
  ->  Index Scan using tmp_posts_lists_idx_score_desc1532588309 on posts_lists  (cost=0.56..3372404.39 rows=10000 width=88) (actual time=2696.964..493476.139 rows=2 loops=1)
        Filter: (jsonb_array_lower(tags) ? 'qui'::text)
        Rows Removed by Filter: 9999998
Planning time: 0.426 ms
Execution time: 493476.190 ms

Why? How to improve the efficiency of the query?

Definition of the two functions used above:

create or replace function score_rank(score integer, id text)
  returns text as $$
select case when score < 0
  then '0' || lpad((100000000 + score) :: text, 8, '0') || id
       else '1' || lpad(score :: text, 8, '0') || id
       end
$$
language sql immutable;

create or replace function jsonb_array_lower(arr jsonb)
  returns jsonb as $$
SELECT jsonb_agg(lower(elem))
FROM jsonb_array_elements_text(arr) elem
$$
language sql immutable;


texts:
I have the following 2 queries.:

shipManagementInvoiceNetValue:

SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
FROM tckopf AS shipManagementInvoice
WHERE shipManagementInvoice.referenzid = 1
    AND shipManagementInvoice.btyp = 5

shipManagementCreditNoteNetValue:

SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
FROM tckopf AS shipManagementCreditNote
WHERE shipManagementCreditNote.referenzid = 1
    AND shipManagementCreditNote.btyp = 6

but I need the result shipManagementInvoiceNetValue - shipManagementCreditNoteNetValue. I'm using a HXTT JDBC DBF driver that supports more than SQL92.

How can I realize that?


texts: How to get table columns with datatypes (and lengths) using PowerShell?

texts:
I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far:

#Load PSSnapin
Add-PSSnapin *SQL*

#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} |
        ForEach-Object {$_.Columns} |
            Select-Object Name, DataType
$colNames

How to get also datatype lengths for columns?


texts: distrb.exe & Logread.exe using huge SQL.txt file in C:\Users\sqlsrvacct\AppData\Local\Temp folder

texts:
I have the following situation:
SQL server 2008 R2 SP2
I have both the Distribution and Subscription database in this server.
LogReader agent and Distribution agent are running in continuous mode

the following file is growing currently (103GB) with readable data
C:\Users\svc-20121\AppData\Local\Temp\SQL.LOG

sample content extracted from the file opened with emeditor:

distrib         2780-768    ENTER SQLAllocEnv
        HENV *              0x000000000019AA00

distrib         2780-768    EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
        HENV *              0x000000000019AA00 ( 0x00000000006CFE00)

distrib         2780-768    ENTER SQLFreeEnv
        HENV                0x00000000006CFE00

.....
SQLAGENT        2af0-1e24   ENTER SQLGetDiagFieldW
        SQLSMALLINT                  3
        SQLHANDLE           0x00000000040DAE80
        SQLSMALLINT                  1
        SQLSMALLINT              -1150
        SQLPOINTER         0x000000001EBBDDFC
        SQLSMALLINT                 -6
        SQLSMALLINT *       0x000000001EBBDDF0

SQLAGENT        2af0-1e24   EXIT  SQLGetDiagFieldW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  3
        SQLHANDLE           0x00000000040DAE80
        SQLSMALLINT                  1
        SQLSMALLINT              -1150
        SQLPOINTER         0x000000001EBBDDFC
        SQLSMALLINT                 -6
        SQLSMALLINT *       0x000000001EBBDDF0

When i tried to delete it windows will say it is used by LogRead.exe and Distrib.exe and when i stop them it says another unrelated instance on that machine is using it

any help appreciated?


texts: Has anyone tried prepopulating temporal tables with old history data?  Before we decide to use temporal tables we need to be able to import our old history into them.


texts: SQL agent job step's connection managers

texts: Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?


texts: How can i insert the multiple data in table using the existance condition?

texts:
I am able to insert the multiple data in table mytest using the below query. But how can i modify the below query so that everytime it checks the first_name and @id_for_test and if its unique, it does allow to insert into mytesttable.

 BEGIN
    DECLARE @id_for_test INT

    SELECT @id_for_test = ixp_tables_id
    FROM   mytables
    WHERE  table_name = 'test123'

    INSERT INTO mytest
    (
        first_name,
        table_id

    )

    VALUES('f1', @id_for_test),
          ('f2', @id_for_test),
          ('f3', @id_for_test),
          ('f4', @id_for_test),
          ('f5', @id_for_test),
          ('f6', @id_for_test)
END

Currently when i run this multiple time, it will insert the data multiple times. Instead i want,  it doesnot allow to insert the duplicate data matching the first_name and table_id.


texts: Transposing a row data in multiple rows based on multiple columns

texts:
I have data in the database in the below format

And I want output data in this format

BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount    | Timestamp
714              | 135                    |  12010194  |       110 | 102000.00 | 01-01-2020

Basically timestamp in the desired result is '01-' + <'MONTH'> + <'YEAR'>

I tried transposing, pivot, unpivot but unfortunately unable to get the desired result.

Column Names:

Id | BudgetCategoryId | CostCenterDepartmentId | Year | AccountNo | CostCenter | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 | Budget

PS: I am very new in SQL (RDBMS)


texts: What is the best way to store large lists of user statistics?

texts:
I am trying to figure out the best way to store user statistics data in a table. For each user property, there can be anywhere from one to a couple thousand integer values (realistically, these would would average to a number in the hundreds). I know that PostgreSQL allows columns to contain integer[] values, but is this really the best solution when I could potentially be storing a a couple hundred or a thousand integers in one cell?
Here is example table with a few of the user properties I might store in it:
| UserStatistics                                                                                |
|-----------------------------------------------------------------------------------------------------|
| user_id | user_song_likes | user_album_likes | user_artist_likes | user_followers | user_following  |
|-----------------------------------------------------------------------------------------------------|
| 1       | [12,56,34,11,7] | [1,154,32,33,84] | [5,14,54,43,10,2] | [55,63,23,2,4] | [43,78,2,4,22]  |
|-----------------------------------------------------------------------------------------------------|
| 2       | [1,2,3,4,87,89] | [62,71,4,6,12,2] | [13,17,35,76,1,2] | [91,17,7,87,9] | [21,17,79,98,1] |
|-----------------------------------------------------------------------------------------------------|
| 3       | [45,53,42,19,2] | [7,14,144,67,93] | [13,14,2,23,3,73] | [11,56,5,12,1] | [34,91,23,10,5] |
|-----------------------------------------------------------------------------------------------------|

In this example, the user_song_likes array will realistically hold hundreds of values (I scaled down the array for the sake of this example). So will the user_album_likes array. user_artist_likes will most likely come close to a hundred values, but it will definitely surpass that for some users. user_followers will average a between 20 and 80 values, but there are going to be a few select users with tens of thousands of followers.  user_following is going to average between 100 and 2,000 values for almost every user.
All this considered, I think it would be very inefficient to store user statistics with integer[] columns. What are my alternative options? What would be a reasonably efficient way to store this data?


texts: Count events on "user based" sliding window

texts:
So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be clear in the code examples.

I could fix on the min(sale_date) of my data and calculate the windows form there, counting each event:
http://sqlfiddle.com/#!4/7cd69/6

but that would not be a sliding window. However if I fix on the max(sale_date), as this date increases the windows are brought along:
http://sqlfiddle.com/#!4/7cd69/7

In these examples, I am using the following mechanism to obtain the time windows:

SELECT DISTINCT
ma - (level - mod(level, 7)) - 7 + 1 dt_lim_bot,
ma - (level - mod(level, 7)) dt_lim_up,
level - mod(level, 7) slc_id
FROM (select max(dt) ma, min(dt) mi from sales)
CONNECT BY LEVEL <= ma - mi order by 1

(as mentioned, the 7s here could be replaced by any number of days)

Mind the detail: select max(dt) ma, min(dt) mi from sales. Max and min are found regardless if these dates are relevantes for all of my users, which wouldn't be true in a example like: http://sqlfiddle.com/#!4/74f1b/4

I know it will probably not change the result of my initial examples and the problem is probably solved already, though I was curious.

If I try to use select max(dt) ma, min(dt) mi, usr from sales group by usr instead (http://sqlfiddle.com/#!4/74f1b/3), it breaks (infinite loop?). So I figured I should change my connect by too, but couldn't come up with a quick solution.

What would be the correct way to generate these time slices, per user basis?

I am working on Oracle 11r2


texts:
We have two tables that are joined on Social Security #'s, but for the sake of this example, I have altered it to join on name only.  One table holds each individual sale, and the other table holds the employee's annual salary.  I need to compile this data into one table, and only update ONE row from the individual sale table with the annual salary.  How can I update the smallest saleid for each employee?

Below is sample DDL:

Declare @Helper Table (empname varchar(50), saleid int, sal float)

Declare @Helper1 Table (empname varchar(50), sal float)

Insert Into @Helper (empname, saleid) Values
('Number One', 818181),
('Number One', 19213),
('Number One', 919131),
('Number One', 131311313)
,('Number Two', 131313),
('Number Two', 9823),
('Number Two', 24)

Insert Into @Helper1 (empname, sal) Values
('Number One', 44000.00), ('Number Two', 55000.00)

This is my desired output:

empname     saleid  sal
Number One  19213   44000
Number One  818181
Number One  919131
Number One  131311313
Number Two  24      55000
Number Two  9823
Number Two  131313


texts: Impact of changing server machines names with SQL Server 2016 Standard in mirror state

texts:
It's an enterprise network domain.

Two existing Windows server 2016 machines, one each for SQL Server 2016 Standard edition and other is also SQL Server 2016 Standard edition in a passive mirror configuration.

For some reason we need to change both server machine names from say A to B and A1 to B1.

Will this have any impact on SQL Server and its passive mirror access?

What precautions I need to take?

I can understand the connection string with change but I am looking for impact of this change on mirroring configuration particularly


texts: MySQL order by rand but result must exist different specific value

texts:
I'm trying the get random values from SQL but I want results must have minimum one specific value from one column, how can I do it?

For example;

TABLE;

id  |  category  |  name
----------------------------
1   |      1     |  A
2   |      2     |  B
3   |      3     |  C
4   |      1     |  D
5   |      3     |  E
6   |      3     |  F
7   |      2     |  G
8   |      3     |  H

When I query random four data with category id, results not have all different categories, sometimes result coming like this;

SELECT * FROM table WHERE category IN (1,2,3) ORDER BY RAND LIMIT 4

id  |  category  |  name
----------------------------
1   |      1     |  A
4   |      1     |  D
3   |      3     |  C
8   |      3     |  H

Category 2 is missing in this example but I want every category must in results like that;

id  |  category  |  name
----------------------------
3   |      3     |  C <-- One from category 3
4   |      1     |  D <-- One from category 1
7   |      2     |  G <-- One from category 2
5   |      3     |  E <-- All categories ok, it can be random from any of them

Sorry for my english, I hope I could explain what I wanted. Results must have minimum one row from where clause "category IN (X,Y,Z)".

Thank you.


texts: The difference between sys/ and sys@xe (Oracle)

texts: Can anyone tell me in detail,what is the difference between sqlplus sys/ as sysdba and sqlplus sys@xe as sysdba?For the records the database is installed on the same machine as the client.


texts: Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

texts:
My application has a Product and a Purchased table.

The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the purchase table maintains a historical record of the product details.

Purchased.name and Purchased.description fields are taking up too much space. On average, 1 of 30 strings in these fields are unique, and 29 of 30 are duplicates.

I would like to avoid creating a HistoricalProduct table, as many facets of Product change on a regular basis, and that table would grow quite large, too.

Is there a way to get PostgreSQL to manage deduplication of these variable character fields automatically for me?


texts: SQL Server 2014 accounts for Daylight Saving Time now?

texts:
My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference.

CREATE TABLE datetimeoffsetfunction(
                                    servertime DATETIME2,
                                    utc_servertime DATETIME2,
                                    offset_datetime DATETIMEOFFSET,
                                    switch_offset DATETIMEOFFSET,
                                    to_datetime DATETIMEOFFSET
                                      );

INSERT INTO datetimeoffsetfunction
VALUES (  SYSDATETIME(),
          SYSUTCDATETIME(),
          SYSDATETIMEOFFSET(),
          SWITCHOFFSET(  SYSDATETIMEOFFSET(),  '+05:00'),
          TODATETIMEOFFSET( SYSDATETIME(), '+05:00')
          );

The result I got is

servertime:         2016-07-09 23:59:16.4770556
utc_servertime:     2016-07-10 03:59:16.4770556
offset_datetime:    2016-07-09 23:59:16.4770556 -04:00
switch_offset:      2016-07-10 08:59:16.4770556 +05:00
to_datetime:        2016-07-09 23:59:16.4770556 +05:00

The servertime is my system time, and considering the daylight saving time, I can explain why there were only four hour difference between servertime and utc_servertime. But I did expect offset_datetime to be 2016-07-09 23:59:16.4770556 -05:00 unless SQL Serve now accounts for Daylight saving time.

So my question is Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?


texts: Prevent truncate without denying ALTER permission

texts: Is there a way to disallow users from truncating a table without denying ALTER permission on the table? Users should be able to maintain the standard ALTER permissions (adding columns to a table, etc.), but should be prevented from truncating the table.


texts: In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them?

texts:
I've done this with MS SQL Server in the past where data that is not required for disaster recovery for instance is in a separate file group and file and not cloned to save significant space and bandwidth.

Example info on the partial restores we used for SQL Server here: https://msdn.microsoft.com/en-us/library/ms190394.aspx

Is something like this possible with Oracle? We're using RAC in case that matters.

To clarify I'm NOT asking about how to setup disaster recovery. Forget about RAC and DR if that adds confusion. How do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them.


texts: How can I find the difference between queries that return multiple rows?

texts:
I have two tables, website and page, that look like such:

website
id | name
------------
1  | site 1
2  | site 2
3  | site 3

page
id | name | site_id |  deleted
----------------------------------
1  | home|    1     |     0
2  | pg2 |    1     |     1
3  | pg1 |    2     |     0
4  | pg2 |    2     |     0
5  | pg1 |    3     |     1
6  | this|    3     |     1

In the example, site 1 has one deleted page and one active page; site 2 has two active pages; site 3 has no active pages.  The goal is to return a list of websites that have no actives pages.

I have the following query that gives me the number of deleted pages for each site:

SELECT w.id, w.name, count(p.id) AS deleted_pages_num
FROM page p
RIGHT JOIN website w ON p.site_id = w.id
WHERE p.deleted = 1
GROUP BY p.site_id

Where you'd get this result

id| name   | deleted_pages_num
-------------------------------
1 | site 1 | 1
2 | site 2 | 0
3 | site 3 | 2

and vice versa where you'd set p.is_deleted = 0 to get the number of active pages each website has.  I need to find the difference between the two queries so that I can identify which sites have had all pages deleted.  Just totally blanking on this.


texts: Importing a different character set export dump into another DB instance

texts:
I am trying to import a given dump file using oracle imp utility.
However; I am facing the following issues.

C:\Users\AD>imp DB_USER/*****@dev1 file=C:\test\db42312.dmp log=C:\test\import2.log

Import: Release 11.2.0.1.0 - Production on Thu Apr 10 13:37:47 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

IMP-00038: Could not convert to environment character set's handle
IMP-00000: Import terminated unsuccessfully

C:\Users\AD>

My source DB yields following results for the NLS query

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

PARAMETER           VALUE
----------          ---------
NLS_LANGUAGE        FRENCH
NLS_CHARACTERSET    AL32UTF8

SQL>

And my destination DB yields the following results:

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

PARAMETER           VALUE
----------          ---------
NLS_LANGUAGE        AMERICAN
NLS_CHARACTERSET    AL32UTF8

SQL>

The issue that I face while executing the import command seems like a character set issue.

Is it possible to import the database dump onto my new (but blank) schema? Please note that I already have other databases on the same server.


texts: secure way to connect with remote Mysql database

texts:
I created my application using MySQL and it relies completely on the database I have around 190 tables, and I'm afraid of security breaches and another problem is for remote access I have to add the IP addresses of the devices that would use it, and since they change dynamically I will need to regularly update the remote access IP addresses, what is a better option for the connection with the remote MySQL?  Currently I'm hosting it on my account at hostgator.

For now, the application is under a testing period so, I have time to change my approach with the database connection, It's a desktop application created using c# and visual studio 2013.

My question might seem silly but this is my first real life application so, I don't want to be a mess!
Thanks


texts: Can one use npgsql to go direct Postgresql table to Postgresql table

texts:
Migration Task: take data from on-premises Postgresql database and migrate it to Azure Database for Postgresql - Single Server...their PaaS service.
Ngpsql has the COPY command that can be used for exporting and importing in bulk but it uses an intermidary file that is either binary or text. The problem with this is that importing requires one to specifiy the datatypes all over again.
Question: is there a way to bulk transfer from one Postgresql to another for a one-shot migration using npgsql without the intermediate file(s)?


texts: Why is log reserve negative in the SQL Server compensation log when using fn_dblog command

texts: I was using the fn_dblog command and was not able to get why the log reserve was negative.


texts:
I have a table containing various a/b test experiment performance on a daily basis. The table structure looks something like this:

date     | experiment | user_count | user_attr | sales
---------------------------------------------------------
6/1/2014 |      a     |     21     |      x    |   1
6/1/2014 |      a     |     15     |      y    |   2
6/1/2014 |      b     |     29     |      y    |   0
6/2/2014 |      a     |     20     |      x    |   0
6/2/2014 |      b     |     18     |      x    |   3
6/2/2014 |      b     |     31     |      y    |   5

While this table structure seemed to be the most logical one when I created it, I'm learning more about database normalization and it seems like this structure may be inefficient (e.g. repetition of experiments and attributes on each day).

I was wondering if there was a preferred structure for this type of data while preserving the daily performance for each experiment (use case involves monitoring daily trends). Is there a better way to model this?


texts:
I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 indexes and Orders1 table has no indexes which i had replicated with statement(select * into orders1 from orders).

I had checked the space of table Orders and Orders1 where i found the index size is of Orders Table is more than data size and in Orders1 Only i can find Data Size is less.
Question 1:Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)
Question 2:Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?
Question 3:Does Non-Clustered index occupy space on disk or only Clustered Index?
Question 4:How does fill factor effect on storage of these clustered and non-clustered indexes.
How does index occupy size on disk(any references links/books/blog)?

Answers are welcome and greatly appreciated.

Best Regads,
Siva


texts:
I'm doing an update like this:

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

And I want to use the OUTPUT clause to back up my changes.

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
OUTPUT
    inserted.Id, inserted.BirthDate AS New_BirthDate,
    deleted.BirthDate AS Old_BirthDate
    INTO OutputTable
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

What I want to know is if there is a way for the OUTPUT clause to create the table OutputTable or do I have to make sure it already exists before running the statement?


texts:
We have faced an issue where the Dev team is not able to view their reports. The installations were all done by them and we had to jump to see what is wrong. We doubt they installed the Evaluation version and the 180 days are over. The error states thats the issue however, we still want to check the edition installed.

We were able to find the Version from registry which was Version 1.4.6969.7395 (Build 15.0.1102.235)

How to find the edition of Power BI Report Server?


texts: Syncing two (or more) PostgreSQL 9.1 clusters

texts:
This question assumes the use of PostgreSQL 9.1.

Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and a standby synchronous replication server. One cluster is hosted in London and the other is hosted in Tokyo.

I have a web app that directs users to the nearest server to them. So I have two instances of the web app running. Each directly connected to one of the PostgreSQL clusters. What would be the best way to keep the two PostgreSQL clusters in sync with each other? I know I could do synchronous replication between the two masters in London and Tokyo but that would add a long time on top of any write queries because the PostgreSQL server would have to wait for a response from the remote system which would (at best) probably add another 200ms of latency to the whole operation.

Does anyone have any suggestions for solving this issue?

Thank you for any help :).

Edit: Also it would be good if the answer took into out account the fact that I may want to expand the system to include a third or a fourth PostgreSQL cluster. I'm lost on the best way to handle syncing between so many different clusters.


texts:
This fall we will be powering down the whole data center to replace a defective power module. There is no DR site to move anything to, everything last bit of the company resides in one DC at one location.
What is the best way to shut down a 2 node availability group? Should I remove the databases from the AG then power off the servers? When bringing the servers online after the power work is done. Should I have the share witness server up first, then the primary server and second server last? Then re-add the databases to the AG? We run Windows 2008 R2, and SQL Server Enterprise 2012 core edition in a VMware environment.


texts: Is there a cutoff when using datetime and date datatypes in the where clause?

texts:
I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997.

I then have a where clause that reads as follows:

WHERE achieved_date between '20160901' and '20160930'

The records with the 2016-09-30 23:59:59.997 value are not being included in my results when they should be. I mean the transaction was recorded on the 30th of September.

Any ideas if this is a odd datatype issue?


texts: Proxy for MDW data collection on remote servers - SQL Server 2012

texts: I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I give it rights to?


texts: How to audit table permissions as a non-DBA

texts:
I'm not a DBA, but I do administer a schema called SCHEMA_1.

I'm in charge of assigning privileges to the tables that I create in my schema. Since there aren't any sensitive or permission-unique tables in the schema, the DBA has instructed me to assign the same privileges to all tables.

Every table has these privileges:

ROLE_A has SELECT
ROLE_B has SELECT, INSERT, UPDATE, DELETE
ROLE_C has SELECT, INSERT, UPDATE, DELETE
SCHEMA_2 has SELECT

I would like to audit the tables in my schema to ensure that I haven't accidentally assigned the wrong privileges to any tables. I'm not sure how this is normally done by DBA's, but one idea I had was to tabulate the tables and privileges like this:

+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_NAME | ROLE_OR_SCHEMA | SELECT | INSERT | UPDATE | DELETE | OTHER (ERRORS) |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_1    | ROLE_A         | y      |        |        |        |                |
| TABLE_1    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_1    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_1    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_2    | ROLE_A         | y      |        |        |        |                |
| TABLE_2    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_2    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_2    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_3    | ROLE_A         | y      |        |        |        |                |
| TABLE_3    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_3    | ROLE_C         | y      | y      | y      | y      | OTHERS! ERROR! |
| TABLE_3    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+

The reason that I've suggested this sort of layout is because this is how I normally see the privileges when I set them using my GIS software:

Assuming that this is a logical approach (it might not be), how can I do this?

The only tools I have at my disposal are my GIS software and SQL Developer.


texts: Creating Archiving database for old data in MySQL

texts:
I Have a Master and Various Slave Servers.

There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data.

Scenario is :
I want that only Last one year data to be on the servers and all other previous data to be move to archiving system.

Most of the tables are InnoDB and there may also be referential integrity between the tables. (When I purge/Delete the old data from the Servers, which i have moved to the Archive the referential integrity also may fail).

Since I have this data in the Production Servers, what steps should I take to accomplish this with no downtime?


texts: Explaining how to design a database to model the stocks of a firm to my Grand'ma

texts:
My teacher always asked us to create simple but precise databases. Being able to explain it simply but not without leaving the theory "to your wise but aged loved Grand'ma"...
I have to create a database to model the stocks of a firm which commercialize constructing industry products. A same product (Produit) can be bought (Achat) from suppliers (Fournisseur(s)). Client(s) place orders (Commande(s)) which will be used on different construction sites (Chantier(s)). An order is composed (Composition) by severals products. For each of them a discount can be done as a percentage of the selling price. Every order can be delivered in several times. The total quantity of a product can be allocated in several delivery notes (Bon de commande).
This is described in UML in the following pics:

Therefore we should have:

Fournisseur
Achat
Produit
Composition
Commande
Client
Chantier
Livraison
ContenuBL
BonDeLivraison

The scheme modeling the database is the following one

with the following Latex Code

! $Achat(\underline{#IdFourniseur},#IdProduit,PrixVente,Date)$
! $Fournisseur(\underline{IdFourniseur}, NomFournisseur, NumérotSIRET,NuméroTel)$
! $Produit(\underline{IDProduit},PrixVente,QuantiteEnStock)$
! $BonDeLivraison(\underline{NuméroBon},AdresseLivraison)$
! $Commande(\underline{IDNuméroCommande},Date)$
! $Client(\underline{IDClient},AdresseClient,NomContact,NuméroSIRET)$
! $Livraison(\underline{#RefChantier},\underline{#IDProduit},\underline{#NumeroBon},Date)$
! $ContenuBL(\underline{#IDProduit},#NuméroBon,Quantité)$
! $Chantier(\underline{#RefChantier},ResponsableChantier,AdresseChantier)$

Yet, I'm not sure about it, especially because the associations between "classes", are not always shown in the scheme.

I'm not sure about Livraison for instance, which links ContenuBl and
Chantier but therfore Produit and BonDeLivrasion to Chantier
I'm not sure about Client, which as an Association Qualifier and how
to lin it between two classes which are not association classes

Furthermore, I created these relations by reflex. How to be able to explain to my loved Grand'ma why I always use Foreigner Kyes to association classes and not For simple classes ?

What leads to establish the relations I did, if they are right and optimal?


texts: SQL Server sometimes uses a lot more cpu for query

texts:
I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But sometimes (at seemingly random points in time) the server goes into a state where the query takes ~120 seconds to finish. This state can last from a couple of minutes to several hours. I have indications that you can end the slow state by calling DBCC FREEPROCCACHE, but I'm not completely sure. I'm using SQL Server 11.0.3153.

I've used the profiler tool to trace the server. Most of the time the query requires 900 CPU and 500 000 reads, but in the slow state it's 100 000 CPU and 7.5*10^6 reads. Even when the server is in the slow state it only seems to affect connections made by my .NET application, I can still execute the stored procedure quickly via SQL Server Management Studio.

The query looks something like this:

SET @search = REPLACE(@search, ' ', '%');
SET @search = '%' + @search + '%';

SELECT
    ...
FROM
    Table1 t1,
    Table1 t2
    /* The real query uses 3 more joined tables left out here */
WHERE
    t1.id = t2.t1_id
    AND (
        (t1.col1 LIKE @search)
        OR (t2.col1 LIKE @search)
        OR (t1.id IN (SELECT t1_id FROM Table3 t3 WHERE t3.col1 LIKE @search)
        )
ORDER BY
    SomeCol DESC,
    SomeOtherCol DESC
OFFSET @row_offset ROWS FETCH NEXT @row_count ROWS ONLY

Can anyone tell me what's going on here? What can be the cause of the inconsistent behavior?


texts: Select a count of record sharing the same value than record X

texts:
Figure a table with two columns: id, catId
How to select the records having the same catId than the record having id = 3?
Example:
id catId
1   3
2   4
3   4
4   4
5   3

Input: 3

Then catId of {id = 3} is 4

Then count of records having {catId = 4} is 3

Output: 3 records


texts: Merge saying duplicate rows, but can't find any

texts:
When I run the following:

merge into LIBRARYA.TABLEA mergeTarget
using LIBRARYB.TABLEB mergeUsing
on
    mergeTarget.COLA1 = mergeUsing.COLB1
    and mergeTarget.COLA2 = mergeUsing.COLB2
when matched then update set mergeTarget.COLA3 = mergeUsing.COLB3;

It gives the following error:

  SQL State: 21506

  Vendor Code: -788

  Message: [SQL0788] Duplicate row identified for MERGE target TABLEA in LIBRARYA. Cause . . . . . :   The set of rows identified in the USING clause of the MERGE statement identifies the same row more than once in target table or view TABLEA in LIBRARYA specified in the INTO clause. Any row in the target table or view may only be identified once by the input of the USING clause. Recovery  . . . :   Change the data identified in the USING clause such that no rows occur more than once in the target table or view. Try the request again.

However, the following query returns no results:

select COLA1, COLA2
from LIBRARYA.TABLEA
inner join LIBRARYB.TABLEB on COLA1 = COLB1 and COLA2 = COLB2
GROUP BY COLA1, COLA2
HAVING COUNT(*) > 1

Am I missing something, here? Shouldn't that second query detect any duplicates for the merge in the first query?


texts: postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

texts:
Lets say that tableA has column columnA with jsonb data in it like the following:
{
   "A": 5,
   "B": 15,
   "C": 20
}

and tableB has columnA with jsonb data in it like the following:
{
   "B": 10,
   "C": 55,
   "D": 60
}

Is it possible to set it up so that whenever either of the jsonb columns from tableA or tableB change then both jsonB columns will be taken, merged and added to a third table, tableC?
so in this example, if the column in tableB was modified, then what gets stored in tableC will be:
{
   "A": 5,
   "B": 25,
   "C": 75,
   "D": 60
}

The keys and values are retrieved from separate APIs and are unknown ahead of time. I'd like to keep them separate but also have a table that summarizes all the overall values.
Currently using postgresql 12.5 but can move to 13.1 if necessary
thanks in advance


texts: I can't select directly from a table I created

texts:
My client created a user in his Oracle instance for me to install the objects I have created in my local instance. However, this strange behavior prevents me to deliver my project. I guess it's a security/role related issue, but can't figure out what's missing:

CREATE TABLE "test"(A NUMBER);  -- WORKS FINE

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'test'; -- WORKS FINE, object exists

SELECT * FROM test; -- ORA-00942: table or view does not exist

Any help will be greatly appreciated. I need to point out the exact missing configuration to my client.


texts: Why do I have multiple (unassociated) temporal history tables?

texts:
I have been setting up a proof of concept system which has a SQL Server 2017 back end.
The system uses temporal tables to record asset configurations and track changes over time.
I have a Data table which is linked to the history table, let's call it dbo.MSSQL_TemporaryHistoryFor_12345678900.

So far so good. I have two issues:

Today I turned off versioning on the table so I could add a computed column. This was done and turned back on again without errors.

Now I find that I cannot query any historic data from before the change. New data is being added to the history, but there is nothing beforehand.

Looking inside SSMS, I can now see there are multiple history tables, all with the same name but with a hex suffix, e.g. dbo.MSSQL_TemporaryHistoryFor_12345678900_A0B1C2D3. They are not linked beneath the main data table. They are just floating around on their own inside the database. When I queried sys.tables, these are not shown as history tables and are not linked to the main data table.

These tables do contain the historical data which is missing.

The questions I have are therefore:

What do these additional tables represent?
How were they created?
Is there any way to somehow relink these into the main history chain so I can get my historical reporting back?

It's very frustrating so any help you can provide would be gratefully received. Thanks.


texts: Parallel Execution Causing Duplicates [SQL Server]

texts:
There are multiple procedures on our database, handling a "inventory". Let's say each user can store items, and then request those items to be moved to another user's inventory, or to be stacked with other items.

For example, you have 5 wood, here, and 25 here, you can combine them to have 30 wood. Each individual user can send post requests to your c# WebApi, which then runs some checks, and finally executes the necessary procedures.

Everything is checked, quantity, ids, slots, everything is double checked. But here is the problem:

If a user, sends hundreds of parallel requests, with a web debugging proxy, or an automated posting client, since the api manages each request individually, and in parallel, this causes bugs where duplicates are caused. In other words, botting the api, with parallel requests, causes many checks in the procedure to pass, since they happen in parallel. eg: is there enough of this item? check, is there enough space for the item in the target container? check. etc etc...

I tried limiting the requests per X miliseconds on iis, but then again, we use cloudflare, which make all requests look like they come from the CF ip's as we resolve the cf ip in the application level. What about only allowing 1 execution at a time per user in the webapi?

What should I do to prevent this from happenning?


texts: Lookup multiple ids in seperate table with 2 subquerys

texts:
I have a query from a timesheets tables that I am using to select a submitter, approver, and a date.

SELECT PRTIMESHEET.POSTED_TIME,
  PRTIMESHEET.PRRESOURCEID as "SUBMITTER",
  PRTIMESHEET.PRAPPROVEDBY as "APPROVER"
FROM PRTIMESHEET
WHERE rownum < 10;

In 1 table I have a list of all employees.  The PRTIMESHEET.PRRESOURCEID and PRTIMESHEET.PRAPPROVEDBY both link to SRM_RESOURCES.ID.

That table has data like the following.

How can I run 1 query to combine the 2 tables and get the names instead of IDs?  something like this:

I have tried a subselect like this to do joins, but with no luck.

select prtimesheet.posted_time as "TIMESHEET DATE",
  (
  select prtimesheet.prresourceid as "SUBMITTER"
  from prtimesheet
  where prtimesheet.prresourceid = srm_resources.id
  ),
  (
  select prtimesheet.prapprovedby as "APPROVER"
  from prtimesheet
  where prtimesheet.prapprovedby = srm_resources.id
  )
FROM prtimesheet, srm_resources;

I am getting the following error: ORA-01427: single-row subquery returns more than one row


texts: How do I track logins, connections, user accounts, queries, ...?

texts:
I am a BI person who has been asked to step in and pretend I am a DBA.  I am not a novice with databases but AM a novice at the admin type stuff.

We have a SQL Server database that is going to be decommissioned.  I have been asked to determine who is using the server, the databases that are being used and the queries being run.

So far I've created a table to track server logins as well as check number of connections as referred to @ http://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/

However, once I had that info I was asked to track:

The user account or credentials being used to connect to the server
the database(s) being queried
The IP address or DNS name from which the connection was made
The SQL being used

They want to know specifically who is using any databases on the server so they can determine which need to be migrated and who they need to contact as the database is migrated.

Any help is GREATLY appreciated!


texts: Creating index on a big text column in postgres

texts:
We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE.

The column is of type text and it is used for ~~'%zzz%' queries. (Searching inside log messages).
Query:

select * from tbl WHERE ( col ~~ '%zzz%')

Trying to create a btree index on the column

CREATE INDEX tbl_col_btree_idx ON tbl USING btree(col);

returns:

ERROR:  index row requires 8456 bytes, maximum size is 8191

Tried to follow the suggestion here: https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528 and create a gin/gist index.

The gist index creation attempt

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

also failed with maximum size problem.

The gin index creation attempt

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

failed with the error:

ERROR:  invalid multibyte character for locale.
The server's LC_CTYPE locale is probably incompatible with the database encoding.

This seems just over-complicated for a simple ordinary need of running search queries over texts.
I just want to improve the performance of the ~~'%zzz%' queries. Any "simple" way to do it?

UPDATE:

I've managed to find a workaround for now. Query performance improved by 3 times by:

Changing the query to:

SELECT * FROM tbl WHERE to_tsvector('english', col) @@ to_tsquery('english', 'zzz');

And creating the following index (this time creating the index worked with the error below):

CREATE INDEX text_ind ON tbl USING GIN (to_tsvector('english', col));

However when creating this index, it ignored some data with the error:

Word is too long to be indexed. Words longer than 2047 characters are ignored.

which is a little problematic because might make the searches miss some results.

NOTE: The mentioned workaround is not considered as the solution to the original problem; I am still looking for answers.


texts: How to tell MySQL to ignore all indexes for a given query?

texts:
I've been benchmarking several queries and learning about indexes by using the explain function, profiling and the SQL_NO_CACHE option.

My usual benchmarking session goes something like this:

SET PROFILING=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Some times after adding indexes, I want to know how the query would perform without any index optimization; so my options so far are to either delete the related indexes or use explain to check what indexes are in use and hint MySQL what indexes to ignore;

EXPLAIN SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SELECT SQL_NO_CACHE IGNORE INDEX (field_a) field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Is there a similar option to tell MySQL not to use any index so I can get the query duration without any index optimization. Something like this:

SELECT SQL_NO_CACHE SQL_NO_INDEXES field_a, field_b FROM table WHERE xxx;


texts: Failover from Primary to Asyn Secondary Replica

texts:
In my environment we have 1 Primary 1 Synchornous Secondary  and 1 Asynchronous Secondary Replica (Off site). We are planning to do a manual failover to the Asynchronous Secondary Replica (Off site) as the Primary data center will be down due to maintenance.

My question is will there be any problem if I manually change the Availability Mode to Synchronous mode from Asynchronous mode and do the failover


texts:
I'm trying to dump oplog.rs from local database, based on timestamp.
Query directly from mongo shell works but not from mongodump

Query from mongo shell

RS1:PRIMARY> db.oplog.rs.find({"ts" : {$gt : Timestamp(1591199252, 0)}})
{ "ts" : Timestamp(1591199252, 1), "t" : NumberLong(2), "h" : NumberLong(0), "v" : 2, "op" : "n", "ns" : "", "wall" : ISODate("2020-06-03T15:47:32.975Z"), "o" : { "msg" : "periodic noop" } }
...

Mongodump ends either with an error or with 0 dumped documents (depending on single quotes for timestamp value) :
1. Output with error

$ mongodump $credentials -d local -c oplog.rs --query '{"ts": {"$gt": Timestamp(1591199252, 0)}}' --out ~/test/
2020-06-11T16:54:36.317+0200    Failed: error parsing query as Extended JSON: invalid JSON input. Position: 15. Character: T

2. Output with 0 dumped documents

$ mongodump $credentials -d local -c oplog.rs --query '{"ts": {"$gt": "Timestamp(1591199252, 0)"}}' --out ~/test/
2020-06-11T16:59:28.264+0200    writing local.oplog.rs to
2020-06-11T16:59:30.232+0200    done dumping local.oplog.rs (0 documents)


texts:
Is there a way to define/set/increase the query cache size in SQL Server 2014 similar to what MySQL has

http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html


texts:
My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
#

I used following Streaming Replication - PostgreSQL wiki to set up replication between master/slave and now after a while I end up with following:

-bash-4.2$ du -h ~/archive/
19G /var/lib/pgsql/archive/
-bash-4.2$ ls -la ~/archive/ | head
total 19169360
drwxr-xr-x.  2 postgres postgres    61440 Jun  6 12:10 .
drwx------. 11 postgres postgres     4096 Jun  6 11:21 ..
-rw-------.  1 postgres postgres 16777216 Feb  6 10:19 0000000100000008000000AE
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000AF
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B0
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B1
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B2
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B3
-rw-------.  1 postgres postgres 16777216 Feb  6 10:33 0000000100000008000000B4
-bash-4.2$

should I setup a cron job to kill old archives? or there is postgres way of doing this?


texts: How to list all table columns in sorted order?

texts: I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?


texts: Should I query from a snapshot or use a backup for reporting purposes

texts:
I have a very large select query for reporting purposes that can take up to 1-2 hours to run.

There are a few issues with this query.

It takes up too much system resources and this server can't handle the load, the DB is also shared on the same server with 30 other dbs.
The query is very large and read/write performance suffers, sometimes the tables are locked so I can't update.

The database is used in an application and for reporting purposes. We have another server in a different location where we can host the DB.

The application runs fine as is on this server, the issue comes in with the reporting. My solution is to

solution 1:

Create the same DB on the other server for reporting purposes.
Run timed jobs to to place a backup file in a shared location
Back up the DB from the back up file that is stored in the shared location.

One of the DBA's is telling me this is too much work unless

solution 2:

We move the application DB to the other server.
Create the copy DB and host both DBs on the same server.
Run a timed job for back ups
And then Back up the reporting DB.

I fail to see how this is a too work. I do DB back ups all the time but I've never done one from a file in a shared location. But it seems like it's the same process to me.

If solution 2 is in fact a more hassle free solution, why is that so?


texts: Filtering by minimum value per group problem

texts:
I have a select statement, which when simplified produces the following:

+------------+----------+-----+
| Group      | Name     | ID  |
| vegetables | potatoes | 1   |
| vegetables | carrots  | 6   |
| fruit      | apples   | 100 |
| fruit      | mangoes  | 97  |
+------------+----------+-----+

I would like it to produce this:

+------------+----------+----+
| Group      | Name     | ID |
| vegetables | potatoes | 1  |
| fruit      | mangoes  | 97 |
+------------+----------+----+

Turning the original select into a table is not an option. Ideally for readability, I'd like to not copy/paste the select; I'd rather alias it somehow but I feel like my syntax for that isn't quite right.

Here's my actual code. It is a horrible mess (actually if you have any suggestions for making it less of a mess that'd be great). It gets ORA-00942: table or view does not exist on the top layer - the layer below that works fine (producing the first table above, essentially)

-- first record of each brand name that has been interchanged
SELECT *
FROM
(
-- all brand name  records that have been interchanged
SELECT DIN, DRUG_INFO_NOS.TRADE_NAME tn, FORM, GENERIC_NAME, MANUFACTURER, CR_DATE, NOC_DATE, FIRST_CLAIM_DATE, DRUG_INFO_NOS.SEQUENTIAL_ID blah
FROM
DRUG_INFO_NOS
INNER JOIN
-- Brand name drugs that have been interchanged.
(
SELECT DRUG_INFO_NOS.TRADE_NAME FROM
DRUG_INFO_NOS
INNER JOIN
-- seq_ids corresponding to interchanges
(SELECT MIN(SEQUENTIAL_ID) seq_ID FROM
 (
 --set of sequential ids not belonging to brand names
SELECT * FROM DRUG_INFO_NOS
WHERE DRUG_INFO_NOS.SEQUENTIAL_ID NOT IN
(
--set of all sequential ids belonging to brand names
SELECT SEQUENTIAL_ID FROM
DRUG_INFO_NOS
WHERE
DRUG_INFO_NOS.TRADE_NAME IN
(
--set of brand names
SELECT TRADE_NAME FROM
(
(SELECT MIN(SEQUENTIAL_ID) as seq FROM DRUG_INFO_NOS
GROUP BY GENERIC_NAME) a1

INNER JOIN
DRUG_INFO_NOS
ON a1.seq = DRUG_INFO_NOS.SEQUENTIAL_ID
  )
  )
  )
  )
  GROUP BY GENERIC_NAME) interchangeSeqIDs

  ON interchangeSeqIDS.seq_ID = DRUG_INFO_NOS.SEQUENTIAL_ID + 1) interchangeddrugs
ON DRUG_INFO_NOS.TRADE_NAME = interchangeddrugs.TRADE_NAME) interchangedbrandrecords
INNER JOIN
(SELECT tn, MIN(blah) minseq FROM interchangedbrandrecords GROUP BY tn) filter
on filter.tn = interchangedbrandrecords.tn;