Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int I inner join 2 table (Termination Reasons with Users) were the two talk to the each other and Termination Reasons table contains 2 column (Id (int) , Name(varchar)) and the other one , Users Table contains 1 column (EmploymentStatusId (int)) . I can use EmploymentStatusId(int) from tbl Users  with Id (int) from another table wich is Termination Reasons beacuse , both of them have same data type (int):

SELECT
u AS Status

FROM
d AS p INNER JOIN d AS u ON u=p
INNER JOIN d AS t ON t = u
WHERE u=214 order by u;

But when i try using EmploymentStatusId (int) from tbl Users with Name (varchar) from tbl Termination Reasons , i got problem:

SELECT
t AS Status

FROM
d AS p INNER JOIN db AS u ON u.I=p
INNER JOIN b AS t ON t= u
WHERE u=214 order by u;

And then Error :
Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int.

i know why ,because they have different data type  , therefore they can not talk to the each other. but i tried also to solve this with  Convert  but idont know ,why or how is not working.Can someone please help or point me in the right direction. Thanks.

The Query ,where i tried with CONVERT ,its look like this:

SELECT
CONVERT(INT, CONVERT(VARCHAR(12), t)) as Status

FROM
db AS p INNER JOIN d AS u ON u =p
INNER JOIN db AS t ON t.Name = u
WHERE u=214 order by u.FirstName;

I am not entirely sure what you are trying to achieve by joining on t.Name and u.EmploymentStatusId as they are different data types, varchar and int. When this join is being completed it is trying to implicitly convert t.Name into an int and it contains data that cannot be converted as the error message shows.

If you are trying to get the employment status of the user with CustomerId of 214 I would do something similar to:

 SELECT
 t.Name AS Status

 FROM
 dbo.Paychecks AS p INNER JOIN dbo.Users AS u ON u.Id=p.UserId
 INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId
 WHERE u.CustomerId=214 order by u.FirstName;

If I have missed the point please clarify exactly what you are trying to achieve.


Problem with MySQL subquery Why does this query

DELETE FROM test
WHERE id = ( SELECT id
             FROM (SELECT * FROM test) temp
             ORDER BY RAND()
             LIMIT 1
           );

sometimes delete 1 row, sometimes 2 rows and sometimes nothing?

If I write it in this form:

SET @var = ( SELECT id
             FROM (SELECT * FROM test) temp
             ORDER BY RAND()
             LIMIT 1
           );
DELETE FROM test
WHERE id=@var;

then it work correctly - is problem in subquery?

The reason the first query does not work consistently has to do with how MySQL processes subqueries. In fact, subqueries will experience rewrites and transformations.

There are four(4) components explained here:

Item_in_optimizer
Item_in_subselect
Item_ref
Left_expression_Cache

From the examples posted, it would be impossible to allow an item_ref to become a self reference. In terms of your single DELETE query, the test table as a whole cannot fully self reference itself because some keys are available during transformation and some are not. Therefore, when a query performs a self-reference, a key (in this case id) can disappear in a transformation even though the actual self-referenced table has the key.

Mysql subqueries are only great for sub-SELECTs, even self-referencing a table multiple times. The same cannot be said for non-SELECT queries.

I hope this explanation helps.


What is a Scalable Storage Mechanism for large TTL data collections We currently have a legacy webservice that stores each xml request/response in Sql Server. The data only needs to persist for 3 days before it is considered expired. Sql Server is not good at deleting rows since every delete forms part of the transaction log. The db currently grows at 6-10gb per day and this is going to increase. Only around 1% of the responses that are stored are ever recalled therefore this is a very write heavy application. Each request/response xml document can be upto 14k in size.

What storage mechanism would you choose for upto 50/100gb of data per day?

I understand the solution is not sustainable and I am really looking for a tactical fix since we cannot easily change how all our clients query and re-query the data. We could look into a db that has native support for TTL (Riak, Postgres) etc or maybe a file/blob s3/azure storage solution is a better fit? The issue with a cloud blob storage solution could be lookup performance if we had to scan multiple buckets (since buckets have capacity limits) especially compared to the current sql server single table lookup.

Open to ideas and suggestions?

I have created a very simple demo of how partition switching might work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1
IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date
IF EXISTS ( SELECT * FROM sys.partition_functions WHERE name = 'pf_date' ) DROP PARTITION FUNCTION pf_date
GO

CREATE PARTITION FUNCTION pf_date (DATE) AS RANGE RIGHT FOR VALUES ( '1 Jan 2013', '1 Feb 2013', '1 Mar 2013', '1 Apr 2013', '1 May 2013', '1 Jun 2013', '1 Jul 2013', '1 Aug 2013', '1 Sep 2013', '1 Oct 2013', '1 Nov 2013', '1 Dec 2013' );
GO

-- !!TODO don't use ALL TO PRIMARY, instead create individual files and filegroups
CREATE PARTITION SCHEME ps_date AS PARTITION pf_date ALL TO ( [PRIMARY] )
GO

IF OBJECT_ID('dbo.largeTable') IS NULL
CREATE TABLE dbo.largeTable
    (
    rowId INT IDENTITY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded DATE DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION,

    CONSTRAINT pk PRIMARY KEY(dateAdded, rowId)
    ) ON [ps_date](dateAdded)
GO

CREATE TABLE dbo.largeTable1
    (
    rowId INT IDENTITY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded DATE DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION,

    CONSTRAINT pk2 PRIMARY KEY(dateAdded, rowId)
    ) ON [PRIMARY]
GO

-- Create some dummy data
INSERT INTO dbo.largeTable DEFAULT VALUES
GO 5

-- Multiply the data a bit
INSERT INTO dbo.largeTable ( someData, dateAdded, addedBy )
SELECT someData, DATEADD( month, -2, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, -1, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, 1, dateAdded ), addedBy
FROM dbo.largeTable
GO

-- Have a look at the data
SELECT 'before' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Switch out oldest partition with data and truncate it
ALTER TABLE dbo.largeTable SWITCH PARTITION 9 TO dbo.largeTable1
GO

TRUNCATE TABLE dbo.largeTable1
GO

SELECT 'after' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Merge the range as no longer required
ALTER PARTITION FUNCTION pf_date() MERGE RANGE ( '1 Sep 2013' );
GO

TRUNCATE TABLE can be a minimally logged operation under certain conditions.  Please consult the Data Loading Performance Guide for a fuller treatment on the topic.  There is also a section on "Deleting All Rows from a Partition or Table".

Good luck!


Mongo zoned sharding with a single value We have a database with around 50 collections, and 70 million records. All records belong to specific customers and have a customerId property. Currently all clients are in the USA. However, we're in the process of adding EU clients and would like to host their data in an EU datacenter.

Since all records have a customerId (which is a string) and virtually all of our queries also specify this customerId this makes it a good choice for a shard key for us - we're only going to have one shard per zone, and we'd like all of a customers data to be in that one shard.

My question is, given that the customerId's are strings, how do we specify a minimum and maximum for the sh.updateZoneKeyRange() function? Obiously minimum will be the customerId (e.g., "customer-somename") but how do we specify a maximum of "customer-somename" + 1? The issue here is that maximum is exclusive, so it can't be the same as minimum

Assuming your customerId does not have any obvious mapping to location, it would be best to add a location field and create a compound shard key. The location field should be a prefix of the shard key, otherwise you will have an administrative headache trying to maintain zones for small ranges of customerID values (which could end up as granular as a single customer).

With a compound shard key of  {location: 1, customerId:1} your zone ranges would be straightforward to define.

For example:

sh.updateZoneKeyRange(
  "mydb.mycollection",
  { "location" : "US", "customerId" : MinKey },
  { "location" : "US", "customerId" : MaxKey },
  "US"
)

The MongoDB manual includes a similar example in Segmenting Data by Location and this is the same approach used for MongoDB Atlas' Global Clusters feature. Both of these examples suggest two-character country codes (ISO 3166-1 Alpha-2) for the location field, but you could choose any values which provide suitable granualarity for your future use cases. For example, ISO 3166-2 would provide for countries and subdivisions (administrative regions like state or province).

Starting in MongoDB 4.2, you can also change a document's shard key value if you need to move existing user data between zones. You presumably would never want to change your customerId values, but could update a location field.


Recommended innodb_file_per_table value for AWS MySQL RDS database We have the following scenario: Our databases are hosted on AWS MySQL 5.6, and we are planning the following migration: we currently have 10 distinct instances with about 50 databases split between them, and we desire to migrate to a single instance containing all 50 databases. Each database has about 600 tables. All bases have the same structure, although the volume of data and traffic is different for each one of them. The main purpose of the change is to stack up the resource consumption curves of our different clients, setting up a server properly with fewer resources than the sum of the 10 current instances.

A point that is not standardized between instances, and what I need to define at this point, is the innodb_file_per_table parameter (which will define whether to use a shared tablespace, or individual tablespace for each table). I could not find enough information to decide on it.

The materials I've read talk about advantages for disaster recovery, however our AWS environment already has adequate redundancy, so I want to decide according to the performance (latency, IOPS, amount of open connections, ...). Our application does not perform so much DROP TABLE operations, and we are aware of the specifications of memory, CPU, network, etc., necessary for our centralized instance. Our focus is mainly quick response for our clients.

Based on the "Potential Disadvantages of File-Per-Table Tablespaces" section of the documentation at https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html, using a shared tablespace can bring more speed in writing operations.

