texts: SUM of DATALENGTHs not matching table size from sys.allocation_units

texts:
I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken?

SELECT
SUM(DATALENGTH(Field1)) +
SUM(DATALENGTH(Field2)) +
SUM(DATALENGTH(Field3)) TotalSizeInBytes
FROM SomeTable
WHERE X, Y, and Z are true

I used this query below (that I got from online to get table sizes, clustered indexes only so it doesn't include NC indexes) to get the size of a particular table in my database. For billing purposes (we charge our departments by the amount of space they use) I need to figure out how much space each department used in this table. I have a query that identifies each group within the table. I just need to figure out how much space each group is taking up.

Space per row may swing wildly due to VARCHAR(MAX) fields in the table, so I can't just take an average size * the ratio of rows for a department. When I use the DATALENGTH() approach described above I only get 85% of the total space used in the query below. Thoughts?

SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB,
((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM
    sys.tables t with (nolock)
INNER JOIN
    sys.schemas s with (nolock) ON s.schema_id = t.schema_id
INNER JOIN
    sys.indexes i with (nolock) ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p with (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a with (nolock) ON p.partition_id = a.container_id
WHERE
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND i.type_desc = 'Clustered'
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB desc

It has been suggested that I create a filtered index for each department or partition the table, so I can directly query the space used per index. Filtered indexes could be created programmatically (and dropped again during a maintenance window or when I need to perform the periodic billing), instead of using the space all the time (partitions would be better in this respect).

I like that suggestion and would typically do that. But to be honest I use the "each dept" as an example to explain why I need this, but to be honest, that is not really why. Due to confidentiality reasons I can't explain the exact reason why I need this data, but it's analogous to different departments.

Regarding the nonclustered indexes on this table: If I can get the sizes of the NC indexes, that would be great. However, the NC indexes account for <1% the size of the clustered index, so we are ok not including those. However, how would we include the NC indexes anyway? I can't even get an accurate size for the Clustered index :)


texts: Audit trigger 91plus: setting up with proper role?

texts:
One of the solutions available for auditing in PostgreSQL is Audit trigger 91plus.

wiki page
github page

There is a section in the wiki that is confusing to me:

  Changes by the table owner and superusers are tracked, but can be trivially tampered with.

Ok... I am not sure if this means someone with access rights to the database via console or GUI or if this means something else...

  If you want this audit log to be trustworthy, your app should run with a role that has at most USAGE to the audit schema and SELECT rights to audit.logged_actions. Most importantly, your app must not connect with a superuser role and must not own the tables it uses. Create your app's schema with a different user to the one your app runs as, and GRANT your app the minimum rights it needs.

Apologies for my lack of understanding: I don't understand the picture here. If I have a web app using a PostgreSQL db, what exactly are the dangers if I use Audit trigger 91plus without the different role the wiki talks about?


texts: Finding and fixing InnoDB index corruption

texts:
I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a secondary index is returning incomplete results. Querying a child table by the parent id was returning 498 rows when it should have returned 504. Querying the missing 6 rows by primary key worked, and the correct parent id was returned, so the problem is with the secondary index.

This problem is highly concerning to me since presumably even if all of the data on the slave matches the master I will still get incorrect results from some queries run on the slave.

My brute force solution was to rebuild the entire table like this:

alter table my_table engine = innodb;

That resolved the issue for this specific table, but I'm left with the following questions:

1) How can I determine if other tables have similar index corruption?
2) What's the most efficient way to fix the corrupt indexes?

I've found some good resources online for finding and fixing InnoDB data corruption, but haven't found anything relevant for InnoDB index corruption.

I looked in the MySQL error log and didn't find a smoking gun. I did find some troubling InnoDB errors. I'm assuming this is a separate issue, but it could be related:

130109  9:46:41  InnoDB: unable to find a record to delete-mark
InnoDB: tuple DATA TUPLE: 2 fields;
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a1c03bd; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 04af1f21; asc    !;;
 1: len 4; hex 0a052a77; asc   *w;;


texts: Questionable duplicate index suggested by optimization

texts:
Good day, I have the following sql server database table:

Please note the compound primary key.  This was done for 3 reasons:

Prevent duplicate entries
Improve query performance as all queries will have all of thoes 3 keys.
We needed and index, and I did not want to introduce a random ID.

Please also note that this table was designed with its size in mind, This table is going to store millions and millions of rows of data.

OK now for my actual question. I am using azure sql server to host this db. and I have enabled automatic tuning. And strangely enough I see that it then went and created a new index. (see below)

Now In my mind this seems to be a duplicate index, as the same columns are being indexed.

So I now have two indexes on my table:

Original (My PK):

ALTER TABLE [dbo].[SensorDataRaw] ADD  CONSTRAINT [PK_SensorDataRaw] PRIMARY KEY CLUSTERED
(
    [DateTime] ASC,
    [SensorId] ASC,
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Newly Added (Auto Created by azure tuning):

CREATE NONCLUSTERED INDEX [nci_wi_SensorDataRaw_DC9789077DA75B4440AC8BFE3E2AA198] ON [dbo].[SensorDataRaw]
(
    [Key] ASC,
    [SensorId] ASC,
    [DateTime] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Observations:

The order of the coloumns has been reversed in the new index.
The new index is NOT unique
the new index includes the value column.

Please note my knowledge on indexes is not advanced, hence me asking this.

So my question is:

Can someone explain why the newly added index is better than my intially created one.
How can I remove the two indexes and just create one that covers both cases. With this being such a massive database I cannot afford the space that both these indexes will take up.
Is the maybe a better design alternative?

Additional Info:

I'm assuming the type of queries becomes important here, So I have listed some examples.

All queries include DateTime, SensorId, and Key.

Simple Queries:

Select SensorId Where average value for key w is greater than x where time between (y,z)

Graphing Data:

SELECT AVG([Value]) AS 'AvgValue',
    DATEADD( MINUTE,
    (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
[DateTime]) / @IntervalInMinutes) * @IntervalInMinutes,
        '1990-01-01T00:00:00'
    )      AS 'TimeGroup'
 FROM [dbo].[SensorDataRaw]
 where
    [dbo].[SensorDataRaw].[SensorId] = @SensorId
    and [dbo].[SensorDataRaw].[Key] = @KeyValue
    and [dbo].[SensorDataRaw].[DateTime] Between @DateFrom and @DateTo
    and [dbo].[SensorDataRaw].[Value] IS NOT NULL
  GROUP BY (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
    [DateTime]) / @IntervalInMinutes)


texts: Preserving some data on rollback in InnoDB?

texts:
I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.

Now, I also want to use transactions for access to all the other tables. So there arises the problem - if I rollback the transaction (or transaction do it by itself due to deadlock or whatever), my INSERT INTO log_table logs are also lost on rollback, which is a big problem.

I used to work around this by using MyISAM table for log_table which worked pretty good (as MyISAM doesn't support transactions, INSERTs into it were immediate, and were not undone on transaction rollback). However, MyISAM tables are on the way out in new versions, and are not really working all that good in cluster/replication/hotbackup environment which I want to pursue, so I am looking for another solution.

Only one I've found is using two concurrent connections to the MySQL server (one for INSERT INTO log_table with autocommit=1, and another one for running all the other stuff in BEGIN/COMMIT transactions). However that solution is problematic due to having a high number of connections (and per-connection resource usage) already.

Any other ways to preserve such log across rollbacks?

Note: I do not want to spool logs locally on the client running the application if at all possible (due to multitude of reasons; including clients low or nonexistant resources [thin client-alike], flaky connections [I want to know at which point big transactions die] and problematic low-tech users [who WILL try to kill clients mid-run if they can do it undetected]. While some of that issues can be worked around, that is a lot of extra work).


texts: String manipulation tools to populate a variable for two "functions"

texts:
Versions: SQL Server 2008 R2 - 2014

I'm using a server audit to find changes to logins on my SQL Server instances, and writing it to the ERRORLOG directory path. Unfortunately, not all the instances use the same path for the ERRORLOG directory. I can get the path using SELECT SERVERPROPERTY('ErrorLogFileName') but I need to "insert" the return value of that statement to two other places. The first is when I'm creating the Server Audit:

CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL\Log'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)

So, I need to "insert" the results of the SERVERPROPERTY query into a variable or...something, so I can use it in the FILEPATH value above. Secondarily, I also need to find the ERRORLOG directory path when reading the *.sqlaudit files the Server Audit creates. I can easily use the following to get files, but here too, I need to "insert" the filepath vaule with the result of the SERVERPROPERTY query:

SELECT *
FROM sys.fn_get_audit_file('Filepath\*.sqlaudit',DEFAULT, DEFAULT)

I have tried some "google-fu" and have looked at a couple of string functions, like replace and the "trim-family"...which wasn't very relevant. If anyone can help or point me to a good article or two on how to achieve this, I would greatly appreciate it.


texts: How should GROUP BY clause be used in this query?

texts:
I'm new to PostreSQL, trying to learn how I'd take a query like this:
SELECT (cartype_id = 7) AS "is_position_paper",
(cardate IS NULL) AS "is_null_date",
(
SELECT COALESCE(shorttitle, '')
FROM office oc
JOIN office_manufacturer oc2 USING (office_id)
WHERE oc2.manufacturer_id = manufacturer.manufacturer_id
AND officemanufacturerstatus_id = 1
ORDER BY rank
LIMIT 1
) AS "shorttitle",
"car_manufacturer"."car_manufacturer_id",
"car_manufacturer"."car_id",
"car_manufacturer"."manufacturer_id",
"car"."car_id",
"car"."cartype_id",
"car"."title",
"car"."cardate",
"car"."location",
"car"."key",
"car"."quote",
"manufacturer"."manufacturer_id",
"manufacturer"."firstname",
"manufacturer"."middlename",
"manufacturer"."lastname",
"manufacturer"."nickname",
"manufacturer"."manufacturerpreferredname_id"
FROM "car_manufacturer"
INNER JOIN "manufacturer"
ON ("car_manufacturer"."manufacturer_id" = "manufacturer"."manufacturer_id")
INNER JOIN "office_manufacturer"
ON ("manufacturer"."manufacturer_id" = "office_manufacturer"."manufacturer_id")
INNER JOIN "office"
ON ("office_manufacturer"."office_id" = "office"."office_id")
INNER JOIN "car"
ON ("car_manufacturer"."car_id" = "car"."car_id")
WHERE ("office"."officetype_id" = 'P' AND "office_manufacturer"."officemanufacturerstatus_id" = 1 AND "office_manufacturer"."state_id" = 'NA' AND "car"."release_id" >= 2)
ORDER BY "is_position_paper" ASC, "is_null_date" ASC, "car"."cardate" DESC

The duplicates are occuring on "car"."car_id" wherein there could be many "manufacturer"."manufacturer_id" for one "car"."car_id".
I would like to be able to know when a "car"."car_id" has many "manufacturer"."manufacturer_id" make some some of annotation or column with that info, and then also nix those duplicate "manufacturer"."manufacturer_id" from the queryset.
My understanding is that I should use GROUP BY on the "car"."car_id" but I can't get it to work, I keep getting errors about columns needing to appear in the GROUP BY clause, like this:
column "car.cartype_id" must appear in the GROUP BY clause or be used in an aggregate function

Once I add the following, now I'm getting the following error:
ERROR:  subquery uses ungrouped column "manufacturer.manufacturer_id" from outer query

Is there a way to use an aggregate function to do this kind of deduping whilst providing a new column indicating which car.car_id was deduped?


texts: PostgreSQL - Querying Second Table with Multiple Results

texts:
I am working in PostgreSQL 9.6.6 on a query that involves the use of a second table to return additional data points. The table - "events" - is associated with many entries from the second - "activities" - via a foreign key.

Events

| id | name   |
|----|--------|
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |

Activities

| id | name      | startDate              | endDate                | status    | eventId |
|----|-----------|------------------------|------------------------|-----------|---------|
| 1  | activity1 | 2018-08-27 05:00:00+00 | 2018-08-28 05:00:00+00 | Draft     | 1       |
| 2  | activity2 | 2018-09-27 05:00:00+00 | 2018-09-27 10:00:00+00 | Submitted | 1       |
| 3  | activity3 | 2018-08-25 05:00:00+00 | 2018-08-27 10:00:00+00 | Draft     | 1       |
| 4  | activity4 | 2018-08-21 05:00:00+00 | 2018-08-24 05:00:00+00 | Approved  | 2       |
| 5  | activity5 | 2018-09-27 05:00:00+00 | 2018-09-29 05:00:00+00 | Draft     | 2       |
| 6  | activity6 | 2018-10-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Approved  | 3       |
| 7  | activity7 | 2018-08-27 05:00:00+00 | 2018-08-27 10:00:00+00 | Approved  | 3       |

The data points being collected via the event are a startDate, endDate & status based on the associated activities.

startDate => The least startDate of all associated activities.
endDate => The greatest endDate of all associated activities.
status => Status equals "Pending" unless all associated activities
have a status of "Approved" (then status equals "Complete").

An example returned query would look like:

| id | name   | startDate              | endDate                | status    |
|----|--------|------------------------|------------------------|-----------|
| 1  | event1 | 2018-08-25 05:00:00+00 | 2018-09-27 10:00:00+00 | Pending   |
| 2  | event2 | 2018-08-21 05:00:00+00 | 2018-09-29 05:00:00+00 | Pending   |
| 3  | event3 | 2018-08-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Completed |

I have formulated the following with multiple subqueries, but the performance is terrible.

Any suggestions as to how can I improve this to limit (or even remove) the use of subqueries?

SELECT DISTINCT ON("startDate", "event"."name") 1, "event"."id", "event"."name", (
  SELECT "activities"."startDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."startDate" ASC
  LIMIT 1
) AS "startDate", (
  SELECT "activities"."endDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."endDate" DESC
  LIMIT 1
) AS "endDate", (
  SELECT DISTINCT ON("event"."id")
    CASE "activities"."status"
    WHEN 'Draft' THEN 'Pending'
    WHEN 'Submitted' THEN 'Pending'
    ELSE 'Complete'
    END
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."status" DESC
) AS "status"
FROM "events" AS "event"
ORDER BY "startDate" DESC, "event"."name" ASC
LIMIT 20
OFFSET 0;


texts: "Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060" while altering the table

texts:
I am trying to alter a column in a table. The existing table is like this:

CREATE TABLE [dbo].[table](
   [id1] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [id3] [int] NOT NULL,
   [name] [nvarchar](255) NOT NULL,
   [id4] [int] NOT NULL,
   [xmlData] [xml](CONTENT [dbo].[xml_schema]) NULL,
   [booleanData1] [bit] NOT NULL,
   [notes] [varchar](4096) NULL,
   [id5] [int] NULL,
   [booleanData2] [bit] NULL,
   [id6] [int] NULL,

   CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
   ([id1] ASC, [id2] ASC, [id3] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I am trying to execute this sql on this table:

ALTER TABLE [table] ALTER COLUMN [xmlData] XML

so that I can drop the xml schema and replace it with new one.

But I am getting this error:

  Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.

Can anyone tell me what is the problem here?


texts:
I have a table of names:

Table A

John
Jim
Jason

and a table of xml strings:

Table B

Example 1

    <Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle="">
  <ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" />
</Show>

Example 2

<vars>
  <var name="MatrixName">
    <value>="LockExitPairCompatability"</value>
  </var>
  <var name="Jason">
    <value>=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)</value>
  </var>
  <var name="MinNum">
    <value>=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)</value>
  </var>
  <var name="Where">
    <value>="Site='" + Root.Site + "' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)</value>
  </var>
  <var name="Where2">
    <value>=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)</value>
  </var>
  <var name="CompatibleList">
    <value>=usr.FetchMatrix</value>
  </var>
</vars>

Example 3

<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>

How can I search to find which names in Table A aren't in any of the xml strings?
The data shown are just examples. The xml in the database is of all different formats.

In the xml data it might be "Root.Name". I would still want it to find it even with the "Root." attached to it.


texts: How to drop an in-memory temporal columnstore

texts:
Consider the following:

CREATE DATABASE [Foo]
ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Bar_XTP') TO FILEGROUP XTP
GO
USE [Foo]
CREATE TABLE dbo.A(
    ID INT NOT NULL CONSTRAINT PK_A_ID PRIMARY KEY NONCLUSTERED,
    [Start] DATETIME2 GENERATED ALWAYS AS ROW START,
    [End] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME([Start], [End]),
    INDEX IX_A_CCS CLUSTERED COLUMNSTORE
) WITH (
    MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.A_History)
)

This wonderful contraption combines all the new features of SQL Server: an in-memory columnstore that's also a temporal table. Admittedly, the use cases for this should be limited: an in-memory columnstore typically supports real-time analytics and would not be expected to require versioning. Even so, if it's possible, someone will no doubt find some use for it.

There's just one slight thing I've got a problem with: I don't know how to get rid of it. A simple DROP TABLE produces:

  Msg 13552, Level 16, State 1, Line 27
  Drop table operation failed on table 'Foo.dbo.A' because it is not a supported operation on
  system-versioned temporal tables.

This is to be expected; you get the same error with disk-based tables. You're supposed to turn off system versioning first. Unfortunately ALTER TABLE A SET (SYSTEM_VERSIONING = OFF) produces:

  Msg 10794, Level 16, State 13, Line 1
  The operation 'ALTER TABLE' is not supported with memory optimized tables that have a column store index.

And that's also to be expected, but it seems to leave me out of options. Deleting the table from Management Studio produces the same sequence of commands under the covers, and so that also fails.

I've searched around but found neither a solution, nor someone who's attempted the same thing and confirmed it as a problem yet to be fixed. Workarounds readily suggest themselves (like renaming the table to something obscure) but is there a way to properly drop it?

(This was tested with the most recent non-Azure version of SQL Server as of writing, which is 14.0.3037.1, 2017 RTM CU10.)


texts: Moving Transaction Data To Another Database For Reporting Purpose

texts:
We got a requirement from our developer group, to do the followings:

Frequently (every 30mins) move the transaction data out of live database to another database
Secondary database will be utilize for Ad-Hoc query and Reporting
They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database

Our database server is on SQL Server 2012 Enterprise Edition.

This will make sure, live data are not been queried by the end users causing blocking issue. Developer will be working on getting the Analytic data in near future, but they wanted to implement something quickly to make live data as small as possible.

What are the recommendations to achieve this?

Thank you,

HP


texts: Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?

texts:
In the Optimizing Microsoft Office Access documentation, it mentions that we can:

  ...edit a Microsoft Windows registry setting that allows you to see
  the commands that the Office Access database engine is submitting to
  ODBC.

It goes on to give detailed instructions as to how to do this:

  To enable tracing of all ODBC commands from the Jet database engine:

  From the Windows Start menu, select Run.
  Type Regedit to open the Registry Editor.
  If you are using a version of Office Access prior to Office Access 2007, navigate to the following registry key, which
  appears as a folder in the Registry Editor.

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

  Office Access 2007 uses a customized version of the Jet database
  engine, named the Office Access Connectivity Engine (ACE), which is
  not shared with other Windows applications. If you are using Office
  Access 2007, navigate to the following registry key.

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity
  Engine\ODBC

  Double-click the TraceSQLMode setting, change the value from 0 to 1,
  and click OK.
  If Office Access is open when you make this change, you
  must close and reopen Office Access for the change to take effect.

  After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named Sqlout.txt.

However, as you can probably tell, this documentation is quite old (it was written about Access 2007 and was published in 2006). Because it is so old, the registry information seems to be out-of-date for newer products such as Office 2016.

Also, it would seem that Access 2016 doesn't use Jet Engine, but instead uses the Access Connectivity Engine.

As such, I'm having trouble finding a couple of things:

Where is the Access Connectivity Engine >>> TraceSQLMode registry key for Access 2016?
Additionally, where does the sqlout.txt file get saved on my computer?


texts: SQL Server 2012 Replication Subsystem Increase Number of Working Threads

texts:
I am trying to create Transaction Replication (Push) between two servers, using a distribution database on a3rd server. Server 1 is SQL Server 2008, Server 2 is SQL Server 2012. The Distribution Server is SQL Server 2012. I can create the Publication on Server 1 without a problem. When trying to create the Subscription from either Server 1 or Server 2, and then View Synchronization Status from Server 1, the Last status message is "The agent has never been run." Clicking Start results in the following error message:

The agent could not be started.
------------------------------
SQLServerAgent Error: Request to run job <REPLICATION JOB NAME> (from User distributor_admin) refused because the job already has a pending request from User sa. Changed database context to '<Database Name>'. (Microsoft SQL Server, Error: 22022)

The related Job on the Distribution Server is in fact in a running status. The problem is that the Job never completes, and the replication never succeeds.

The message from the error log I get on the Distribution server is:

Message
[398] The job (<Job Name>) has been queued because the maximum number of working threads (200) are already running. This job will be executed as soon as one of the working thread finishes execution.

Of note: we already have over 230 Replication jobs running on the Distribution server.

After some research, and seeing the error on the Distribution Server, It looks like bumping up the max_worker_threads for the replication subsytems might address the problem. However, after running the following UPDATE query (below) on the Distribution Server, then restarting SQL Agent, the max_worker_thread values revert back to the original 200 value (100 * # of CPUs).

select subsystem,
right(subsystem_dll,20) as 'Agent DLL',
right(agent_exe,20) as 'Agent Exe',
max_worker_threads
from msdb.dbo.syssubsystems

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 400
WHERE subsystem IN (N'Distribution',N'Merge',N'Snapshot',N'QueueReader')

So, not sure where to go from here. Looking for suggestions. The solution above does not seems to work with SQL Server 2012.


texts: How to prevent message »Pager won't be used for less than 500 lines.«

texts: I have \pset pager_min_lines 500 in my ~/.psqlrc. Now, each time I start psql, it shows the message »Pager won't be used for less than 500 lines.«. I don't need that message, I specificly asked for this behavior, I don't need psql to tell me. This get's even more annoying, when I run psql from scripts where this message spams the output. How can I get rid of that message without using -X/--no-psqlrc?


texts: Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?


texts: What does @ signify in a query expression?

texts:
What does the @ symbol signify in this sql expression?

update some_table set some_number=(@some_number+1) where some_id=1;

It seems to return the same result as:

update some_table set some_number=some_number+1 where some_id=1;


texts:
I've never had a database with this many rows, so I can new to keeping the search time to a minimum.

I have a database table with 1.5M rows.

The primary key is an auto-increment id.

Then there is a composite key comprised of 4 of the columns, each row must be unique in that the 4 columns can not be exactly the same for 2 rows.

When inserting, there is an ON DUPLICATE KEY UPDATE to ensure this.

Here is a picture of the indexes on my temp server: http://i.imgur.com/IG27z5V.png, which is the same as the production server.

The php search function takes the search term and does a: (Using Laravel)

$parts = Parts::where('part_number', 'LIKE', "%$part%")->get();

There is no other index to search by when searching through the entire thing.

The search page is also SSL, which I assume is making it slower.

What recommendations do you have to speed this up?


texts:
I have a static table containing filenames (StaticFilesTbl).

Each day a file is sent in that is stored in DailyFilesTbl.

The filenames in DailyFilesTbl will be different from StaticFilesTbl in that they will contain a date (in any format and in any location) in the filename.

I want to return files from DailyFilesTbl that 'match' - using LIKE - to the files in StaticFilesTbl.

In my code below I try to do this by putting a '%' into the StaticFilename value after an underscore - which works. However, I then have an issue if it is needed after multiple underscores.

Is there a better way to do this?

http://sqlfiddle.com/#!18/a0adfe/1

-- Table that holds static file names
CREATE TABLE StaticFilesTbl
(
    StaticFilename nvarchar(150)
)
INSERT INTO StaticFilesTbl(StaticFilename)
VALUES('SG__123456'),
        ('TFF_x2__x3');

-- update static table to add '%' for searches
UPDATE StaticFilesTbl
SET StaticFilename = STUFF(StaticFilename, CHARINDEX('_', StaticFilename)+1, 0, '%');

-- table that holds files received each day (date is on filename and changes daily)
CREATE TABLE DailyFilesTbl
(
    DailyFilename nvarchar(150)
)
INSERT INTO DailyFilesTbl(DailyFilename)
VALUES('SG_20180731_123456')
        ,('SG_20180730_123456')
        ,('SG_20180730_x2')
        ,('TFF_x2_20180730_x3');

-- query daily table for files
SELECT *
FROM DailyFilesTbl d
INNER JOIN
    StaticFilesTbl s
    ON d.DailyFilename LIKE s.StaticFilename +'%'


texts: Change expired user password on sql server from linux

texts:
I have got a colleague (software developer) working on Linux only. He connects to our SQL Server using a sql login with password. Occasionally the password expires and he has to choose a new one. Up to now he then heads over to a colleague with windows workstation and uses SSMS to change the password. I have just figured out that sqlcmd offers a parameter -z to change the password...however this parameter is not available within sqlcmd for linux. Azure Data Studio does not seem to support a password change either.

Is this it and you have to change the password using windows or is there any option from Linux which I haven't figured out?


texts:
I've not seen this before, though perhaps it is common.  I am looking at a virtualized (VMWare 5.5) SQL Server (2008R2 on Windows 2008 R2). What I am seeing is that in Task Manager*, sqlserver.exe is using about 163 MB of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

The VM has 32GB of RAM and Task Manager shows 31.7 GB of that RAM being used.  The VMWare Perfmon counter does not seem to be indicating any ballooning (perhaps I read something the wrong way).

Thoughts/guidance appreciated.  I am trying to right size a new SQL VM.  At this point I have not yet obtained access to vSphere or the vCenter database.

* Using Task Manager because I was looking at overall memory utilization, not just SQL Server. I was warned by a performance touchy admin to keep my treading very very light.


texts: SQL Server RDS Database Restore from S3 stuck in Restore Mode

texts:
Summary:
We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it's stuck in "Restore" Mode and we can't do anything with it.
Detail:
We have spun-up a new SQL Server RDS Instance and we're trying to restore a database from back-up stored in S3. Which is simple enough, we had to do it in the past before maybe a year ago or so. We run the command from the AWS Documentation
EXEC msdb.dbo.rds_restore_database
 @restore_db_name = 'OurDB'
, @s3_arn_to_restore_from = 'arn:aws:s3:::bucket/SQLBackUp.bak'
, @with_norecovery = 1

And we know that command won't work if the Options Group and IAM roles aren't set up, so we've made sure they are. When we run the command it takes a few minutes but appears to run fine.
We run the command to check on the status
EXEC msdb.dbo.rds_task_status
 @db_name='OurDB'

And what as % complete grows over the next few minutes to 100% and the lifecycle value says "SUCCESS". However when I try to access the database, I'm unable to and it says it's stuck restoring and the exact error message I get is "The database OurDB is not accessible. (ObjectExplorer)"
Whenever I search RESTORE DATABASE Stuck RDS SQL SERVER or variations of that, all the stuff I find is about doing a restore of the entire RDS Instance and not an individual database. Or if I'm able to find something about restoring a database that's stuck then it's not about RDS but about regular SQL Server.
Here are some screenshots of what I'm seeing too.


texts: My Database Tables Revert to MyISAM Instead of InnoDB (MySQL)

texts:
I am trying to change my tables engine to InnoDB because I am experiencing problems with the current engine which is inconsistent and crashes often. Unfortunately, it reverts back to MyISAM. This is the query that I am running:

SET @DATABASE_NAME = 'my_database'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;

What could I be missing or doing wrong?


texts: Convincing my DBA there's a sporadic issue with the SQL Server

texts:
How do I convince my DBA there's a sporadic issue with the SQL Server?

The issue: basically, major slow down... Some queries, generating execution plans, even generating scripts of existing DB objects can sometimes take forever. Even operations that should have no performance issue, like generating the create table script, can sometimes take forever.

The situation - I have a SQL Server database on which I can create tables / views / etc..

The DB is hosted on a SQL Server (MS SQL Server 2016) managed by the company's IT.

I've asked him to look into it, and I believe he's been earnest in checking logs and he says the load on the server at times I noticed massive performance issues showed nothing out of the ordinary. In fact, he said it was all under 5% (very low).

I have the exact same code and queries running in staging and development environments, it's fine there - it's even super speedy relative to the production environment.

I'm at a loss at what could be the issue to be honest. And I don't know where to start.


texts:
So I have the following insert statement that I run through MySQL Workbench:

INSERT INTO PeakOnlineCount
(time_stamp, LocationId, Count)
values
('2015-03-26', 0, 1)

If I run the following query:

SELECT * FROM PeakOnlineCount

straight after in the same tab, I can see the results. If I disconnect, reconnect and run the select query the table seems empty.

If I run the insert statement on one computer and run the select from another, the table is empty.

I'm not using transactions or anything like that. What's going on?

(And yes I know that I'm using SELECT *, this is only for the question)

Here's the structure of the table:

CREATE TABLE `PeakOnlineCount` (
    `time_stamp` date NOT NULL,
    `LocationId` int(11) NOT NULL,
    `Count` int(11) NOT NULL,
    KEY `IX_LocationDate` (`time_stamp`,`LocationId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I'm not using auto-commit and according to http://dev.mysql.com/doc/refman/5.1/en/commit.html:

  By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

I certainly haven't turned off auto-commit.

I have run SHOW TABLES and I've copied and pasted the table name from that. Also, I'm running all the queries in the same window so should be connected to the same schema in all queries.

There are no triggers on the table. I try to avoid triggers as much as possible.

Ran show variables, show session variables, and show global variables. All say that autocommit is turned on.

I am using workbench, but I have a colleague using something else (sequel pro on a mac) who runs the insert query and then I've run the select query and got nothing back.


texts:
I use OraOleDB (Oracle's OleDB implementation) in a C++ application on Windows to create tables and insert rows. In the application it is also possible to connect with Oracle using ODBC through OleDB.
The problem is that with OraOleDB clients from 18c onwards, the procedure must be retried a couple of times. There is an error message on insert: unspecified error. With OraOleDB 12c and earlier versions there is no problem. Also when connecting with ODBC, there is no problem. The Oracle server version doesn't matter: the issue can be reproduced on both Oracle Database 11g and Oracle Database 18c, on both Windows and Linux.
The documentation of the OraOleDB 18c provider doesn't seem to be updated, but there must be some changes since OraOleDB 12, I guess.
How can I know what has changed between the OraOleDB 12 and OraOleDB 18 (or 19) providers? Is there documentation that describes the differences between the latest versions of OraOleDB? Or is extra configuration needed for Oracle 18 and 19?
I unsuccessfully searched through the OTN for documentation that has changed since the latest 12c release. The html document installed in the client directories in versions 12 and 18 is also identical.


texts: Oracle program "lsnrctl" is missing after the installation of 32bit Oracle client

texts:
I cleaned up my computer for all old Oracle stuff using Uninstall (OUI) and then later used regedit to  delete all references to Oracle and then deleted all remains of Oracle directories.

I have HP ProBook with Windows 7 (64-bit) and I want to install a 32 bit Oracle client on it. I need to be  connected to an Oracle 11gR2 database on a remote server by OCI, ODBC and OLEDB.

I have done this installation tens of times before, but this time I am stuck.

The command lsnrctl isn't present after the installation, and as expected then, the lsnrctl.exe binary is not present under the Oracle home directory (in BIN) as it should be.

This test shows the frightening situation:

C:\Oracle\product\11.2.0\client_1\BIN>lsnrctl status
'lsnrctl' is not recognized as an internal or external command,
operable program or batch file.

I did the same operations and installation on more time, but with the same result.
Why does this happen and what can I do to straight it out, other than re-installing the whole Windows 7?


texts: Indexes Size 20 times bigger then the table itself and very slow queries

texts:
The table I want to query has only about ~3000 rows with 3 columns:

int/varchar(100)/varchar(100)

yet it takes ~30 sec to query them all!

Then I saw that the table size is 1331MB but the indexes size is 25GB!

The table has only one index and that is the pkey.

VACCUM FULL is running every 24hours and the table sould be pretty static anyway

My question is why are the indexes so big (I guess thats why the querys are so slow) and what can I do to fix that?

Hope you have any ideas, because I have none and I haven't found anything on google.

EDIT: Postgres Version is 8.1.18


texts: Slow query when adding additional where clause

texts:
Here's my setup (Postgres 9.3)

Posts

project_id

Messages

post_id
kind
updated_at

I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are a specific kind. My query looks like:

SELECT "messages".* FROM "messages"
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id"
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972' AND "messages"."kind" IN (10, 11, 12)
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..2372.06 rows=100 width=648) (actual time=17.731..308.603 rows=100 loops=1)
 ->  Nested Loop  (cost=0.17..44164.77 rows=1862 width=648) (actual time=17.730..308.559 rows=100 loops=1)
       ->  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20924.10 rows=151337 width=648) (actual time=0.034..93.817 rows=83481 loops=1)
             Filter: (kind = ANY ('{10,11,12}'::integer[]))
             Rows Removed by Filter: 130238
       ->  Index Scan using posts_pkey on posts  (cost=0.08..0.15 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=83481)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 308.660 ms

If I run without the kind clause:

SELECT "messages".* FROM "messages"
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id"
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972'
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..1320.02 rows=100 width=648) (actual time=0.501..23.613 rows=100 loops=1)
 ->  Nested Loop  (cost=0.17..69371.34 rows=5256 width=648) (actual time=0.500..23.583 rows=100 loops=1)
       ->  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20603.62 rows=427305 width=648) (actual time=0.010..3.256 rows=7893 loops=1)
       ->  Index Scan using posts_pkey on posts  (cost=0.08..0.11 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=7893)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 23.667 ms

I think the main slowdown is that the majority of the rows are not of the kind I'm looking for. I tried adding a partial index on kind, but that didn't have any effect.

CREATE INDEX index_messages_on_kind ON messages USING btree (kind) WHERE (kind = ANY (ARRAY[10, 11, 12]));

Any ideas to speed this query up?

Edit:

Indexes that currently exist:

Posts

index on project_id and updated_at

Messages

indexes on post_id, updated_at and partial index on kind IN (10, 11, 12)


texts:
As the title says, I want to create a query that searches a keyword across all columns in that table.

So I want to simplify this query:

SELECT * FROM tblCustomers
    WHERE (col1 LIKE '%keyword%' OR col2 LIKE '%keyword%' OR col3 LIKE '%keyword%' OR col4 LIKE '%keyword%')
    AND (col1 LIKE '%keyword2%' OR col2 LIKE '%keyword2%' OR col3 LIKE '%keyword2%' OR col4 LIKE '%keyword2%');

To something like:

SELECT * FROM tblCustomers
    WHERE (allColumns LIKE '%keyword%')
    AND (allColumns LIKE '%keyword2%');


texts:
I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by using any of the two.

My partition key is the orderId, so getByOrderId is not a problem. By getByOrderNumber is - there's a one-to-one mapping b/w the orderId and the orderNumber (high-cardinality field), so creating a local secondary index on each node would slow down my queries.

What I was wondering was that I could create a new table with the orderNumber as the partition key and the orderId as the only column (kind of a secondary index but maintained by me). So now, a getByOrderNumber query can be resolved in two calls.

Bear with me if the above solution is egregiously wrong, I am extremely new to Cassandra. As I understand, for such a column, if I used local secondary indices, Cassandra would have to query each node for a single order. So I thought why not create another table that stores the mapping.

What would I be missing on by managing this index myself? One thing I can see if for every write, I'll now have to update two tables. Anything else?


texts: Create Table with Int-attribute which can only use values between 6 and 36

texts:
Hello I'm learning for my databases examination and in one exercise they asking for creating a table with a attribute which can only use values between 6 and 36. How do I solve it?

We used in the course always Oracle databases and not mysql.

Here is the original text of the exercise (in german language):

Erstellen Sie eine Tabelle PROJEKTE. Projekte haben einen Namen, eine
Projektnummer, die Personalnummer eines Professors, der die Rolle des Projektleiters
übernimmt, einen Starttermin und eine Laufzeit (ganze Zahlen zwischen 6 und 36).


texts: Does setting column property to NOT NULL affect table size?

texts:
I have a bunch of columns in my MySQL database table that store booleans (0 or 1) as tinyints. They are 0 by default and are not meant to be null. I am thinking about setting the column property to NOT NULL so that there is another validation check just in case. I wonder though whether it will come at a cost of increased size of the table down the road. My fear is that, perhaps, NOT NULL property adds another bit to every data entry?

My database is new and practically empty so when I try to check the size of the table it always gives me 16kb.


texts:
I use Postgres and jsonB column

I have the following table:

id(VAARCHAR(64)) | value(JSONB)
--------------------------------
1                |[{"x": "10", "y": 1}, {"x": "32110", "y": 12}]

X has always unique

I can add a new element in this sequence.

I can check that sequence contains the element with x value "10" or not.

But I can't
**Remove element with the special value of x (remove value which contains x:"10")?

Find the maximum value of y?**

Can anybody help me with this?


texts: Search for rows with coordinates within a Polyline

texts:
I'm stuck with the logic of a MySQL query where I need to find if a row exists where:

The column longitude and latitude are within a Polyline
The column hit is duplicated within the last month

I've been able to achieve the second point using :

select   latitude,
         longitude,
         count(*)
from     hits
WHERE datetime BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE()
group by latitude,
         longitude
having   count(*) > 1

but I can't figure out a way to only search for rows where the latitude and longitude are within a Polyline. Performance is a main factor here as the table hits can have millions of records.(can also do with a version 1 not so scalable/performant)

Any help would be much appreciated!

EDIT: I'm using MySQL 5.7 and I also have a column coordinates of type point (spatial type) also Polyline means :

  A polyline is a list of points, where line segments are drawn between consecutive points

EDIT 2: Here is the query for people using php/symfony

$query = $this->em->createQuery('
            SELECT   count(i) as counter
            FROM     HitsBundle:Hit i
            WHERE i.datetime BETWEEN :lastMonth AND :now
                  AND
                  MBRCovers(
                    ST_Buffer(
                        ST_GeomFromText(\'LineString('.$linestring.')\') ,
                        0.00001
                    ),
                    i.coordinates
                  ) = 1
            GROUP BY i.coordinates
            HAVING  counter > 1
        ')
            ->setParameter('lastMonth', $lastMonth)
            ->setParameter('now', new \DateTime())
            ->setMaxResults(1);


texts:
Is there a chance to recover overwritten schema or table in a schema?
I have accidentally overwritten schema with the back-up from other source.


texts: How to model schedule and keep good constraints?

texts:
I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will be scheduled for the same store two days in a row, but I have to ensure that they are scheduled for no more than 2 days in a given period (or "cycle").

A spreadsheet representation of the data would look like this.

Cycle Team    Store   Date
1     1       1   1-Dec
1     1       1   2-Dec
1     1       2   3-Dec
1     1       2   4-Dec
1     1       3   8-Dec
1     1       3   9-Dec
1     1       4   10-Dec
1     1       4   11-Dec
1     2       10  1-Dec
1     2       10  2-Dec
1     2       11  3-Dec
1     2       11  4-Dec
1     2       12  8-Dec
1     2       12  9-Dec
1     2       13  10-Dec
1     2       13  11-Dec

I already have a Team/Store cross reference that works pretty well, but getting down to this level of granularity is new. It would be easy if I could just put a composite primary key on Team/Store/Date, but I obviously can't do that, or I would only be able to store one of the two scheduled days.

I've considered using a date table with a one to many relationship from Team/Store to date, but that doesn't seem to fix the uniqueness issue and adds what feel like silly overhead.

Does anyone have any advice on how to model this?

The tag doesn't seem appropriate, as this is really an abstract design question that I'm asking, but I am using SQL Server 2008.


texts: Entity Relationship Diagram - Final Physical Data model Review

texts:
I am new to this programming world and I am learning drawing ER diagram for a small airport management suite and use the below physical data model in my project report before that, I want to verify If the the following are correct or not.
(i) Relationships
(ii) Primary Keys and Foreign Keys
(iii) In Actual_flights_ there is a aircraft_id which must be FK but it was not mentioned as FK against it in the diagram, same with pilot_id.
(iv) In table Hangers, Airport_ID is termed as FK but there is no relation between Hangers and Airports_ table.

Hence, I would like to verify before using the same ER. For your reference. I am currently developing database in access. Please help me!!

Entity Diagram


texts: How to avoid Hash Match by ordering the underlying view or subquery in SQL Server

texts:
Suppose a is a table with 800 rows and a primary key on Id and bView is a view that queries several tables and ends up with a total of 8000 rows, and I want to do the following type of query without a WHERE clause:

SELECT a.*, bView.*
FROM a
JOIN bView ON a.Id = bView.aid
ORDER BY a.OtherColumn

Now, this join will do a Hash Match since bView is unordered, which is frustrating because we could easily sort the results of bView and do a merge or loop join instead and save perhaps half the query cost.  Isn't there a way to make SQL Server ORDER the results of bView to enable a wiser join?

(I can change bView if necessary.)


texts: How to get Size of db tables one month back

texts:
Recently there has been a drop in our db size and we want to compare the size of the tables back then with that of now. For this purpose we need the exact size of each table one month back.
We can extract license_size for an old date, but how to extract size of each table say a month ago.


texts: full outer join where join key in result always has value

texts:
I wish to do a full outer join between two tables A, B joining on A.ID = B.ID.
Now, it is quite easy to include A.ID and B.ID in a result set. this will include rows where:

A.ID is null, B.ID is not null
B.ID is null, A.ID is not null
A.ID and B.ID have the same value.

I want to have a third column which be equal to:

A.ID and B.ID when they are the same value
A.ID when A.ID has a value and B.ID is null
B.ID when B.ID has a value and A.ID is null

How do I do this?


texts: Index seek much slower with OR condition compared with separate SELECTs

texts:
Based on these questions and the answers given:

SQL 2008 Server - performance loss possibly connected with a very large table

Large table with historical data allocates too much of SQL Server 2008 Std. memory - performance loss for other databases

I have a table in a database SupervisionP defined like this:

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

It contains cca 211 milion rows.

I run following statement:

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24
SELECT min(cas) from PenData p WHERE IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

The result is shown here:

The third SELECT also loads much more data into SQL Server memory cache.

Why is the third SELECT so much slower (8.5 s) then the first two SELECTs(16 ms)?
How can I improve performance of the third select with OR? I want to run following SQL command but it seems to me that creating cursor and running separate queries is much faster than a single select in this case.

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM  ...)

EDIT

As David suggested I have hovered over the fat arrow:


texts: PostgreSQL array column, querying and grouping by array field

texts:
I have the following  table data in a table named queryables in Postgres 9.4:

 queryable_id | liked_count | users_who_like_ids
--------------+-------------+--------------------
         2376 |           3 | {1,80,78,101, 188}
        18771 |           1 | {78,101, 123,125}
         1790 |           1 | {78}
         2257 |           1 | {78}

and I want to select for users who like 1,78,80

The user_who_like_ids column was defined as a text array:

 users_who_like_ids      | text[]                      | default '{}'::text[]

but I has been updated to an integer.

users_who_like_ids      | integer[]                   | default '{}'::integer[]

I perform my query as this:

SELECT * FROM  queryables WHERE users_who_like_ids  @> ARRAY['1','78','80'];

but this requires users_who_like_ids to match ALL of them. I'd like it to be ANY of them, and then group by the length.

How would I do this?


texts: SQL Server storing predefined value sets along with open values

texts:
I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values must be localized. I designed this schema:

Though it seems like all requirements are met, I am a bit confused about having characteristic value in two different tables. I think it can cause some issues when trying to search by characteristic value.

Can you offer more elegant way to solve this problem?


texts:
In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.

Also, can you guys suggest some better tools for analyzing the query execution time?

Indexes are present on id's column in both the tables. The rows scan on both the tables is the same for these 2 queries.

SELECT DISTINCT sng_id FROM table1 WHERE id in (SELECT DISTINCT id from table2 WHERE LOWER(comment) LIKE '%home%')

SELECT DISTINCT sng_id FROM table1 AS a JOIN table2 AS b ON a.id = b.id WHERE LOWER(b.comment) LIKE '%home%'


texts: how to run once a bunch of generated scripts with exception handling?

texts:
Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.

So I created a script that calls a procedure that will save the contents of this table in a DBA database, for all databases where this table exists.

That is working fine, but I would like to automate this process, avoiding the copy and paste - see below.

How it works:
When I run the following script I get a bunch of scripts to be executed:

--============================================================================
-- to save the table dbo.ref_systemSettings in all the databases where it exists
-- just run the generated script below at Radhe
--============================================================================
SELECT name, Radhe='exec sp_save ' + ''''
     + name + '.dbo.ref_systemSettings' + ''''
FROM sys.databases
WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

the query above produce the below:

which I copy and paste, in order to run:

exec sp_save 'my_db_01.dbo.ref_systemSettings'
exec sp_save 'my_db_02.dbo.ref_systemSettings'
exec sp_save 'my_db_03.dbo.ref_systemSettings'
exec sp_save 'my_db_04.dbo.ref_systemSettings'

when I run the whole of the query above, all my tables are saved into the DBA database. This is currently working fine.

Question:

I would like to eliminate the copy and paste.
what could be an effective way to run these scripts, while taking into consideration exception handling, lets say... there is no more space in the DBA database and the table cannot be saved... or any other expection??


texts: sql server replication - how to get combination of values from a binary(8) field?

texts:
I am using the table sysschemaarticles to collect information in my transactional replication.

It is used for a variety of things including adding new articles and finding out who are the subscriber Servers.

this script needs to be run in the publication database.

SELECT
 PublisherDB=db_name()
,PublisherName=sp.name
,The_Type=so.type_desc
,The_Schema=schema_name(so.schema_id)
,The_Object=sa.name
,SubscriberServerName=UPPER(srv.srvname)
,so.is_schema_published
,so.is_published
,sa.schema_option
from dbo.syspublications sp
INNER JOIN dbo.sysschemaarticles sa on sp.pubid = sa.pubid
INNER JOIN sys.objects so on sa.objid = so.object_id
INNER JOIN dbo.syssubscriptions s on sa.artid = s.artid
INNER JOIN master.dbo.sysservers srv on s.srvid = srv.srvid

However the schema_option has different options that can be associated with it, and I would like to figure out ALL of them.

You can see the possible options on the picture below.

How can I get ALL the values stored in a binary(8) field?

I believe there could be a combination of options in this case.


texts: Oracle : Is there a way to grant the ability to run a job to a role?

texts:
We have several dbms_scheduler jobs in an 11.2 database that we would like users with a certain role to be able to run.  As such, we don't want to grant the ability to run directly to the users, as it will be more difficult to maintain.

Based on Oracle's documentation, granting ALTER JOB to the user will allow them to run the job.  Unfortunately, it doesn't appear this extends to roles: I can run the GRANT statement just fine, and the job shows up in ALL_SCHEDULER_JOBS for the user, but attempting to run the job results in the following error:

ORA-27476: "S1.J1" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

Given that DBMS_SCHEDULER is AUTHID USER, but DBMS_ISCHED is AUTHID DEFINER, am I just out of luck?  Or is there a way I can grant it to the role and not the user?


texts: SQL Server 2014 "syspolicy_purge_history" job keeps failing

texts:
I am using SQL Server 2014 and the job "syspolicy_purge_history" keeps failing at step 3 "Erase Phantom System Health Records" and its driving me crazy as I don't know what is causing the failure.

here is the command:

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

The error message I got was:

  Executed as user: jonbrown. A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\xyz\T$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)  '.  Process Exit Code -1.  The step failed.


texts:
Today, I was hit by the below error on my SQL Server DB having multiple files in multiple filegroups.

  Event ID: 1105. Could not allocate space for object  in database
  because the 'PRIMARY' filegroup is full. Create disk space by deleting
  unneeded files, dropping objects in the filegroup, adding additional
  files to the filegroup, or setting autogrowth on for existing files in
  the filegroup.

  MSSQL 2014: Could not allocate space for object in database because
  the Filegroup is full

It occurred when my index maintenance job was running. Specifically during index reorg of one of the index. Upon investigation, I found that all my filegroups had at least one file with auto-growth enabled and all the auto-growth enabled files residing on respective drives had enough free space available. So there was no question of space issue or file autogrowth(considering I have at least one file in each filegroup with auto-growth enabled).Upon further drill down, found below from one of the MS link https://msdn.microsoft.com/en-us/library/aa337441.aspx

  When an index is located on several files, ALTER INDEX REORGANIZE can
  return error 1105 when one of the files is full. The reorganization
  process is blocked when the process tries to move rows to the full
  file. To work around this limitation perform an ALTER INDEX REBUILD
  instead of ALTER INDEX REORGANIZE or increase the file growth limit of
  any files that are full.

So in order to fix my issue I went ahead and rebuild my index which fixed the issue. However, I am looking out for ways to avoid it as it may occur for different index in future and doing manual index rebuild all the time isn't a good way to fix it? I would love to hear your suggestions on this.


texts:
I would like a user abc to access only a single keyspace and no other keyspaces in the Database.
How can I achieve so?


texts: MYSQL -- Inventory View with calculated fields

texts:
Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an error on the third line " ((COALESCE(items_received.Qty_Received, 0) - COALESCE(items_sold.Qty_Sold, 0))) AS QtyOnHand" The error return is 1054 Unknown Column 'QtyOnHandinfield list`. I assume I might need to do an additional SELECT statement but not sure how to put it in. Any help is appreciated.

SELECT
    `products`.`cat_num` AS `Product`,
    ((COALESCE(`items_received`.`Qty_Received`, 0) - COALESCE(`items_sold`.`Qty_Sold`, 0))) AS `QtyOnHand`,
    COALESCE(`item_demand`.`Item_Demand`, 0) AS `Demand`,
    COALESCE(`items_allocated`.`Qty_Allocated`, 0) AS `Allocated`,
    COALESCE(`items_backordered`.`Qty_Backordered`, 0) AS `Backordered`,
    If(`QtyOnHand` - `Allocated` - `Backordered` < 0,
        0,
        `QtyOnHand` - `Allocated` - `Backordered`) AS `Available`,
    COALESCE(`products`.`min_production_qty`, 0) AS `InMultiples`,
    COALESCE(`products`.`target_inv_level`, 0) AS `TargetLevel`,
    If(`Available` < `Demand`,
        ABS(`Available` - `Demand`),
        0) AS `Shortage`,
    If(`Available` < `TargetLevel`,
        If((`Shortage` + (`TargetLevel` - `Available`)) <= `InMultiples`,
            `InMultiples`,
            ABS(CEIL(-(`Shortage` + (`TargetLevel` - `Available`)) / `InMultiples`))),
        0) AS `QuantityToPack`
FROM
    `items_allocated`
        RIGHT JOIN
    (`items_sold`
    RIGHT JOIN (((`items_received`
    RIGHT JOIN (`items_onHold`
    RIGHT JOIN (`products`
    LEFT JOIN `items_backordered` ON `products`.`cat_num` = `items_backordered`.`cat_num`) ON `items_backordered`.`cat_num` = `products`.`cat_num`) ON `items_received`.`cat_num` = `products`.`cat_num`)
    LEFT JOIN `items_unallocated` ON `products`.`cat_num` = `items_unallocated`.`cat_num`)
    LEFT JOIN `item_demand` ON `products`.`cat_num` = `items_demand`.`cat_num`) ON `items_sold`.`cat_num` = `products`.`cat_num`) ON `items_allocated`.`cat_num` = `products`.`cat_num`
GROUP BY `products`.`cat_num`
ORDER BY `products`.`cat_num`;


texts: MySQL2::Error: BLOB/TEXT column can't have a default value

texts:
I am trying to run a rails mysql migration on Amazon Elastic Beanstalk.

It fails with this error message:

  Mysql2::Error: BLOB/TEXT column 'description' can't have a default value: ALTER TABLE items ADD description text DEFAULT ''

I found out from this thread that Amazon RDS might be in strict mode. How do I change the mode?


texts: sudo systemctl restart mssql-server hangs

texts:
When installing Red Hat Enterprise Linux Server release 7.6 (VM) minimal install, I followed these steps for MS SQL installation:

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo
sudo yum install -y mssql-server
sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
sudo ACCEPT_EULA=Y yum install msodbcsql17
sudo ACCEPT_EULA=Y yum install mssql-tools
sudo /opt/mssql/bin/mssql-conf setup

Until this stage everything works as expected. Problems start when I issue following command:

sudo systemctl restart mssql-server

Nothing happens. It remains in zombie state forever until I issue Ctrl + C to get the cursor back. Then I find the process id using ps -ef | grep mssql and kill it. Then start it again using sudo systemctl start mssql-server

Another issue that I am facing is when I try to connect using SSMS from my windows machine using SQL Authentication, it seems like it gets connected but nothing shows in Object Explorer. firewall rule is added to allow incoming traffic on 1433/1434 ports. SQLCMD from windows client works perfectly fine and show results of queries. SSMS however goes into a hang state.

Logfile has following entries

2019-07-21 12:50:17.09 spid6s Always On: The availability replica manager is going offline because SQL Server is shutting down. This is an informational message only. No user action is required.
2019-07-21 12:50:17.09 spid6s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2019-07-21 12:50:17.20 spid23s Service Broker manager has shut down.
2019-07-21 12:50:17.21 spid6s .NET Framework runtime has been stopped.
2019-07-21 12:54:18.03 Logon Error: 18451, Severity: 14, State: 1.
2019-07-21 12:54:18.03 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Only administrators may connect at this time. [CLIENT: 127.0.0.1]
2019-07-21 12:54:19.14 Logon Error: 18451, Severity: 14, State: 1.
2019-07-21 13:01:34.29 spid22s Error: 39002, Severity: 16, State: 1.
2019-07-21 13:01:34.29 spid22s SQL failed to boot extensibility for error code 0x80070005.

Any idea on how to resolve these 2 issues?
Thanks in advance


texts: Scrapy data analyzed with R, mongoDB or other pipeline?

texts:
I'm scraping data using the Scrapy library in python and i'm planning to make few analysis in the future using R.

In the future it could be a very big database with millions of items, what are the difference between using mongoDB or other databases? I've read the difference between storing in a SQL or NoSQL way but i can't decide which is more easy to process later in R.


texts: SQL query for combinations without repetition

texts:
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.

Extended sample input and result so input has 3 values instead of 2 - however, number of input values may vary from 1 to n.

Example:
Input: table with values in one column in multiple rows

Value  (nvarchar(500))
------
Ann
John
Mark

Output#1: table with values concatenated in one column

    Ann
    John
    Mark
    Ann,John
    John,Mark
    Ann,Mark
    Ann,John,Mark


texts: Will a Transactional Replication break the LSN chain in my backups

texts:
I need to create a fairly up to date read only copy of parts of a production database for reporting purposes.

I did look at log shipping to keep the copy up to date but this will break the LSN and render my backups unrestorable (Log backup every 15 minutes).

I reasonable confident that a Transactional Replication will fit the bill working from the Distribution Database and leaving the source unaffected but I can't find a definitive confirmation of this.

Can anybody put my mind at rest (or suggest something better!)


texts: how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

texts:
I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up.

Is it possible to:

create a stored procedure, with only input the A id
the sp runs the 7 queries, at each step concatenating the result to a single string. Rows/Columns should be separated in some way to allow automatic parsing later (not sure what is the best practice here)
the sp returns the single string, and in my code I only do a round-trip to Oracle

Is this possible? Is my gut right that this would be faster? Any pointers?

thanks


texts: SQL server Import CSV file - 1'st row is also added as s column name

texts:
I'm trying to import a CSV file using import export wizard, Im facing an issue.
The first row is also added as a column name.

Import wizard:

The highlighted area is my 1'st row.


texts: Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

texts:
I have the following tables and content

create table t(i int primary key, j int, k char(6000))
create  index ix on t(j)

insert into t values(1,1,1)
insert into t values(2,1,1)
insert into t values(3,1,1)
insert into t values(4,1,1)
insert into t values(5,1,1)
insert into t values(6,1,1)
insert into t values(7,1,1)
insert into t values(8,2,2)
insert into t values(9,2,2)

select * from t where j = 1
select * from t where j = 2

I'm really confused on why the first SELECT uses only a Clustered Index Scan (Clustered) while the second one uses a Index Seek (NonClustered) and a Key Lookup (Clustered).

Can someone clear this up for me?


texts: Can we change the default owner of mongodb to other than 'mongod' user?

texts:
mongod user is created on the system during installation and all of the appropriate files are created and owned by the mongod user.

Can we change the default owner of mongodb to other than 'mongod' user ?

Where to change ?

One of the Government Client wants to change as to tighten security.

Please share your ideas.


texts: Does mysqldump export indices, by default?

texts:
I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:

--disable-keys, -K

which suggests, that it actually does export the indices. But I don't want to trust my interpretation and I want to make sure I got it right (or wrong ;-)). Can anyone confirm that?


texts: I need to import into mysql data from report exported by infobip

texts:
I want to import this csv sample data:

��SendDateTime;ExternalMessageId;SMSCount;Account Name;Country;Country Prefix;Network;Destination Address;Sender;Price Per Message;Credits Per Message;Parent AccountId;Parent Credits Per Message;Client Metadata;Status;Reason;MessageText;DR Arrival Time
21.2.2016. 0:01:17;="126022022011749133";1;some-account;some coutry;27;somenetwork;="27123456789";="27123456789";90,0000;90,0000;;;="";Delivered;;Confirmation: some sms text value;21.2.2016. 0:01:19;

This is an example from a report exported by http://www.infobip.com/.
Why infobip calls this a csv I don't know.

I am having problems with the following 90,0000 currency values.

How can I convert this to a FLOAD / DECIMAL as part of the import.


texts: SQL Server-MySQL master-master replication

texts:
We are looking into setting up a master-master replicated MSSQL database and I am interested to hear of any potential pitfalls that we may run into along the way and generally any advice that you might have for us.

To expand on our situation a bit we currently have an internal SQL 2008 R2 server and a web based MySQL server. We use SSIS a lot to transfer the data between the two servers. This ends up with the data on the two servers being out of sync as the SSIS packages run hourly or nightly. We have investigated MSSQL->MySQL replication but we've never found a comprehensive solution.

This has brought us to what we are hoping to set up with the web using both the current MySQL and an additional replicated MSSQL database.

Specific areas that I'm interested in finding out more on are:

How does a master-master set up deal with an internet connection
being dropped? I've read that one node becomes read-only but I can't
seem to find much detail about this.
Is there anything to be wary of connecting from PHP to an MSSQL server?
What type of replication would you recommend? Bi-directional transactional seems to fit the bill quite well.

Thanks for your time


texts: Mysql performance on varchar with ranges

texts:
I will try to describe my problem as simple as I can.

I have a db with tables that contains columns with phone numbers ranges, e.g. numbertype_id = 7, number_start = '00110000', number_end = '00119999', numbers = 10000 (of course I have an id pk and unique key index as well).

On the site that uses those tables, I have a search functionality, which should find a number within those ranges (if there is any). For example, user searches for '00112233', and it should return true, since there is a hit.

Now the problem is performance. I am searching only in one table per search, with a max of under 200k rows.

Being varchar columns (and need to be, since with int I would loose the leading 0's, plus the leading zeros could be none, up to 6-7), for me the obvious choice was a search like this:

SELECT *
FROM numberdata
WHERE numbertype_id = 7
  AND number_start <= '00112233'
  AND number_end >= '00112233'

Depending on the table, that could take anywhere between 1.5 to 2.5 seconds, and it's not normal to take that long.

Does someone knows a better solution for that?

IMPORTANT UPDATE

I did not explained one vital detail. No matter that the number searched for returns any result or now, I still have to "offer" 45 alternatives, and have to search for those 45 numbers in part as well. So the total queries are 46 actually.

SHOW CREATE TABLE returns this

CREATE TABLE 'numberdata' (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier',
'numbertype_id' int(10) unsigned NOT NULL DEFAULT '0',
'prefix' varchar(5) NOT NULL DEFAULT '',
'number_start' varchar(20) NOT NULL DEFAULT '',
'number_end' varchar(20) NOT NULL DEFAULT '',
'number_count' int(10) unsigned NOT NULL DEFAULT '0',
'owner' varchar(100) DEFAULT NULL,
'owner_city' varchar(100) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY number ('numbertype_id','number_start','number_end')
) ENGINE=InnoDB AUTO_INCREMENT=48690 DEFAULT CHARSET=utf8

And EXPLAIN SELECT... returns this

id select_type table      type possible_keys key    key_len ref   rows  Extra
1  SIMPLE      numberdata ref  number        number 4       const 17174 Using index condition

Is it possible for the query to return 2 or more rows? Or will it always return 1 (or 0) rows?

For any number I return only one result, or none (in case it's not present in any of the ranges). No possibilities for duplicates.
Can we assume that all values in number_start and number_end are left-padded with 0s to the same length?

Yes, that's correct.


texts: Sybase Database Migration from 11.5 to 15

texts:
I have to migrate the Sybase Database from 11.5 version to 15.
I would like to know few things mentioned below -
i) What are the neccessary steps I need to follow in this migration.
ii) What are the changes required for table, Stored Proc, etc., for this change to work as per the current execution?
iii) Any bottle necks if exist for this transiton

Note :- We have a Unix server that executes these procs with the help of Perl Script?

Thanks
Utkarsh


texts: SQL Server Management Studio with Multi subnet HADR cluster

texts:
We are starting to deploy Multi-Subnet HADR AlwaysOn clusters for our environment. Using MultiSubnetFailover=True in application connection strings works.

However, SSMS connections require manual paste of this connection parameter into the additional connection parameters window for every connection.

Is there a workaround to automatically add this for all connections?

Alternately, using a Central Management Server to store connections for multiple SQL Servers, is there a way to store this here?

Thanks.
Jeremy


texts: Updating statistics, what about existing execution plans

texts: If you update the statistics on tables that have never been updated (don't ask why they never have), does SQL Server automatically refresh the execution plans based on the outdated statistics? If not, what is best approach to do so?


texts: How can I speed up a SELECT statement related with a VIEW in MySQL?

texts:
I have the query shown bellow:

    SELECT i2016.rid,
           NOW(),
           NOW(),
           1,
           1,
           1,
           0,
           'changeme',
           '',
           '',
           '',
           '',
           i2014.total,
           i2015.total,
           i2016.total,
           '',
           i2016.categoryid,
           i2016.accid
      FROM invreport i2016
 LEFT JOIN invreport i2015
        ON i2016.rid = i2015.rid
 LEFT JOIN invreport i2014
        ON i2016.rid = i2014.rid
     WHERE i2016.period = '2016'
       AND i2015.period = '2015'
       AND i2014.period = '2014'
  GROUP BY i2016.`rid`

It takes over an hour for 1000 rows if not longer. I need help to speed it up as I have over million rows stored in the line table.

It runs from the following VIEW:

 SELECT YEAR(`invoice_lines`.`date_entered`) AS `period`,
        SUM(`invoice_lines`.`quantity`)      AS `total`,
        `invoice_lines`.`pasteldb2`          AS `pasteldb2`,
        `invoice_lines`.`categoryid`         AS `categoryid`,
        `invoice_lines`.`accid`              AS `accid`,
        CONCAT(`invoice_lines`.`pasteldb2`,
               `invoice_lines`.`accid`,
               `invoice_lines`.`categoryid`) AS `rid`
    FROM `invoice_lines`
   WHERE (`invoice_lines`.`id` LIKE 'nem%')
GROUP BY `invoice_lines`.`pasteldb2`,
         YEAR(`invoice_lines`.`date_entered`),
         `invoice_lines`.`accid`,
         `invoice_lines`.`categoryid`


texts: What is a good way to store a large number of columns?

texts:
I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add.

I have data coming in formatted like so, but rather than 4, the number of columns is approx 240, so each date has 240 unique values associated with it:

Date/Time 200,00 202,50 205,00
2010.11.12  13:34:00  45,8214 43,8512  41,5369
2010.11.12  13:35:00  461,9364  454,2612  435,5222

Also, rows are associated with DataSites.

My first thought was to have a table like so:
DataID (pk), DataSiteID, ParameterID, Date, Value, with an index on DataSite, Parameter and Date. The ParameterID refers to another table that stores the input column headers (200,00 202,50 205,00 ...).

My second thought was simply to have a table with all 240-odd columns. I have come up with a few other ways, but they are pretty unsatisfactory as well.

The problem I have with my first solution (not such a huge problem, but I don't like it), is that the Date and DataSiteID are going to be repeated for all 240 values in that input row, so it uses quite a bit of extra space.

There will be about 40gb of data a year coming in (in the above text format), and the data will be searched by DataSite, Parameter and Date. The amount of data coming in will most likely quadruple in a year or so.

Any good ideas?
Thanks, James

edit: This is time series data, with the columns being measurements at different wavelengths. Data will want to be analysed within a relatively narrow range of wavelengths. There could also be extra wavelengths added in at some point in the future.

edit: Thanks for the answers guys, I really appreciate it :) I think I can probably find time to run some experiments with 500gb or so of test data. I'll post back with any conclusions ;)


texts: Postgres on Heroku: Is it possible to prevent users from logging in to the primary host?

texts:
I have a Postgres 12.3 server running in a primary-follower configuration on Heroku. I added a few credentials to the primary instance, which were replicated to the follower. I would like to prevent users from logging into the primary host.
I understand this is generally possible with pg_hba.conf, but I do not believe I have access to this file on Heroku.
Is it possible to achieve this on Heroku?


texts: Is there a way to optimize the process of uninstalling and installing SSMS?

texts:
in terms of patching sql server 2016 to the latest cumulative patches, I found out that stopping the services helps avoiding lots of inconveniences, specially if there is log shipping and replication involved:

However,
when upgrading to the newest SSMS, stopping the services does not seem to make the uninstall of the old version go quicker, it is indeed a very slow process.

same slow is the installation.

even having the relevant services stopped for this operation:

Plus a reboot at the end of the process.
Is there anything that can be done, in order to automate or optimise the process of installing the new SSMS (currently 18.5.1)?


texts:
I have been reading up on Data Warehousing and its purpose. The main purpose seems to be to track historical data and whatever changes may have occurred over time.

The DW book I am reading gives a number of reasons for using a DW schema...

1) Normalized schema may have 1000's of tables

2) Naming conventions may not be enforced

3) Data may be in multiple databases

4) Data quality issues

5) Historical data requirements