Further information is that after we complete the unification of the databases and make some adjustments, we have plans to upgrade from MySQL 5.6 to MySQL 5.7. Thus, the issue of the limit of 10000 tables for instances above 200 GiB is not at all relevant.

We thank you in advance for any suggestions on the best option to adopt, or the indication of materials that help in this decision.

Complex answer

All tables are "small"?  Use =0.
All tables are "large"?  Use =1.
A mixture?  You could do some one way, some the other.  Such as using file_per_table=1 for tables over 10MB.

Simple answer

OK, that is more complexity than you would like.  In that case, let's go with the "short answer":  Use whatever AWS has it set to.  It won't matter much.

In particular, none of the metrics you quoted raise any alarms saying that you must go one way or the other.


Is it possible to connect MS SQL Server Express 2012 through DAC Connection? Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked out using this TSQL

select * from sys.dm_os_schedulers;

The schedular is not showing DAC (Dedicated administrator connections) scheduler ID.

Any idea or suggestion will be appreciate.

To use the DAC in Express, you need to enable startup trace flag 7806. This is documented here and in more detail here.

Open Configuration Manager, in your SQL Server Services pane right-click the Express instance and select Properties. On the Startup Parameters tab add -t7806, hit Add, then OK, and restart the service.

You can check the error log or the DMVs after the restart to verify the DAC is enabled.

The reason this is disabled by default in SQL Server Express is for performance reasons (you're already limited to 1GB of memory, and DAC locks up a chunk of that). So, unless you are really sure you're going to need the DAC, it might be best to leave it off.


Database link from Local Oracle 11g to Oracle Database Cloud Service 11g We are using Oracle database from Oracle Database Cloud Service,
now we are not able to connect Cloud db from Local oracle db.

Somehow we created link using below commnd,

CREATE DATABASE LINK link_1 CONNECT TO "username"
IDENTIFIED BY "password" USING 'https://x.x.x.oraclecloudapps.com/apex/';

Now once i execute query select * from EMP@link_1 It throws an error

ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"

with Cause and Actions.

Let me know if some additional configuration/changes required.

That is not how you connect to another database through database links. Database links require SQL*Net connectivity, not HTTP(S).

First you need to enable access, then you can connect using the address of the compute node and the service name for your database.

http://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-372D4233-9793-47EC-8DFC-AFD6CF32866C.htm#CSDBI3449


Are there any benefits in certain ordering of columns when defining indexes For example, if I have two indexes:

CREATE INDEX IDX_1 ON MY_TABLE_1
 (ITEM, DATE, LOCATION)
 COMPUTE STATISTICS;

CREATE INDEX IDX_2 ON MY_TABLE_1
 (DATE, LOCATION, ITEM)
 COMPUTE STATISTICS;

Would this make IDX_2 redundant? If not, how do I determine the order of declaring the columns?

Should I be tailoring indexes to regular queries?

Yes, the benefit comes when you want to query on a part of the index.  If you put the part-used predicates first the index can be used for queries that involve those predicates but not all of the columns in the index.

Also, unless you have other requirements it can help to put the most selective predicates first, as this can trim down index seek operations faster.

In your case IDX_2 is not necessarily redundant depending on the nature of the queries on the table.  However, it may not be necessary to include all of the columns.  If, for example, you do a lot of queries by location and date then IDX_2 may be useful to help resolve those queries as IDX_1 is not in the right order to be useful for that.  You might, however, find that item is redundant on IDX_2.

From 9i, Oracle introduced a 'skip scan' operator where trailing index columns can be queried more efficiently, which can reduce the need for supplementary indexes of this sort.

In a more specific case, if you are querying item by location and date and don't need any other columns then the query could be completely resolved though the index without having to read anything from the table.  You can also build covering indexes that have non-indexed columns attached.  If all of the needed columns can be resolved from the covering index the query doesn't need to touch the main table at all.

Finally, in answer to your last question: If you have a set of regularly used queries that are using up a lot of resources and could be tuned using an index then it's certainly worth considering.  However, maintaining indexes comes with an overhead on inserts, so you will have to trade query performance off against the overhead that the indexes place on insert or update operations.


Differences between the different installers of SQL Server 2014 Express I'm trying to understand the differences between the different installers of SQL Server 2014 Express.  If you are able to tell me whatever you can or find documentation on them, I'd appreciate it.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=42299

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe
Express 32BIT\SQLEXPR_x86_ENU.exe
Express 64BIT\SQLEXPR_x64_ENU.exe
ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
LocalDB 32BIT\SqlLocalDB.msi
LocalDB 64BIT\SqlLocalDB.msi
MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe
MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe

  This is for installing the 32-bit version of SQL Server Express, just the engine, on a 64-bit OS in WoW64 mode. WoW64 mode is described in excruciating detail here.

Express 32BIT\SQLEXPR_x86_ENU.exe

  This is for installing the 32-bit version of SQL Server Express, just the engine, on a 32-bit OS.

Express 64BIT\SQLEXPR_x64_ENU.exe

  This is for installing the 64-bit version of SQL Server Express, just the engine, on a 64-bit OS.

ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe

  This is for installing a 32-bit engine and advanced services on a 32-bit operating system. "Advanced services" are explained here.

ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe

  This is for installing a 64-bit engine and advanced services on a 64-bit operating system. "Advanced services" are explained here.

ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe

  This is for installing a 32-bit engine and tools (most importantly SSMS and Profiler) on a 32-bit operating system. "Express With Tools" is explained here.

ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe

  This is for installing a 64-bit engine and tools (most importantly SSMS and Profiler) on a 64-bit operating system. "Express With Tools" is explained here.

LocalDB 32BIT\SqlLocalDB.msi

  This is for installing LocalDB on a 32-bit operating system. LocalDB is described here.

LocalDB 64BIT\SqlLocalDB.msi

  This is for installing LocalDB on a 64-bit operating system. LocalDB is described here.

MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe

  This is for installing a standalone Management Studio on a 32-bit operating system.

MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

  This is for installing a standalone Management Studio on a 64-bit operating system.


best index type for uuid in postgres 11 I realize a similar question has been asked multiple times but all the ones I can find are from 2014/2015 and many versions of postgres ago.

I'm on postgres 11. I'm introducing a new primary key for a table of type uuid. I see that since postgres 10, hash index types are in pretty good shape ( e.g. http://rhaas.blogspot.com/2017/09/postgresqls-hash-indexes-are-now-cool.html ), and I'm wondering if it's the better choice for a uuid key in postgres these days?

Update I realize I'm missing part of the use case which impacts this question.

My primary use case is that I previously had a composite primary key and am replacing it with a UUID for easy joins and to not have to propagate the primary key's data to other tables.

In this sense, I'm likely to want create the primary key using INCLUDE (composite, fields, here) which is only supported for btree index types. So in that sense, I have my answer.

I am still curious about a 'generic' use case of simply wanting rapid access to looking up a value by its primary key, however.

My primary use case is that I previously had a composite primary key
  and am replacing it with a UUID for easy joins and to not have to
  propagate the primary key's data to other tables.

A hash index wouldn't work for that: it can't enforce uniqueness, therefore it can't be used to support a primary key. As mentioned in the v11 documentation:

  Currently, only B-tree indexes can be declared unique.


PostgreSQL not working, pgAdmin always says server not listening I am brand new at this so maybe it's a simple mistake. I was given a very large .sql file with the instructions to make a copy of a database locally on a computer. I was told "to be able to install the database you need to install postgres. Use pgAdmin to create a table called reports with UTF8 encoding".

pgAdmin III always tells me the server is not listening. Postgres is not showing up anywhere in the services in administrative tools. Whenever I install or uninstall Postgres 9.1 or 9.2 I get errors. When I uninstall I get "Error stopping service postgresql-x64-9.2", "Error deleting service postgresql-x64-9.2", "The data directory (C:\Program Files\PostgreSQL\9.2\data) and service user account (NT AUTHORITY\NetworkService) has not been removed".

I was advised to login to the postgres account, however none appears at the login screen. If I try to create an account named postgres I get a bogus error message (see picture)

I was advised to type this into the command line:

When installing I get this error:

Since you installed multiple versions of PostgreSQL, there is a good chance your cluster is running on a different port than 5432. Additional clusters automatically use the next free port unless instructed otherwise. Like 5433, 5434 etc.

Check your setting in postgresql.conf to find out and connect to the proper port. postgresql.conf is usually installed in your data directory, which in turn is defined in pg_env.bat, usually located here

C:\Program Files\PostgreSQL\<version>\pg_env.bat

Default location of the data directory:

C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

Where <version> is 9.1 or 9.2 in your case.
Base directory varies according to your version of Windows.

Obviously initdb fails to create a database cluster. Read the chapter in the manual to get a grip on initdb. Maybe a problem with missing privileges?

Here is a closely related question with a solution.


SUM over distinct rows with multiple joins Schema:

CREATE TABLE "items" (
  "id"            SERIAL                   NOT NULL PRIMARY KEY,
  "country"       VARCHAR(2)               NOT NULL,
  "created"       TIMESTAMP WITH TIME ZONE NOT NULL,
  "price"         NUMERIC(11, 2)           NOT NULL
);
CREATE TABLE "payments" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);
CREATE TABLE "extras" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);

Data:

INSERT INTO items VALUES
  (1, 'CZ', '2016-11-01', 100),
  (2, 'CZ', '2016-11-02', 100),
  (3, 'PL', '2016-11-03', 20),
  (4, 'CZ', '2016-11-04', 150)