I am now working on a project where it has been suggested to me to use DW schema. However, none of the above 5 reasons apply to my situation.

1) Data is in region of 10 tables

2) Columns are named correctly
3) All data is in a single database

4) No reported Data Quality issues

5) No requirements for historical data

So, is tracking historical data the main reason to use DW?


texts: msdb.dbo.sysjobs and last run date and time

texts:
I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same time as what the GUI log history displays.

Can anyone provide guidance on this?

Here is the t-sql code I have that converts the value to a datetime value:

'last run literal' = dateadd(millisecond,
    sjs.last_run_time,
    convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10))))


texts: Converting "string" dates with errors to normal dates or NULL

texts:
I have an old table containing dates stored as strings like '20191001'.
I have created a new field of type DATE and I try to recover the old dates in the new field :

UPDATE extcg SET newdate=to_date(olddate,'yyyymmdd');

The problem is that some of these dates are invalid (20190231). I would like to skip the conversion for these dates and leave the new field NULL.

Is there a way to exclude these in a WHERE statement ?


texts: Preferred approach to implement multiple many-to-many relationships?

texts:
I am building a database that includes the following tables:

pages
users

A particular user can access (or is associated with) a page with one of the following roles (permissions):

Owner
Administrator
Editor

What will be the best approach in this kind of situation? Either to setup join tables like this:

owners (page_id, user_id)
administrators (page_id, user_id)
editors (page_id, user_id)

Or using a single join table:

pages_users (page_id, user_id, role)

Also, if the second approach is preferable, should role attribute be a string (such as 'owner', 'administrator', or 'editor') or something like role_id and the roles defined in a separate table as follows:

roles (id, name)

If there's a completely different approach which fits the need in a more appropriate manner, please let me know.

Also, is there any special name for this type of relationship(s) in database jargon? I am unable to craft the question's title right, edits are welcome.

Thank you for your answers


texts:
I have a table of "admins" and what they control "units" each unit and admin are from a different table. I wanted to check if there is a connection between an admin and a unit. The number of levels down is unknown. This is my attempt so far. May I get some help?

DROP FUNCTION IF EXISTS `isadmin`;

DELIMITER //

CREATE FUNCTION isadmin(a_id INT(11), a_table VARCHAR(25), u_id INT(11), u_table VARCHAR(25))
    RETURNS INT DETERMINISTIC
BEGIN
    DECLARE rtn INT(1);

SET @a_id := a_id;
SET @a_table := a_table;
SET `rtn` := 0;

WHILE @a_id != '' AND @a_table != '' DO
        SELECT `unit_id`,`unit_table`
    INTO @u_id, @u_table FROM `admin`
    WHERE (`admin_id`,`admin_table`) IN (@a_id, @a_table);
        IF @u_id = `u_id` AND @u_table = `u_table` THEN
                SET `rtn` := 1;
            SET @a := '';
        ELSE
                SET `rtn` := 0;
            SET @a_id := @u_id;
            SET @a_table := @u_table;
        END IF;