;
INSERT INTO payments VALUES
  (1, '2016-11-01', 60, 1),
  (2, '2016-11-01', 60, 1),
  (3, '2016-11-02', 100, 2),
  (4, '2016-11-03', 25, 3),
  (5, '2016-11-04', 150, 4)
;
INSERT INTO extras VALUES
  (1, '2016-11-01', 5, 1),
  (2, '2016-11-02', 1, 2),
  (3, '2016-11-03', 2, 3),
  (4, '2016-11-03', 3, 3),
  (5, '2016-11-04', 5, 4)
;

So, we have:

3 items in CZ in 1 in PL
370 earned in CZ and 25 in PL
350 cost in CZ and 20 in PL
11 extra earned in CZ and 5 extra earned in PL

Now I want to get answers for the following questions:

How many items we had last month in every country?
What was the total earned amount (sum of payments.amounts) in every country?
What was the total cost (sum of items.price) in every country?
What was the total extra earnings (sum of extras.amount) in every country?

With the following query (SQLFiddle):

SELECT
  country                  AS "group_by",
  COUNT(DISTINCT items.id) AS "item_count",
  SUM(items.price)         AS "cost",
  SUM(payments.amount)     AS "earned",
  SUM(extras.amount)       AS "extra_earned"
FROM items
  LEFT OUTER JOIN payments ON (items.id = payments.item_id)
  LEFT OUTER JOIN extras ON (items.id = extras.item_id)
GROUP BY 1;

The results are wrong:

 group_by | item_count |  cost  | earned | extra_earned
----------+------------+--------+--------+--------------
 CZ       |          3 | 450.00 | 370.00 |        16.00
 PL       |          1 |  40.00 |  50.00 |         5.00

Cost and extra_earned for CZ are invalid - 450 instead of 350 and 16 instead of 11.
Cost and earned for PL are also invalid - they are doubled.

I understand, that in case of LEFT OUTER JOIN there will be 2 rows for item with items.id = 1 (and so on for other matches), but I don't know how to build a proper query.

Questions:

How to avoid wrong results in aggregation in queries on multiple tables?
What is the best way to calculate sum over distinct values (items.id in that case)?

PostgreSQL version: 9.6.1

Since there can be multiple payments and multiple extras per item, you run into a "proxy cross join" between those two tables. Aggregate rows per item_id before joining to item and it should all be correct:
SELECT i.country         AS group_by
     , COUNT(*)          AS item_count
     , SUM(i.price)      AS cost
     , SUM(p.sum_amount) AS earned
     , SUM(e.sum_amount) AS extra_earned
FROM  items i
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   payments
   GROUP  BY 1
   ) p ON p.item_id = i.id
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   extras
   GROUP  BY 1
   ) e ON e.item_id = i.id
GROUP BY 1;

Consider the "fishmarket" example:

Two SQL LEFT JOINS produce incorrect result

To be precise, SUM(i.price) would be incorrect after joining to a single n-table, which multiplies each price by the number of related rows. Doing it twice just makes it worse - and also potentially computationally expensive.
Oh, and since we do not multiply rows in items now, we can just use the cheaper count(*) instead of count(DISTINCT i.id). (id being NOT NULL PRIMARY KEY.)
SQL Fiddle.
But if I want to filter by items.created?
Addressing your comment.
It depends. Can we apply the same filter to payments.created and extras.created?
If yes, then just add the filters in the subqueries as well. (Does not seem likely in this case.)
If no, but we are still selecting most items, the above query would still be most efficient. Some of the aggregations in the subqueries are eliminated in the joins, but that's still cheaper than more complex queries.
If no, and we are selecting a small fraction of items, I suggest correlated subqueries or LATERAL joins. Examples:

Multiple array_agg() calls in a single query


Tempdb with Multiple mdf and Renaming We just inherited a legacy Tempdb database with Multiple MDF files. Tempdb1 is the primary.

Example:

Tempdb1.MDF
Tempdb2.MDF
Tempdb3.MDF

(1) Was tempdb2.mdf and tempdb3.MDF being ignored the whole time?

(2) Or Is is true naming extensions don't matter?  I can call 2 and 3, .mydat, or .datafile, etc.  And they are still inferred as secondary?

(3) However, for best practice should I label 2/3 with .ndf?

SQL Server uses a round robin with proportional fill algorithm for TempDB and data files in general.  Thus if you have 1 TempDB file and add 2 more, only the other 2 will be filled until they reach the size of the first one and all will then grow proportionally; or when the next restart occurs and they all get emptied out.

Thus you will be using those files.  You can see them being used in the physical index stats DMV.  Naming doesn't matter, just do what works for you to manage them.


Can't use SQL Server database on a networkpath I am working on a project for a very long time now. It's a .NET application which works with a SQL Server database (.mdf, LINQ to SQL). Now I am almost finished and wanted to run some tests.

But here is where I totally screw up. Until now I had my application only running on my own pc with the .mdf file being stored locally. What I actually planned was an application which connects to a database stored on a networkpath from different machines.

Now I am reading that this is not possible with SQL Server databases?! And I also get the message:

  The file xxx.mdf is on a network path that is not supported for
  database files.

Can anybody think of an option how I could solve this problem without reprogramming the whole database stuff again?

One possible a clearly desperate solution I thought of first, was to:

Let my application find the database file on the network path
Copy the .mdf + .log files to a local folder
Do the Read/Write operations...or whatever the user wants to do
And at the end copy/replace the whole .mdf + .log files back to the network folder

If there is no other solution for my problem, would my solution work? If it would, what would be a possibility to avoid concurrent updates?

Please help and thanks in advance!

An MDF files is not the database, as such. It is merely the on-disk storage used by the SQL Server database engine.

When your app "uses" the MDF, it is actually attaching them to an SQL Server install and running queries against that. This is transparent really.

Instead of this, you can install SQL Server on the network (even Express edition), attach tehe database, and connect to that from all your app installations on the network.

This SO answer has way more details: https://stackoverflow.com/q/10366586/27535


Create Nested XML in a Sql Server Indexed (Materialized) View This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this:

SELECT Col1,
       Col2,
       ( SELECT Col3, Col4
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE;

(Using a subquery)

This is exactly at odds with the requirements of an Indexed View (no subqueries allowed).

Is there anyway to get these two features together?  (Have an indexed view that has an XML Tree?)

The short of my question is: How can I create an XML Tree (ie nested XML nodes) from many tables without using any subqueries or unions.  (So it will work with an SQL Server Indexed View.)

FYI: Not sure if it matters, but my specific query has over 10 levels of my xml tree (pulled from a bunch of different tables).

Short answer: You can't.

Long answer:

There's a few things going on here.

First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". Your provided example, in fact, could be handled without the nested XML generation.

Let's make some test tables and data:

USE tempdb;

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;

CREATE TABLE T1
(
    Col1 int,
    Col2 varchar(50),
    [Col] char(1)
);

CREATE TABLE T2
(
    [Col] char(1),
    Col3 varchar(50),
    Col4 varchar(50)
);

INSERT INTO T1 (Col1, Col2, [Col]) VALUES
    (1, 'Test1', 'A'), (2, 'Test2', 'B');
INSERT INTO T2 ([Col], Col3, Col4) VALUES
    ('A','1x','1y'), ('A','2x','2y'), ('B','3x','3y'), ('B','4x','4y'), ('B','5x','5y');

Your query:

SELECT
    Col1,
    Col2,
    (
        SELECT      Col3, Col4
        FROM        T2
        WHERE       T2.Col = T1.Col
        FOR XML AUTO, TYPE
    )
FROM        T1
FOR XML AUTO, TYPE;

Produces the same results as one without any nesting:

SELECT
    Col1,
    Col2,
    Col3,
    Col4
FROM        T1
JOIN        T2
ON          T2.Col = T1.Col
FOR XML AUTO, TYPE;

They both produce:

<T1 Col1="1" Col2="Test1">
  <T2 Col3="1x" Col4="1y" />
  <T2 Col3="2x" Col4="2y" />
</T1>
<T1 Col1="2" Col2="Test2">
  <T2 Col3="3x" Col4="3y" />
  <T2 Col3="4x" Col4="4y" />
  <T2 Col3="5x" Col4="5y" />
</T1>

(That will break down if you have more complex or custom nesting requirements.)

Regardless of this particular example, I don't think there's an effective way to get (dynamic) XML into an indexed view.

First, an "indexed view" is really a view with a unique clustered index. You can't create "normal" indexes on an XML column.

Let's say you try:

CREATE TABLE T3
(
    x xml
);
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON T3 (x);
GO

You will get an error:

  Msg 1977, Level 16, State 1, Line 73 Could not create index 'IX_T3' on
  table 'T3'. Only XML Index can be created on XML column 'x'.

What about an XML index? Well:

  You cannot create an XML index, either primary or secondary, on an xml
  column in a view, on a table-valued variable with xml columns, or xml
  type variables.

CREATE XML INDEX (Transact-SQL)

So in order to have the XML materialized using a view, it can't be the only column. You need a "normal" column to create the unique clustered index on. One way to get a normal column would be to have the XML be created with a subquery. But as you point out, if you try this:

CREATE VIEW V1
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    1,
    (
        SELECT 'Test' AS 'Col'
        FOR XML PATH, TYPE
    )
GO

CREATE UNIQUE CLUSTERED INDEX IX_V1 ON V1 (n);
GO

You will get this error:

  Msg 10127, Level 16, State 1, Line 83 Cannot create index on view
  "tempdb.dbo.V1" because it contains one or more subqueries. Consider
  changing the view to use only joins instead of subqueries.
  Alternatively, consider not indexing this view.

You might think you could cheat by creating an outer view which didn't have a subquery, calling the original view. But if you try:

CREATE VIEW V2
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    n,
    x
FROM    dbo.V1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V2 ON V2(n);
GO

You will get an error:

  Msg 1937, Level 16, State 1, Line 98 Cannot create index on view
  'tempdb.dbo.V2' because it references another view 'dbo.V1'. Consider
  expanding referenced view's definition by hand in indexed view
  definition.

So you can't nest these views, and you can't use subqueries. You can't use the APPLY operator either, so you can't use a table-valued user-defined function (nor can you effectively use the nodes XML function).

But you can use deterministic scalar-valued functions. So you can construct some rudimentary XML. For example:

CREATE FUNCTION F1
(
    @Col1 int,
    @Col2 varchar(50)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1,
                @Col2
            FOR XML PATH(''), TYPE
        )
END
GO

CREATE VIEW V3
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F1(T1.Col1, T1.Col2)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V3 ON V3(n);
GO

While you could create complex, multi-level XML within the function, the function cannot reference any tables (or it wouldn't be deterministic). For example, this:

CREATE FUNCTION F2
(
    @Col1 int,
    @Col2 varchar(50),
    @Col char(1)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1 AS Col1,
                @Col2 AS Col2,
                (
                    SELECT      Col3, Col4
                    FROM        dbo.T2 AS T2
                    WHERE       T2.Col = @Col
                    FOR XML AUTO, TYPE
                )
            FOR XML PATH, TYPE
        )
END
GO

CREATE VIEW V4
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F2(T1.Col1, T1.Col2, T1.Col)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V4 ON V4(n);
GO

Results in this error:

  Msg 10133, Level 16, State 1, Line 181 Cannot create index on view
  "tempdb.dbo.V4" because function "dbo.F2" referenced by the view
  performs user or system data access.

Therefore the function can't go get the information it needs. It must be fed the information. And there's simply no way (that I can think of) to collect the data from multiple rows and feed it to the function. You can feed a deterministic function XML to manipulate, but you can't create the base XML without a subquery.

So in short, I don't think there's any way using an indexed view to materialize XML which contains data from multiple rows in any table.


Stored Procedure doesn't show result in SQL Server I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP doesn't return any result. It should return more than 1 rows

 CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS
    BEGIN

        SELECT  [Capital Project] ,
                [Capital Task] ,
                ResourceName ,
                ProjectName ,
                [Project Home Group] ,
                ActualWork ,
                TimeByDay ,
                ResourceStandardRate ,
                ActualWork * ResourceStandardRate AS Dollars ,
                [Project Hierarchy]
        FROM    [IR.CapOnly]
        WHERE   ( TimeByDay >= @StartDate )
                AND ( [Project Home Group] = ( @ProjHomeGrp ) )
                AND ( TimeByDay <= @EndDate )
                AND ( ActualWork > 0 )
                AND ( [Project Hierarchy] = ( @ProjHier ) )
        ORDER BY ProjectName ,
                ResourceName

    END

You can see that in the where clause, Project Home Group (@ProjHomeGrp) and Project Hierarchy (@ProjHier) are the parameters, whose value when supplied (more than 1) SP returns zero values.

The value that I'm passing are:

Start Date: 1/1/2011
End Date: 12/31/2012
@ProjHomeGrp : PHG1,PHG2,PHG3,PHG4,PHG5,PHG6,PHG7
@ProjHier: PROH1, PROH2, PROH3

Let me know for any questions!

You need to split your parameters into rows and use where ... in as a check.

Using a split function like this one your code could look like this.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

    INSERT INTO @ProjHomeGrpTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHomeGrp);

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

    INSERT INTO @ProjHierTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHier);

    SELECT  [Capital Project] ,
            [Capital Task] ,
            ResourceName ,
            ProjectName ,
            [Project Home Group] ,
            ActualWork ,
            TimeByDay ,
            ResourceStandardRate ,
            ActualWork * ResourceStandardRate AS Dollars ,
            [Project Hierarchy]
    FROM    [IR.CapOnly]
    WHERE   ( TimeByDay >= @StartDate )
            AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END


How is powershell commonly used in SQL server? I am wondering how is powershell used in SQL Server? I heard people saying that powershell makes a DBA's life easier. But I dont know how is powershell commonly used in SQL server. Can anyone provide me insights or links as to how powershell is being used in SQL Server?

So the main thing is that you can set these up to be dynamic. You wouldn't have to log into each instance to find out the settings. It does make it easier to perform a health check and just keep track of the lot. I used it for Parsing Excel documents to SQL server. This turned out to be very simple instead of converting it to csv documents and importing from there.

But what I would do is check into this site, it has a pretty good repository. A bit old though...

Additionally, Microsoft is making a huge amount of enhancements to powershell into SQL server. It was ignored previously as it was already a scripting language. Most people now are going into proper desired state configuration where you can spin up a SQL server instance with memory and all the settings you would like inside the SQL server instead of it just setting up the windows box and you needing to log into the SQL instance to run scripts. Pretty good post about it here.


Determine if columns in postgres table are inherited I want to be able to list out the columns in a child table and see which ones are inherited from the parent table and which ones are defined on the child table.

SELECT column_name, inheritied
FROM column_view
WHERE table_name = 'a_child_table'

------------------------------------
| column_name       |  inherited   |
------------------------------------
| foobar            |      f       |
------------------------------------
| whizbang          |      t       |
------------------------------------

Is this possible?

This query uses pg_inherits, which tracks table inheritance:

SELECT a.attname,
       EXISTS (
          SELECT 1
          FROM pg_inherits AS i
             JOIN pg_attribute AS a2
                ON i.inhparent = a2.attrelid
             WHERE i.inhrelid = a.attrelid
               AND a.attname = a2.attname
       ) AS inherited
FROM pg_attribute AS a
WHERE a.attrelid = 'tablename'::regclass
ORDER BY a.attnum;


Can I use only 1 user for SQL Server replication? I want to setup transactional replication on sql server. By following the instructions of the ms replication preparation tutorial and the ms replication configuration tutorial, everything works fine.

My question is: Why all these 4 users? Will there be any trouble if instead of 4 I only use one windows user/db login for every step?

If you use 1 user, you typically end up giving that user more access than it needs. By having the example use 4 different users, it helps demonstrate the good practice of using dedicated accounts for particular purposes. If one is compromised, there’s less impact on the overall system. If the example used just one user, everyone would assume it had to be that way.


SQLOS Resource Monitor and QueryMemoryResourceNotification Win API Here's some info about SQLOS and the Resource Monitor I found from Jonathan Kehayias:

  As a general rule, SQL Server will use as much memory as you can give
  it, and it will not release the memory that it has allocated under
  normal operations, unless the Windows Server OS sets the memory low
  resource notification flag. A component of the SQLOS called the
  Resource Monitor monitors the QueryMemoryResourceNotification Windows
  Server API and when Windows sets the low memory resource notification,
  the SQLOS will respond by sweeping its caches internally to reduce the
  process working set, and release memory back to Windows.

So, if windows sets the low memory resource notification, when the Resource Monitor discovers it, is there a way for the DBA to know it too?  An event notification?  An alert?  Something else?  Ideally, I'd like to send an email when it happens.

This is called external memory pressure assuming that you set the max memory setting to a reasonable value. Generally it happens when processes other than SQL server (ssis, monitoring agents, iis etc.) consumes memory.

You can identify if a memory pressure is internal ör external from ring buffers which is explained in Jonathan's post

An easier way of determining memory pressure is to monitor memory grants pending and memory grants outstanding performance counters.

In both ways either you have to use a third party monitoring software for alerting or you can create your own alerting mechanism.

Note: Cited on both question and answer :). Cheers Jonathan.


replacing a database with pg_restore I am trying to replace an existing database with pg_restore and am running into issues. I apologize if this issue is elementary.
Specifically my Postgres install is set up like this:

database postgres owned by root
database d owned by u
database restore owned by u (more on this later)

I created a dump like pg_dump --format=custom .... I am now trying to replace d with the contents of that dump. Specifically I am running a command like pg_restore -cC --dbname=$DB --exit-on-error --format=custom --username=$USER.
But it seems no matter what combination of $DB and $USER, Postgres is unable to restore. From the error messages (below) Postgres seems to want the user to simultaneously be root and the owner of d. I have read the man pages and tried various combinations to no avail.
What am I doing wrong?

Specifically, if $DB is d, regardless of $USER, I get

ERROR:  cannot drop the currently open database
Command was: DROP DATABASE d;

Fair enough. So let's try $DB=postgres and $USER=root:

ERROR:  must be owner of database d
Command was: DROP DATABASE d;

Okay, so let's try $DB=postgres and $USER=u:

ERROR:  must be member of role "root"
Command was: ALTER DATABASE d OWNER TO root;

Huh? Maybe that has to do with logging into postgres, which is owned by root? Let's try $DB=restore (created specifically for this purpose) and $USER=u:

ERROR:  must be member of role "root"
Command was: ALTER DATABASE d OWNER TO root;

Evidently, "root" is not a superuser (which is a bit odd, given the normal implications of the word "root").  The pg_restore command you wish to run must be run as a superuser.  Only a superuser can drop someone else's database, and then create a new database owned by someone else.

Although it looks like the restored database is going to be owned by "root", not "u".  I guess that is not what you are expecting, but it is what was encoded in the pg_dump output.


Compatibility level of SQL Server Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?

To know  the current compatibility_level of your database you can run:

SELECT name,compatibility_level
FROM sys.databases
WHERE name = 'DatabaseName';

Result for my TEST database:

name    compatibility_level
TEST    140

If the compatibility level changes, for example with the command:

ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 120;

You could find the date and time that happened in the sql server error log:

Or reading the (current) error log file with xp_readerrorlog:

EXEC sp_readerrorlog 0, 1, 'COMPATIBILITY_LEVEL'

Result

Replace the first parameter of xp_readerrorlog to 1 to read the second error log file, 2 for the third file, ... .


SQL Query to select all under certain age using only date of birth How can I select all that are under 20 and female from a SQL database?

The table has a datetime for the dob and strings for name, surname, and sex. That is the whole table.

I am having trouble calculating the age. I am not even sure this is possible.

I am using SQL Server LocalDB and Visual Studio 2015.

This is totally possible.

SELECT *
FROM <TableName>
WHERE DOB > DATEADD(yy,-20,GETDATE())
  AND Sex = 'F'


sp_AllNightLog - running on an existing SQL server I am trying to get sp_AllNightLog running on an existing server that had regular SQL log shipping (now removed) and Ola Hallengren's backup utility for nightly backups.

I have the procs and jobs configured, but they only take log backups, so it won't seed the secondary server.

I assume this is due to Ola's scripts having been run on the DBs before so they are not reverting to a full back up.  How can I force the full backup on first run?

Great job on troubleshooting, and yes, you are correct. Ola Hallengren's backup scripts need a change as described in this Github issue for his scripts:

https://github.com/olahallengren/sql-server-maintenance-solution/issues/12

Ola hasn't accepted the pull request yet (he's fairly new to Github), so if you want to use my fixed version with the new parameters I describe in that issue, here's my repo:

https://github.com/BrentOzarULTD/sql-server-maintenance-solution

If all that sounds too scary - and I wouldn't blame you - then you'll need to seed the restores yourself manually for each new database.


mysql master-slave replication and status of data consistency I have setup a master-slave replication with mysql 5.7 and GTID.

GTID should be more "safe" than the older way where each statement alone was replicated right?

But when replicating with GTID can I then check the data consistency on the slave just by checking SHOW SLAVE STATUS\G ?

Or do I need to checksum each row in each table to make sure everything is replicated correctly?

Before I address your question, some terminology issues...

GTID uniquely identifies each thing replicated.  This is an improvement on the previous Replication technology -- but mostly when you have multiple machines and need to recover from some kind of failure.  It helps automate things.

bin_log_format = STATEMENT or ROW or MIXED refers to what is transferred from Master to Slave.  STATEMENT (SBR) is the old way; ROW (RBR) is the new way.  There are many subtle reasons why RBR is 'better'.

SHOW SLAVE STATUS provides, among other things, whether or not replication has stopped due to some error.  It reports a crude measure of staleness (Seconds_Behind_Master), but not "consistency" (whether the changes arrived and were correctly applied).

You do not need a checksum on each row.  It would be clumsy for you to add such.  The network layer (TCP/IP, etc) does checksumming to 'guarantee' delivery and to validate the contents of the delivery.

If you want to do your own consistency checking, I recommend Percona's pt-table-checksum .  It takes care of a lot of end-cases that are not obvious to someone starting out to do the checking himself.


How to find when was partition created in SQL Server In SQL Server, is there any way to find when were the partitions created on the table?

Background:
We have around 40 tables which are partitioned along with their indexes. We have total of 4 Filegroups. we have a windows service which is related to partitioning. what it does is, it creates new partitions if the number of records that reached some threshold(other logics are also there) and these partitions are created in one of the 4 filegroups in round robin fashion. we have lot of partitions. I want to know is there anywhere inside SQL Server, does it store the creation time of these partitions?

If not I will be shocked as I feel this is the basic which should be present in SQL Server no matter what. Please provide your inputs.

Perhaps you can use the modify_date of sys.partition_functions.  As new partitions for existing tables are created by ALTERing the partition function.

Or mine these out of the default trace.


Small Cloud Database I'm researching the best way to do the following but haven't been able to come up with anything.  I'm trying to build a small (probably 3 or so tables with at most 200 records each) relational database that is hosted online.  If it could be hosted offline, I'd use Access and call it a day, but I can't figure out an equivalent that is online.

Requirements are:

accessible online via anywhere
ability to restrict viewing of certain information to certain users
once setup, is very easy to use

Preferable:

not a lot of coding - I'm not fluent in any server side coding and don't want to spend the time learning for this small of a project - e.g. Access can do a lot without coding
free though cheap is an option (since this is a small project, it shouldn't cost more than 5-10$ a month)

If this isn't possible, I'd be obliged if someone could direct me to the closest thing to it. Thanks.

If you are strictly interesting in

setting up a quick-and-dirty database
free to use and setup
not worrying about a lot of administration
not being tied down to Storage Engine configuration/tuning specifics

then I would recommend the Xeround Cloud Database service.

I just wrote a post about Xeround's limitations in terms of MySQL and its configurability. Since that is not a concern, Xeround gives you a fairly easy Database to setup and tools to manage it. You can literally create a Xeround MySQL Instance in under 5 minutes.

The free service will give you room to create a 10MB DB. If you need slightly more space, there are pricing options.


Safe way to truncate SQL Server Error Log We are running out of space. What is the safe way to clear the error log?

You can cycle the error log by calling sp_cycle_errorlog and then that will close the current error log and cycle the log extensions.  Basically, it'll create a new error log file that SQL Server will be hitting.  Then the archived error log(s) can be treated accordingly (delete/move with caution).  This will not technically "truncate" the log, it'll just roll it over and you can handle the old logs as you so please, like any other file system file.

When you do this, you should see a new log file with an entry that resembles the following:

  The error log has been reinitialized. See the previous log for older entries.

BOL reference on sp_cycle_errorlog


Import issue: Missing columns values from CSV file I have a problem with the importing of a  CSV file to my table in MySQL. The values (3) of the columns (var1, var2, dates) are not loading, as shown in the image below:

First I created the table,

CREATE TABLE table1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,
    var1 INT NOT NULL,
    var2 INT NOT NULL,
    dates DATE NOT NULL
);

After that, I imported the data to the table:

LOAD DATA LOCAL INFILE '/home/task.csv'
INTO TABLE table1
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

I am using an Ubuntu terminal.

Specify the column list so you won't include id.  Add this after then IGNORE clause:

(var1, var2, dates)


How to repair Microsoft.SqlServer.Types assembly When I run a checkdb('mydb') this is the only error message printed.

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3857, State 1: The attribute (clr_name=NULL) is required but is missing for row (assembly_id=1) in sys.assemblies.

It is referring to 'Microsoft.SqlServer.Types' I do see that in the this db the clr_name is blank. but under the master db there is a value in there.

I tried to drop or alter the assembly to add this value but its restricted.

btw, this db was updated lately from sql-server 2005 to 2008R2.

After searching online a lot, I see the only option to fix that type of corruption is to recreate the database objects and move all the data.

(or restore a backup if available)


Simple Mode - Is using checkpoints to truncate the transaction log a bad practice? I have a database with simple recovery mode. For that database I have a job running every morning. This job imports data and executes plenty stored procedures that inserts data, updates records, delete records, makes calculations... The job takes approximately 30 minutes and makes the transaction log be 10GB big. To avoid growth of the log file during the job I set the log size to 11GB. With that amount it is never autogrowing. Nevertheless it is blocking 11GB of disk space.

I just read that putting checkpoints truncates the log. So my idea is to put checkpoints at several location in my stored procedures in order to be able to minimize the size of the log (from 11GB to lot less: 3GB for instance).

Question: is it dangerous to do that take into consideration once again that I will never have to make recoveries?

For those hitting on that post I actually found a solution to reduce the size of my log by chunking the biggest transactions. That link helped me out :http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes


Linked Server to Mirrored Database Fails I've setup up a mirrored database on two servers Server1 and Server 2.

On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database.

I'm using this to setup the linked server.

EXEC sp_addlinkedserver
  @server = N'MyLinkedServer',
  @srvproduct = N'',
  @provider = N'SQLNCLI',
  @provstr = N'Server=SERVER1;FailoverPartner=SERVER2;Network=dbmssocn',
  @catalog = N'MyDatabase';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'data access',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc',
  @optvalue = N'false';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc out',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'connect timeout',
  @optvalue = N'60';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'MyLinkedServer',
  @locallogin = NULL,
  @useself = N'False',
  @rmtuser = N'sqluser',
  @rmtpassword = N'sqlpassword';

As long as MyDatabase on Server1 is the principal, the linked server works.  When I manually failover to Server2, the linked server fails.

The error messages are:

Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sqluser'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "MyDatabase" requested by the login. The login failed.

The 18456 and 4060 error messages repeat until the query times out after 60 seconds.

On Server1, I can see repeated failures trying to login to "MyDatabase".  It's failing because Server1 is the mirror.  I see no attempts to connect to Server2 which is the principal.