END WHILE;

RETURN `rtn`;

END//
DELIMITER ;

SELECT isadmin("1","user","2","group");


texts: PostgreSQL 8.4 replication of more than 100 tables

texts:
I am facing a problem with PostgreSQL version 8.4.

I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to replicate with some details and sequences separately..... Now imagine a database with more than 100 tables, that is my case, what should I do?

If I upgrade PostgreSQL to version 9X the database links with Oracle stop working, so I need to stay with 8.4 but I can't find a tutorial to replicate the entire database like I do in the mysql instances.

I will be more specific in the end of this tutorial learning how to use slony suggests:

set add table (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.tablename', comment = 'table');
set add sequence (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.sequencename', comment = 'seq');

(Link for the full tutorial: https://www.howtoforge.com/configuring-slony-i-cascading-replication-on-postgresql-8.3)

For each table i want, and don't say nothing about replicating the entire database, another tutorials says the same.

Please somebody know a good way to solve that? Maybe I will need some Shell Script?


texts: Select by grouped rows and order by date


texts: Get ip postgresql master from recovery.conf using non-superuser

texts:
I try on PostgreSQL 9.3 Slave to get ip postgresql master from recovery.conf using non-superuser.

Created FUNCTION f_showfile

CREATE FUNCTION f_showfile(myfile text) RETURNS text AS $x$
BEGIN
RETURN pg_read_file(myfile, 0, 1000000);
END;
$x$ LANGUAGE PLPGSQL SECURITY DEFINER;

Added grant execution to f_showfile for non-superuser

GRANT EXECUTE ON FUNCTION public.f_showfile (text) TO the_user;

Get lines with primary_conninfo string from recovery.conf

SELECT *
FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo';

Output:

-----------------------------------------------------------------------------------------------------------------------------------------
 user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=1.1.1.1 application_name='postgresql-93'

But i cannot get host from recovery.conf

My error. How get ip postgresql master from recovery.conf using non-superuser?
Thank you

SELECT regexp_split_to_array
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' ');
ERROR:  syntax error at or near "select"
LINE 1: select regexp_split_to_array(select * from regexp_split_to_t...

OR

SELECT *
FROM regexp_split_to_table
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
     ' ');
ERROR:  syntax error at or near "select"
LINE 1: select * from regexp_split_to_table(select * from regexp_spl...


texts:
I have a file which I believed was an Oracle data dump, but Oracle refuses to import it:

There is a problem reading from the import files
ORA-39143
"C:\nm\data\THEFILE.dmp" may be an original export dump file

The Linux "file" utility says it is DBase 3, but I doubt this, because the records number is not realistic and DBase tools fail to load the file:

THEFILE.dmp: DBase 3 data file (1380929624 records)

Here is an hex dump of the beginning of the file:

0003 4501 5058 524f 3a54 3156 2e30 3230

Any idea what database format it could be?


texts: How to select an item from a column only if none of the value in corresponding column is NULL

texts:
I want to know if I can assemble a part.( Which is possible if I have all the raw material). So from the Table I want to select the item number only if Quantity for raw material is NOT NULL (not zero).
The table is like this

So I want the result AS
Model 2
Model 3
Model 4

The query I am using is
SELECT DISTINCT ItemNumber FROM TABLE WHERE QUANTITY IS NOT NULL

But because in 2 rows Model 1 have some quantity which is not NULL I am getting that in result as well.
Please let me know a way of fixing it.


texts: Creation of a database, hangs up the server for 2 minutes

texts:
I have been using the following script to create databases for a while, but recently, when I run this, it runs for good 2 minutes and slows down sql server to a halt almost.

Any ideas for to look for to diagnose this?

I do notice that while this is happening, one disk seems to be rather busy

USE [master]
GO

CREATE DATABASE [fbg125]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'fbg125', FILENAME = N'D:\SQLData\fbg125.mdf' , SIZE = 1048000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'fbg125_log', FILENAME = N'C:\SQLData_logs\fbg125_log.ldf' , SIZE = 1048000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [fbg125] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [fbg125].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [fbg125] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [fbg125] SET ANSI_NULLS OFF
GO

ALTER DATABASE [fbg125] SET ANSI_PADDING OFF
GO

ALTER DATABASE [fbg125] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [fbg125] SET ARITHABORT OFF
GO

ALTER DATABASE [fbg125] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [fbg125] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [fbg125] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [fbg125] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [fbg125] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [fbg125] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [fbg125] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [fbg125] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [fbg125] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [fbg125] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [fbg125] SET  DISABLE_BROKER
GO

ALTER DATABASE [fbg125] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [fbg125] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [fbg125] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [fbg125] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [fbg125] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [fbg125] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [fbg125] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [fbg125] SET RECOVERY FULL
GO

ALTER DATABASE [fbg125] SET  MULTI_USER
GO

ALTER DATABASE [fbg125] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [fbg125] SET DB_CHAINING OFF
GO

ALTER DATABASE [fbg125] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [fbg125] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [fbg125] SET  READ_WRITE
GO


texts: Postgres advisory lock deadlocks itself?

texts:
I am executing pg_advisory_locks using the following set of statements:

SELECT pg_advisory_lock(317,2);
UPDATE posts SET dislikers = array_remove(dislikers, 7) WHERE id = 317;
update posts set num_dislikes = icount(dislikers), updated_at = now() where id = 317;
WITH update_likers AS (SELECT pg_advisory_unlock(317,2)) select num_likes, num_dislikes, (7 IN (select(unnest(likers)))) as liked, (7 IN (select(unnest(dislikers)))) as disliked from posts where id = 317 LIMIT 1;

These are issued from a multithreaded app. One in 1000 queries, I get a deadlock after the execution of the above set of statements. i.e.  SELECT try_pg_advisory_lock(317,2) returns false. Is there something obvious that I am doing wrong?

Also I noticed that executing the following query:

SELECT *
FROM
(
    SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.pid
) AS foo
WHERE age > '30 seconds'
ORDER BY age DESC;

I get 2 entries for the pid 78573 with one as granted = true and the other as granted =false but they have the same timestamps. See below:

       age       |  pid  | granted | application_name |         backend_start         |          xact_start           |         state_change          | waiting | state  |          query_start          |                             left
-----------------+-------+---------+------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------------------------+--------------------------------------------------------------
 10:53:25.394862 | 78261 | t       | bin/rails        | 2014-10-12 05:20:04.543242-07 |                               | 2014-10-12 08:57:22.950505-07 | f       | idle   | 2014-10-12 08:57:22.949693-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET dislikers =
 10:53:23.50218  | 78573 | t       | bin/rails        | 2014-10-12 05:37:58.120879-07 | 2014-10-12 08:57:24.842375-07 | 2014-10-12 08:57:24.842379-07 | t       | active | 2014-10-12 08:57:24.842375-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET likers = arr
 10:53:23.50218  | 78573 | f       | bin/rails        | 2014-10-12 05:37:58.120879-07 | 2014-10-12 08:57:24.842375-07 | 2014-10-12 08:57:24.842379-07 | t       | active | 2014-10-12 08:57:24.842375-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET likers = arr
 00:57:53.495221 | 90360 | t       | bin/rails        | 2014-10-12 18:43:20.09202-07  |                               | 2014-10-12 18:52:54.849397-07 | f       | idle   | 2014-10-12 18:52:54.849334-07 | SELECT "posts"."id" AS t0_r0, "posts"."content" AS t0_r1, "p

Any insights here would be deeply appreciated.


texts: SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working

texts:
I'm trying to perform a indentity insert inside a instead of insert trigger, but when I try to insert data into the data, it throws and error msg "Msg 544, Level 16, State 1, Line 36
Cannot insert explicit value for identity column in table 'PixTest' when IDENTITY_INSERT is set to OFF.".

I'm using SQL SERVER 2014 Developer Edition 64 Bit installed on Windows 8 64 bit.

Here is some sample code that might help:

CREATE TABLE PixTest
(
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      TESTE VARCHAR(10)
)
GO

CREATE TRIGGER AutoIncrement_Trigger ON PixTest
instead OF INSERT AS
BEGIN

    DECLARE @ID INT;
    SELECT @ID=ID FROM inserted;

    IF COALESCE(@ID,0)<> 0
        BEGIN

            SET IDENTITY_INSERT PixTest ON
            INSERT INTO PixTest(ID, TESTE)
            SELECT ID,TESTE FROM inserted
            SET IDENTITY_INSERT PixTest off

        END

    ELSE

        INSERT INTO PixTest(Teste)
        SELECT TESTE FROM inserted;

END;
go

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')
GO

I googled about using SET IDENTITY_INSERT inside of a trigger, and couldn't find anything saying it is not possible, but the code above doesnt seem to work.

Any ideas?


texts: How to manage datas from specific world areas (i.e. several CRS) in Postgis?

texts:
Until now, I used to store spatial data concerning the French metropolitan territory in a PostGis database. The reference coordinate system chosen is 2154 RGF93/Lambert93.
Very soon, I will have to manage data from overseas. How can I manage these datas, which will have a CRS specific to the region in question, compared to the PostGis database ?
Should we multiply the databases with their "good" CRS according to the datas ? Or find a global CRS, insert all the data after reprojection, then decline views according to the region of the world? Or something else?
I don't know how to approach the subject.
Thank you for your advice.