If I recreate the linked server with Server2 as the Primary and Server1 as the Mirror, it will work until I fail back to Server1.

The mirrored servers are SQL Server 2012 and the box I'm trying to create a linked server on is SQL Server 2005, but I also have the same problem when creating the linked server on a SQL Server 2008 R2 box.

I've seen reports that it does work.

What am I missing?

Thank you!

Update

I've made progress.  Yesterday, when I first set things up, I could have sworn it worked once.  I could never reproduce it.  Then, today, I got it to work once.  I can failover from Server1 to Server2 (or vice versa) one time.  The linked server will work after a failover, but if I failover back to the original server, it stop working.  A restart of the SQL Server service on the server with the linked server will let it work again (for 1 failover).  Letting things sit overnight must work too since it worked the first time I tried it today.

Creating a new linked server (with a different name) doesn't work, so I'm guessing that the SQL Native Client driver is caching something.

Another Update

I did get the linked server (as described above) to work correctly on a SQL Server 2012 Express instance.  Then, I tried it on another SQL Server 2012 Standard instance.  It did not work.  One thing that might be relevant is that Server1 and Server2 are in a different untrusted domain than Server3.  But, since I'm using sql server authentication, I wouldn't think it should.  The Express instance that works fine is in the same untrusted domain.

This is a known issue.  Linked servers which are looking for database mirroring logins only work for Windows logins not SQL Logins as talked about on this connect item.  Basically you need to either hard code a username and password in the linked server, which isn't recommended or use Windows authentication to access the linked server.

Another option would be to setup alerts and SQL Agent jobs on the two machines so that when the database becomes active on those machines they automatically reconfigure the linked server with the correct servername.


Activity Monitor paused Some of our end-users still like to use the Activity Monitor in SQL Server.
When setting up our SQL-server 2016 servers the activity monitor always ends up in a paused state when the users are not part of the "Administrators" group in Windows (on the server).

When the users are part of the Administrator-group all data gets through (so probably not a Firewall-problem).
We also tried:

Adding the users to the "Performance Monitor Users" and "Distributed COM Users"
In COM: Changing the limits of "My Computer" and the permissions on "Windows Management and Instrumentation".
The users also have the "View server state" permission in SQL Server.

Do you have any other suggestions?

Kind Regards,

Tim

Found the sollution for this after @TiborKaraszi 's tip about WMI.

Run wmimgmnt.msc on the DB-server
Open the properties of WMI-Control (RM-click)
Open the security tab.
Select CIMv2 en click the Security button below
Add or locate the account or group
In the permissions list, check Remote enable.


Oracle user created information Is it possible to determine what user created another user's account in Oracle database?

I have two types of users in database:

Users having system and object privileges
Users having only system privileges

I can find the owner of all users having system and object privileges.

select distinct d.username,d.account_status,t.privilege,t.owner
from dba_users d,dba_tab_privs t where d.username=t.grantee
and d.account_status='OPEN';

But this query doesn't fetch the users having only system Privileges. Please help how to find the owner of all those users having only system privileges?

DBA_USERS (Table) will give you access to WHEN your user was created, but AFAIK the only way to get WHO created your user is if auditing is turned on.

This also requires you to have access to the DBA_AUDIT_TRAIL table.

SELECT OS_USERNAME,
   USERNAME AS USER_WHO_CREATED_YOU,
   USERHOST,
   TERMINAL,
   TIMESTAMP,
   OBJ_NAME AS USER_CREATED,
   ACTION_NAME
 FROM DBA_AUDIT_TRAIL
 WHERE
TIMESTAMP > SYSDATE-90
AND ACTION_NAME ='CREATE USER'
--AND OBJ_NAME = 'YOURUSERNAMEGOESHERE'

Further Reading: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3056.htm#i1619732


Truncate database in mysql server by half? I have a Mysql server with large DB  over 100 Gigs with lots of tables; I would like to set up a test server with half of the records in DB; what could be the best query to  do it ? Any help is much appreciated.

there is no single query for this, but first what you need to analyze:

are all tables have a similar size or not?
if any relations explicit or implicit?

often case when only a few tables take 80% of space, and in this case, you can copy all other as is and a half for biggest tables.

if foreign keys enabled - you must prepare a proper sequence of queries
if no foreign keys - you must think, do you strictly require data consistency or it just a test data and this moment not important


Full text indexing not working + Mysql 5.6.4 CREATE TABLE `table_copy` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `fulltxt` mediumtext,
  `indexdate` date DEFAULT NULL,
  `size` float DEFAULT NULL,
  `visible` int(11) DEFAULT '0',
  `level` int(11) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`link_id`),
  KEY `indexdate` (`indexdate`),
  FULLTEXT KEY `fulltext2` (`fulltxt`)
) ENGINE=INNODB AUTO_INCREMENT=274212 DEFAULT CHARSET=latin1

The above table contains about 2,74,211 records, when i tried to get data from table it takes 2 minutes to execute.

Query:   SELECT c.link_id from table_copy c where MATCH(fulltxt) AGAINST('Council')

How can i optimize  the above query?

You need check - configuration of server, memory for InnoDB, for FullText

compare size of index and FT buffer size, by default it only 8Mb, max value is 80Mb

Than next step and profile query - how many records returned, what time of intialyze of index

and upgrade 5.6.4 to something more new
in 5.6.4 FullText was only implement for InnoDB, many  bugs fixed from that time


Redundant indexes in SQL Server I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce overhead during batch.

Some of these does not make sense, due to PK columns being used in the middle of other indexes, etc. However, I was wondering how SQL Server handles multi column indexes (with overlapping column).

Examples:

NONCLUSTERED INDEX Index1 (A, B, C)

NONCLUSTERED INDEX Index2 (A, B)

Would SQL Server be able to use Index1 for the same rows as Index2 and Index2 is not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.

Can Index2 be removed without impacting performance?
As far as I have understood, indexes can also help with sorts in execution plan.  Can Index1 be used for these as well?

In general you should be able to remove Index2 without any major issues.  Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).

If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.


Optimize PostgreSQL server setting for extremely wide tables We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM.

On this server we have quite atypical data - there are several tables with approx. 500 columns each, ranging from .5 - 2 milion rows. All columns are either of smallint or integer data type. The typical operations we do:

perform some aggregation function on one column, grouped by another column (this may well be also some window function)
read subset of variables (e.g. 20) and all rows
transform entire table into new table (typically there are no joins to other tables, these are mostly simple case when then end statements

There are two concurrent users at maximum. There is a primary key (single column), but this is almost never used in queries. There are no additional table constraints or indexes.

What would be the recommend configuration of PostgreSQL server? Increasing shared_buffers to 60GB, work_mem to 10GB? Anything else?

Note: Using such strange tables is not bad design in this case. We really have that many attributes for one case. We use this data as an input to in-database machine learning (MADlib) and as data pre-processing storage before using other statistical tools.

How many of those columns to you use for grouping? If it's relatively few, then I would recommend restructuring the data to be in a long format, where each grouping (category) column is maintained, and is each grouped-by (metric) column is instead jammed into two columns variable and value, similar to how R's reshape2::melt function works. For instance, a table:

id|cat1|cat2|metric1|metric2|

Would become:

id|cat1|cat2|variable|value
id|cat1|cat2|metric1|value of metric 1 column
...
id|cat1|cat2|metric2|value of metric 2 column

The table would become K times longer, with K being the number of metrics you'd like to melt. This can actually improve query performance if you add indexes on your category columns.

If that doesn't speed up performance, then I'd recommend using a different tool than Postgres, such as Apache Spark.


Using an index when leading column is not in the predicate Are there any circumstances when an index could be used when not all the index columns are included in the predicate?  In my case the leading column in the index is unique and is part of the unique primary key so I am wondering if SQL can still use the index, as it strikes me it has all the information to know its unique.
e.g.
CREATE TABLE x (a int, b int, c int, CONSTRAINT y PRIMARY KEY CLUSTERED (a ASC, b ASC))

SELECT * FROM x WHERE b = 1

This does not use the clustered index (it uses some other NC index).  Of course if I specify the leading column it does.
 SELECT * FROM x WHERE a = 1 AND b = 1

In my case the leading column in the index is unique ... it strikes me it has all the information to know its unique.

It does not. That primary key does not guarantee that either a or b are unique, just that all combinations of a & b are. There could be many rows for which b = 1 is true, maybe all of them, maybe none of them. When you search for a specific combination of a and b then it can use the index to do a simple seek.
With that table definition, your first query is like asking "find all words in the dictionary where the second letter is 'a'". You can't answer that with a single seek. Your second query is like asking for "words starting 'aa'" which is easy to answer with that index.
Note though that some database systems are able to perform a skip-search to speed up the first query, which would help here, essentially looking for a=1 and b=1 then a=2 and b=1 then a=3 and b=1 and so on (it isn't quite this but as near to as makes little odds). If operation is supported it may be used if the datatype of a and the selectivity indicated by the index stats suggest it might be appropriate. No version of SQL server supports this operation though. Oracle does unless you have a really old version (> a decade or two), as does SQLite, IIRC both call the operation a "skip scan". Further note that in every circumstance where a skip-scan is better than a full index scan, having an index on the second column to use would be more efficient, often significantly so, meaning that while the feature might make some queries better in databases that are not optimised for them, if you expect such queries you should optimise your table(s) for them by having the extra index even if your DBMS supports skip-searches.

it uses some other NC index

To explain why that particular index is selected and used instead of any other, we would need to know how that index and all the other indexes are defined, and perhaps see the query plan. Also you don't say how the index is used - I assume it was scanned rather than being used for seeks.


How to monitor space usage on ASM diskgroups Last night the recovery area on one of our Oracle Database Appliances went full. This was reported in one of the database alert logs, and we were able to clear out some space before the next log switch, at which point the production would have come to a halt.

It certainly would have been nice to have a little more warning, like when the disk group was 70% full.

What options do we have for monitoring disk usage inside ASM?

Just use asmcmd. For example:

[oracle@oel61 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel61 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     30708    28479                0           28479              0             N  DATA/
[oracle@oel61 ~]$

Or get the info directly from the ASM Oracle instance:

[oracle@oel61 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 7 10:44:44 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
     FROM v$asm_diskgroup;

NAME                  FREE_MB   TOTAL_MB PERCENTAGE
------------------------------ ---------- ---------- ----------
DATA                    28479      30708 92.7413052

SQL>


In Oracle, how can I give one user privileges to alter the schema of another user? I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that is used by a lot of my users.

I don't want to give Dogbert the password to the account to the Wally schema. So I've created a temporary account for Dogbert.

How can I give Dogbert's account permission to alter the structure of Wally's schema? I know I can certainly give Dogbert insert/update/delete/select but that doesn't affect the structure.

You can use CONNECT THROUGH to allow Dogbert to connect as Wally:

alter user wally grant connect through dogbert;
grant create session to dogbert;

To connect in sqlplus:

sqlplus dogbert[wally]/scottadams@DATABASE

Some tools may not support these proxy logins.


Creating centralized DBA database We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly appreciate your help. Thank you very much.

On SQL Server 2008 R2 I'd guide you to SQL Server Utility Control Point, with some details here.

From MSDN description:
"The SQL Server Utility models an organization’s SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). Entities that can be viewed in the SQL Server UCP include:

Instances of SQL Server
Data-tier applications
Database files
Storage volumes

Resource utilization dimensions that can be viewed in the SQL Server UCP include:

CPU utilization
Storage space utilization"

On 2008 and earlier - MOM, as Gaius pointed, or maybe give a try to Multi Server Administration as defined by MS. You can see detailed article on SQLServerCentral Multi-Server Administration or on MSDN.


wsrep_sst_auth user permissions In the my.cnf for our PXC cluster nodes, we have been using root user for wsrep_sst_auth with all the admin privileges that comes with the root account. We know using the root user:password inside the config file might not be the most responsible and safe thing to do, so we are thinking to change this. So my question is, what are all the necessary permissions required for the wsrep_sst_auth user that would make the PXC work effectively without having any issue whatsoever?

It seems you are using Xtrabackup-v2 method for SST.

So if you are using that, you should set RELOAD, LOCK TABLES, REPLICATION CLIENT and SUPER privileges to work correctly.

If you do not want set directly user pass in my.cnf you cant start mysql server with this command:

service mysql start --wsrep-sst-auth=username:password


Searching for pairs of date/time values in a single row I have a legacy IBM DB2 database table that contains lots of records of phone calls.  It has columns for ID, customer number, employee who took the call, etc.  The date/time for each call is kept in a pair of columns, ROSDAT and ROSTIM.  ROSDAT is an 8 byte numeric column, and ROSTIM is a 6 byte numeric.  Example:

ROSDAT    ROSTIM
20111006  163243
20111007  012335

So for the first row the date is 2011-10-06, and the time is 16:32:43.  There are millions of records in this table, and I'm trying to make a query that will search it by time and date.  The only way I've found to do that so far is to concatenate the time/date columns into one big number like so:

select * from PHONELOGTABLE
where ROSDAT * power(10, 6) + ROSTIM >= 20111015124500
and ROSDAT * power(10, 6) + ROSTIM <= 20111116013000

This searches for everything from 2011-10-15 12:45:00 to 2012-11-16 01:30:00.  It works, but the concatenation means (as far as I know) that the indexes on the ROSDAT and ROSTIM columns cannot be used, so the query is slower than it should be.  It seems to me that there should be a way to do this with a join or something.

I tried this but couldn't get it to work, and I know I'm missing something:

select a.* from PHONELOGTABLE a join PHONELOGTABLE b on a.ID = b.ID
where a.ROSDAT >= 20111015 and b.ROSTIM >= 124500
and a.ROSDAT <= 20111116 and b.ROSTIM <= 013000

Anybody have an idea?

I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat <> @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)
                )
        )

WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT > @start_rosdat   and  ROSDAT < @stop_rosdat )

   or   (  @start_rosdat < @stop_rosdat
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   or   (  @start_rosdat < @stop_rosdat
      and  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim
                                   and  ROSTIM <= @stop_rostim  )

and another version (parentheses are needed here):

where   (  ROSDAT > @start_rosdat
       or  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   and  (  ROSDAT < @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )


Single table with more columns vs multiple tables with fewer columns What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows?
For example: A user can post an update on their wall or in a group.
Two database designs I can think of are:
Design 1
UserPosts

id
userId
post
datetime

UserGroupPost:

id
groupId
userId
post
datetime

Potential problem : Might require joins, which can (in future) be a slow query.
Design 2
Posts:

id
userId
groupId
post
datetime (where groupid would be null if user posts on their wall)

Potential Problem : Looping over large dataset could take a (long)time.

How can I get better performance when data increases? Is there any other (better) way?

My inclination here would always be design option 1, or at least along those lines. Do not worry too much about trying to eliminate the need to join tables in future queries - any normalized database is going to use joins in any useful queries, that's just relational databases.

Also, why would you necessarily have to join the userPosts and userGroupPosts tables for your website? Would they not be displayed separately? The only reason you would join these tables is maybe when searching for posts, but it shouldn't be too hard to write efficient queries for that. Apart from that you may end up wanting to query the tables for analysis purposes, but that is not the primary purpose of this database.

Design 2 could at the least mean that you end up with a very busy table.

The best option though would be to prototype each and run some tests. Build a prototype of each design option, and do some performance benchmarking on different operations with some dummy data.


How do you test for race conditions in a database? I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I want to be sure that if they do happen in a production environment my application will do the right thing.

I generally know exactly which concurrent query is likely to cause a problem, but I've no idea how to force them to run concurrently to see if the correct behavior happens (e.g. I used the correct type of lock), that the right errors are thrown, etc.

Note: I use PostgreSQL and Perl, so if this can't be answered generically it should probably get retagged as such.

Update: I'd prefer it if the solution was programmatic. That way I can write automated tests to make sure there aren't regressions.

I do it all the time with my T-SQL modules.

Essentially, all you need to do is run your modules from two or more connections in a loop for a couple of minutes. Typically, all potential problems are exposed in a few minutes, assuming you have a SQL Server box with decent CPUs.

I wrote a few examples here and here.


How do I create a simple SELECT statement as a stored procedure in PL/SQL? - Regardless of the best practises around Stored Procs Was wondering how to create a simple select statement to return all records within a table in PL/SQL such as

CREATE OR REPLACE PROCEDURE getStudents
AS
C1 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR
SELECT * FROM STUDENTS
DBMS_SQL.RETURN_RESULT(C1);
END;

I'm a beginner to this, Thanks in advance.

So what is your question? You posted your answer already:

CREATE OR REPLACE PROCEDURE getStudents
AS
C1 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR
SELECT * FROM DUAL;
DBMS_SQL.RETURN_RESULT(C1);
END;
/

Procedure created.

SQL> exec getStudents

PL/SQL procedure successfully completed.

ResultSet #1

D
-
X

SQL>


Referencing specific columns using a variable range I have to shift 20+ columns over one to the left, for a set of rows.  Is there a way to do this without manually typing each column name? I know how to get the data for the specific rows.

For example: move data in column q49 to q48.  move data in column q50 to q49 and so on up to column q79.

I know how to move data, I just wondered how I could write it so I wouldn't have to type the long set list in an update statement:

update tablename
set q48 = q49, q49 = q50, q50 = q51, ...
from ...
where ...

I assume there would be a way to use a variable for the column names but I could be wrong.  How can this be resolved?

As far as SQL Server is concerned, column names must be specified explicitly. There is no way around. As far as you are concerned, names can be parametrised, but that means you need to generate the entire statement dynamically.

Depending on how often you need to do this, there may be no need to use dynamic SQL. If this is a one-off job, you could just generate the assignment list using something like

SELECT
  x.prev_name + ' = ' + c.name + ','
FROM
  sys.columns AS c
  CROSS APPLY (SELECT CONCAT('q', STUFF(c.name, 1, 1, '') - 1)) AS c (prev_name)
WHERE
  c.object_id = OBJECT_ID('dbo.tablename')
  AND c.name BETWEEN 'q49' AND 'q79'
;

Run that in SSMS to get an output like this:

(No column name)
----------------
q48 = q49,
q49 = q50,
q50 = q51,
...
q78 = q79,

Copy the contents of the output column and paste it into the query window where you are writing your UPDATE statement, at the appropriate position. You will only need to make minimal syntactical adjustments, like removing the comma after the final assignment.

Even if I knew I might need to do this kind of data movement again in the future, I would still consider this approach first before actually going the dynamic SQL route.

If you firmly believe you need a more convenient tool for this problem, then you can use the above statement as a starting point for your script. Browse the dynamic-sql questions on this site or on Stack Overflow to find more examples to help you with the final solution.


Sort for specific column by keep other column grouped SELECT
      CurrencyCodeFrom,
      CurrencyCodeTo,
      MidRevalRate,
      MultiplierRate,
      RevalRate,
      CurrencyMarketCode,
      Added,
      Modified,
      UpdateReason
FROM
     [InsightWarehouse].[dbo].[DimCurrency] as dc
 INNER  JOIN
     [InsightWarehouse].[dbo].[FactCurrency] as fc
     ON fc.CurrencyId = dc.CurrencyId
     AND fc.BusinessDate  = '2018-11-27'
     AND ( CurrencyCodeFrom <> 'KHR' OR CurrencyCodeTo <> 'KHR')

Result

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode
THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.750000000   122.750000000   1.000000000 10
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9

I want to sort CurrencyMarketCode DESC by keep it group CurrencyCodeFrom    CurrencyCodeTo

Should be

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode

THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
THB                 KHR             122.750000000   122.750000000   1.000000000 10

USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10


MySQL is insanely slow on fresh installed Ubuntu 17.10 Yesterday I installed Ubuntu 17.10 on my desktop and today I wanted to migrate a MySQL datababase (php artisan migrate:fresh with laravel), but my database was very slow. The migrations only, took around 30 seconds.

On my macbook these migrations take only a few seconds (under 5 seconds).

I tried google and got a lot of settings I could change in my.cnf.
For example the following settings:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

This didn't made it any faster. I also found the option for slow query logging, but the output file is empty after the migrations.

Does someone have any other solutions?

Ps. I removed the settings from my.cnf because it didn't made it faster and I wasn't sure whether to keep the settings or not.

I have mysql-server-5.7.

long_query_time = 1 (or possibly a lower value); this will help get something useful in the slowlog.

With rare exceptions, "You can't tune you way out of a performance problem."

How much RAM?

Find some slow SQL so we can discuss it.  Please provide the corresponding SHOW CREATE TABLE so we know what the table(s) look like.


mysql view with user variables I have a query that calculate distance from point a to point b using gps coordinates, and would like to make it view.

Is it posible to pass variables that will replace xxx and yyy in my query with variables passed from outside of view?

Here's query.

select
    round((((degrees(acos(((sin(radians(xxx)) * sin(radians(`ag_regija_hr`.`lat`))) +
         ((cos(radians(xxx)) * cos(radians(`ag_regija_hr`.`lat`))) *
           cos(radians((yyy - `ag_regija_hr`.`lng`))))))) * 60) * 1.1515) * 1.609344), 2) AS `dist`
from
    `ag_regija_hr`
order by `dist`

check out this one: https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql

ot you can crate sp for this select
 http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html


Using Switch in SSRS In the below expression I am wanting to evaluate several things. I want to turn the value red if the material % is greater than 89.9 %. If it is not then look at the following. is the invoice amount less than 75 dollars and the material % is greater than 20% then turn orange.

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE", Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE", Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",  Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",  Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",  Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",  Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",  Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",  Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",  Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE")

The way i have it now looks like it is stopping after looking at just this:

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE"

Any help is very appreciated!

Tyler

I would recommend you read the expression you have written very carefully because your logic is flawed and that is why your SWITCH statement is not working as you believe it should.

=SWITCH
    (Fields!Matl_.Value > 89.9, "RED",
        Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE",
        Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE",
        Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",
        Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",
        Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",
        Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",
        Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",
        Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",
        Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",
        Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE"
    )

When you format the code, it's easy to see you have written clauses like:

Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150

The value of IHDAR_AMT1.Value cannot be both less than 76 and greater than 150, so it immediately evaluates to FALSE.


How to circumvent ORA-01775 looping chain of synonyms in Oracle Forms? I think I have a database problem... Not necessarily with Oracle Forms (10g)...Let me explain ...

I'm trying to implement this Oracle Forms Menu Security Configuration When I do it, it says that I get ORA - 01775 "Looping Chain of Synonyms".... Since I have NO IDEA what synonym is creating the problem, I was wondering how you guyz can help me fix this issue.... Maybe there is a DBA script somewhere to solve this....

UPDATE

Oracle Database trace  log says the culprit SQL is :

select ROLE GRPNAME ,FLAG GRPFLG from FRM50_ENABLED_ROLES order by ROLE desc  and FRM50_ENABLED_ROLES is created from step 1 here

That statement executes properly where I execute it in SQL*PLUS

Set the below event in the database:

alter system set events '1775 trace name errorstack level 1';

This will make all the ORA-01775 errors logged in the alert log plus a trace file, and that trace fail should contain the Current SQL statement, that caused the error.


forensically deleting/updating data I have a need to forensically remove data from oracle. If I just delete it, my understanding is the data will still actually be in the data file until that space is reused. I'm not concerned about the redo/archive/undo space, those will age out reasonable quickly.

Is there any methods for ensuring data is actually removed from a data file?


Update column with the value of two columns multiplied I want to update ColumnA with the value of ColumnB x ColumnC. Can someone please help? I have tried the following:

UPDATE database.table
SET ColumnA = ColumnB * ColumnC;

Error message as follows:

  ERROR:  operator does not exist: double precision * character varying
  LINE 2: SET ColumnA = ColumnB * ColumnC;
                                  ^ HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
  SQL state: 42883 Character: 58

Text values must be converted to numeric before any math operation.

UPDATE schema.table
SET    ColumnA * ColumnC::numeric  -- Use same data type as ColumnA

Have a look at this tutorial


Slow query performance on simple select I have 2 tables:

Organization (2.500.000 rows)
City (35.000 rows)

'Organization' has 'city_id' set as foreign key to 'cities'.'id':

Organization:

ALTER TABLE public.organization
  ADD CONSTRAINT organization_pkey1 PRIMARY KEY(id);

ALTER TABLE public.organization
  ADD CONSTRAINT organization_city_id_381976b6f7d84e86_fk_city_id FOREIGN KEY (city_id)
      REFERENCES public.city (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX organization_c7141997
  ON public.organization
  USING btree
  (city_id);

City:

ALTER TABLE public.city
  ADD CONSTRAINT city_pkey1 PRIMARY KEY(id);

Query below takes 6.2 seconds to finish:

SELECT
    org.id
FROM organization AS org
INNER JOIN city ON city.id = org.city_id
WHERE org.id>=1 AND org.id<=3736809
LIMIT 1000 OFFSET 100000;

Explain analyze result:

Limit  (cost=22129.97..22340.23 rows=1000 width=4) (actual time=6268.343..6274.803 rows=1000 loops=1)
  ->  Hash Join  (cost=1104.52..521879.44 rows=2476878 width=4) (actual time=29.056..6259.761 rows=101000 loops=1)
        Hash Cond: (org.city_id = city.id)
        ->  Seq Scan on organization org  (cost=0.00..465045.17 rows=2476878 width=8) (actual time=0.014..6026.781 rows=101000 loops=1)
              Filter: ((id >= 1) AND (id <= 3736809))
        ->  Hash  (cost=694.23..694.23 rows=32823 width=4) (actual time=29.014..29.014 rows=32823 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 1154kB
              ->  Seq Scan on city  (cost=0.00..694.23 rows=32823 width=4) (actual time=0.008..13.538 rows=32823 loops=1)
Total runtime: 6275.272 ms

Is this an expected query performance ?

How to speed query execution ?

p.s. - i include some of my settings below

PG 9.3 on MacOS 10.9.5 (8GB ram)

Postgresql.conf:
default_statistics_target = 50
maintenance_work_mem = 448MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 5632MB
work_mem = 44MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 1792MB
max_connections = 80

You have a lot of results that's why your index is not used. Make your query more restrictive.

It seems that you are using pagination:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page


"Connection to host lost" using telnet to MySQL I am trying to setup MySQL Workbench to manage a remote database and I keep getting "waiting for initial communication packet" error. So I try to telnet to the box on port 3306, I get in, but then I get the following message and the connection gets dropped:

"5.5.13-enterprise-commerical-advanced-log?||Dn}I3aooo;'QC=D!umysql_native_password
Connection to host lost

What can be the issue here?

What you're seeing with the telnet test looks like normal behavior, since mysql doesn't "speak telnet," so the result may not be all that helpful... all it really tells us is that a TCP connection to the remote machine is at least possible.

sqlbot@dev:~$ telnet 10.20.30.40 3306
Trying 10.20.30.40...
Connected to 10.20.30.40.
Escape character is '^]'.
N
5.5.15-log|     \Q_l"`>SÀh4e$~rA/b$'9mysql_native_passwordConnection closed by foreign host.

sqlbot@dev:~$

A better test would be to try the MySQL command line client -- from the same machine where you're getting the error from on workbench.  Whether that succeeds or fails will probably be more informative.

How long does it take for the message to appear?


SQL job using SSIS package takes a long time to run especially in the evenings I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.

Though not really enough info here to give a full answer these steps are how I would approach trouble shooting this...

check the execution logs for the package. Take note of the timings on steps. It should he obvious which takes longest.
execution plans are helpful once you know which to look at. If it's stored procs or a fairly static query the should be in the query plan.
change the package to manually log individual steps and information that you can clearly analyse later it will help identify the slow running points. Add row counts to see if you can quantify if a per row metric to calculate run time. Even run a query to log current connections and transactions to see if it's clashing with other tasks.

This being said 8 hour sync between staging and live is huge unless we're talking about 100s of millions of rows. The run time could suggest missing indexes on staging but the execution plans should tell you that.