Does SQL Server require SSLv3? We have a Windows 2008 R2 server running SQL 2012 Express RTM (11.0.2100.60).

All three protocols (shared memory, named pipes, TCP/IP) are enabled for the server and both 32-bit and 64-bit clients. No certificate is configured, and encryption is not required. SQL Server is configured for mixed-mode authentication.

A few weeks back, we added the registry key to disable SSLv3 for server software, but didn't restart the server. All three TLS protocols (1.0, 1.1 and 1.2) are enabled.

This morning, after installing the critical MS14-066 patch and restarting, SQL Server would not accept any connections. Using shared memory or named pipes returned the dreaded "no process is on the other end of the pipe" error. Using TCP/IP returned a "connection forcibly closed" error.

Uninstalling the patch made no difference - we were still unable to connect to SQL. Only after re-enabling SSLv3 and restarting the server were we able to reconnect. We have since reinstalled the patch, and the problem has not returned.

Therefore, I can only conclude that SQL Server requires SSLv3 to be enabled, even if the connections are not encrypted. However, I can't find this documented anywhere.

Can anyone else confirm this? Is it a known issue?

I can't confirm whether SQL Server needs SSLv3 for secure connections ... but I can tell you for sure that the MS14-066 patch introduced all sorts of problems in https traffic with reports all over the web for browsers that stopped working, SQL Server connections that all of a sudden started using high CPU and so on ... all in all a pretty buggy patch and we're all waiting on Microsoft to come clean and tell us how to safely undo it while still protecting our systems from the mystery bug that the patch supposedly fixed.

For more details see this:

http://odetodata.com/2014/11/microsoft-patch-ms14-066-leads-to-https-problems-with-iis-and-google-chrome/


Poor Performance When Calling Query From Another DB on same Server I have 2 databases - both on the same server. Both databases also have their files on the same drives.

When I run a query on it's local DB, it runs in 3 secs.

However, when I run it from the second DB, it takes about 20mins! The only difference here is the Database name added to the FROM clause.

What can I look for to find why this performs so badly? Would statistics play a part?

The query is a SELECT one on a TVF. Unfortunately I cannot view the TVF as it is 3rd party query and I do not have access.

The issue was the Compatibility Level as suggested by @Kin

One database was 120, the other 100.

I updated to ensure both are 120 and now queries are quick on both databases!

https://docs.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database

Thanks all for the help!


Executing same request from C# VS SSMS give different execution time I have a request like this one

SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
[RevisionNumber],
[CreationDate],
[ModificationDate],
[ProjectDescription],
[IsBsdq],
[ClosingDate],
[ClosingTime],
[ClosingUpdatedOn],
[DeadLineDate],
[IsReceived],
[Inclusion],
[Exclusion],
[Misc],
[Note],
[WorkDeadLines],
[Comments],
[Validity],
[PlansLocation],
[PlansReceivedFrom],
[Price]
FROM [Estimate].[Estimates]
ORDER BY [ClosingDate] ASC, [ClosingTime] ASC

When i run this query in SSMS i get a executing time of 953ms, but when i run this query from a Linq Query in my C# i get a executing time of 1813ms.

The Linq Query use the ".Net SqlClient Data Provider" and is issued against EntityFramework (EDMX file). Is this can be an issue ?

Does anybody knows why i have a big difference between execution times of those requests that are the same but execute from different context against the same database ?

I verified all execution plans of both request and they use the same index to satisfy their respective query.

To see the execution plan of the C# request i use the SQL profiler to trap the Show Plan XML event and i compare it to the one of SSMS and both are the same.

Is this consistent, time after time?

I see a CPU difference which could be compile time. Are there any LINQ settings that affect this?

Edit:

Capture the plans in Profiler
Are you sure the SQL is the same in Profiler?


Which foreign keys do I use if they are repeated I have the following two tables

company_relationships

|ID|Company_ID|Affiliate_ID|<Relationship Info>|
+--+----------+------------+-------------------+

jobs

|ID|Company_ID|<Job Info>|
+--+----------+----------+

and I need to keep a record of job offers (i.e. a company offers a job to its affiliate). Do I use:
job_offers

|ID|Job_ID|CompanyRelationship_ID|<Offer Info>|
+--+------+----------------------+------------+

or

|ID|Job_ID|Affiliate_ID|<Offer Info>|
+--+------+------------+------------+

or something else?

I want to use the first one, because relationship info applies to the job offer, but then Company_ID is specified by both Job_ID and CompanyRelationship_ID. Is there an issue with this / better schema to use?

If you go for the Option 1, how do you Identify the Affiliate?
and that's why I think Option 2 is better, unless I am missing something


How do I show warnings when loading data created from mysqldump? I've got a large .sql file with large insert into ... values ... statements.  Many of these statements are generating warnings during execution.  How can I get mysql to print the warnings?

If it hit control-C then the import stops and I'm dropped back to the OS command line.

Here is sample output from running the SQL:

Query OK, 9827 rows affected, 5403 warnings (0.20 sec)
Records: 9827  Duplicates: 0  Warnings: 5403

Query OK, 9859 rows affected, 5247 warnings (0.20 sec)
Records: 9859  Duplicates: 0  Warnings: 5247

You should startup mysqld with log-warnings (it should be on by default) and also the max error count

[mysqld]
log-warnings
max-error-count=9999999999

You may have to start the mysql client with

SET SQL_WARNINGS = 1;

This is not mysqld setting, it's a mysql client session setting.

You may want to add show-warnings to mysql client session

mysql -u... -p -hlocalhost --show-warnings


Hallengreen's Index maintenance query not using all indexes I'm using Hallengreen's Index maintenance To reorganize/rebuild indexes on my databases.
The problem is, when I execute it:
EXECUTE dbo.IndexOptimize
@Databases = 'MyDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

It only finds 2 or 3 indexes (On Messages Window). And I have a lot of them with more than 90% fragmentation:

Is there some information I'm missing?
Maybe there are not enough pages, so there's no performance impact, and then the script just skips the index ( the first index has 240 pages)?

There is a variable in the script. Something like 'do not rebuild indexes with less then 1000 pages' - this is the default.
Its name @PageCountLevel

You can change it to whatever you wish.


Why is Oracle's dbms_metadata.get_ddl returning different results? I'm trying to compare two schemas using dbms_metadata.get_ddl. The two schemas were created using exactly the same SQL scripts. However on one primary key I'm getting a difference in the DDLs:

ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA_INDEX"  ENABLE

Vs:

  ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "DATA_INDEX"  ENABLE

Both schemas are located on the same database instance. The script for creating this primary key, used for both schemas, is:

ALTER TABLE "AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX  TABLESPACE "DATA_INDEX" ENABLE;

What could cause the difference in result for GET_DDL, when both are created using exactly the same script?

You can control whether or not the storage parameter is included by using DBMS_METADATA.SET_TRANSFORM_PARAM. It's possible that you changed this in one of your sessions.

To suppress the storage parameter, execute the following and then run the GET_DDL again:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

When you're finished, you can set it back to the default by running this:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');


design database for electrical meter data? Hi just wondering how to I have .csv "Time 'datetime' Voltage 'double'"file that record Voltage usage from electrical meter each every minutes and database to record the data just want to normalize the data  should  be in database ? should I have Meters table,Times table, usage or meters table  Voltages table (double, time stamp)

csv file data

 meterID  timeDate         Voltages
    M1    1/12/2014 5:58:00  117.5
    M1    1/12/2014 5:59:00  117.6
    M1    1/12/2014 6:00:00  117.5
    M1    1/12/2014 6:01:00  117.3

The data in your CSV file gives enough information about which meter shows what reading at what time. This table, I believe, should not be decomposed as the information that every row contains is comprehensive in itself. There is no point in creating a Times table here, as there are going to be a lot of times when readings will be taken, hence storing times in a lookup table would be waste of disk space.

If I was developing a real time system for your requirements, the table you have given would have been a part of a larger schema. I would create something like the following:

TABLE customer
( customerID                     INTEGER        PRIMARY KEY
, cust_name                      VARCHAR(100)   NOT NULL
, customer_billing_address       VARCHAR(200)   NOT NULL
, registration_date              DATETIME
, ...
, ... -- Any other columns that you may think appropriate
, ...)

TABLE meter
( meterID                        INTEGER        PRIMARY KEY
, customerID                     INTEGER
, meter_address                  VARCHAR(200)   NOT NULL
, start_date                     DATETIME
, ...
, ... -- Any other columns as per your requirements.
, ...
, FOREIGN KEY (customerID) REFERENCES customer(customerID)
-- ^^ This is because one customer may have multiple meters.
)

Now I will have a meterReading table taken from your question.

TABLE meterReading
( meterReadingID                 INTEGER        PRIMARY KEY
, meterID                        INTEGER        NOT NULL
, readingTime                    DATETIME       NOT NULL
, voltage                        NUMBER(18,2)   NOT NULL
, ... -- any other columns
)

Now if I want to see the total voltage eaten up by a meter in a month, as meter readings are incremental, i.e., they don't reset, I would simply write a query like the following:

SELECT meterID, MAX(voltage) - MIN(voltage) as consumption
  FROM meterReading
 WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                       AND TO_DATE('31-JAN-2014) + 86399/86400;

The last + 86399/86400 was because of the following:

When we use only date values and not time information, most databases treat it as beginning of the day, e.g., 31-JAN-2014 00:00:00.
There are 86400 seconds in a day. So, + 86399/86400 adds one whole day minus one second. So, it is just one second before 01-FEB-2014.

Next, if I want to see the list of customers and how much power they consume in a month, I would simply write the following:

  SELECT c.cust_name, SUM(mr.consumption) AS total_consumption
    FROM customer c INNER JOIN meter m
         ON c.customerID = m.customerID
         INNER JOIN (SELECT meterID
                              , MAX(voltage) - MIN(voltage) as consumption
                       FROM meterReading
                      WHERE readingTime BETWEEN TO_DATE('01-JAN-2014)
                                            AND TO_DATE('31-JAN-2014)
                                                + 86399/86400) mr
         ON mr.meterID = m.meterID
GROUP BY c.cust_name
ORDER BY SUM(mr.consumption);


MySQL select complementary pairs on particular values I have the following table (MySQL):

I need to select the complementary pairs of accessions having different properties grouped by folder. In other words I do not want to group accessions having same property values for one folder.

As a final result I need to get:

I came up with something like that, but I am getting doubled results (strait and reversed pairs).

select    distinct a.accession, a.folder, b.accession, b.folder
from      main a
     join main b on a.accession!= b.accession AND a.folder= b.folder
where    a.property not in (select c.property from main c where c.accession = b.accession)
and      b.property not in (select c.property from main c where c.accession = a.accession)
group by a.accession, a.folder, b.accession;

Try changing your JOIN statement as follows:

     join main b on a.accession < b.accession AND a.folder= b.folder

This should include a.accession = "Z1" and b.accession = "Z2", but exclude a.accession = "Z2" and b.accession = "Z1".


Index Optimize cause DB size increasement After Index Optimize the database size increased from 114 to 140Gb, is this normal?

Server RAM usage do not reduced after the process finishing, and stay at 62Gb/98%

Yes that is normal.

See: Disk Space Requirements for Index DDL Operations

  When a new index structure is created, disk space for both the old
  (source) and new (target) structures is required in their appropriate
  files and filegroups. The old structure is not deallocated until the
  index creation transaction commits.

  The following index DDL operations create new index structures and
  require additional disk space:

  ALTER INDEX REBUILD

So if one of your indexes was rebuilt by the script, the size needed for that operation was the size of the old index structure + the size of the new index structure for the time the operation was running.

Once the new structure is in place the old structure is removed and free space remains inside the data file, but it is not returned to the operating system so your data file ends up larger but should include some free space that will be reused in the future.

Also take a look at Index Disk Space Example

  Total disk space required to support both the source and target
  structures for the duration of the index operation is 816 MB (363 +
  453). The space currently allocated to the source structures will be
  deallocated after the index operation is committed.


Securely remove columns (and traces thereof) from a SQL database I've come upon a database that has previously held sensitive data (think "passwords/credit-card-numbers" kind of sensitive) in plain text. All of the sensitive data in question has been deleted, but I'm concerned that if accessed by someone with malicious intentions, the data might be recoverable through backups and/or transaction logs.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?

Well there is always a chance that someone can find the bits at various hardware layers if they had access to the physical disk(s), but I think you can be fairly confident you have removed traces of the data accessible through less nefarious means by:

rebuilding the table where the column was removed (perhaps perform a select into, drop the old table, rename the new one, and re-create any indexes)
starting the log chain over (a new full backup)
forcing the log to wrap around and re-zero-initialize by backing the log up twice and then performing a slight shrinkfile
eradicating any old full/diff/log backups (you may want to wait on this step until your data recovery window has passed, e.g. if you might still need to recover back to last Monday, wait until two Mondays from now - in the meantime moving them to a more secure location). At some point you know that a backup from two weeks ago is absolutely useless, because you're not going to restore to a point in time that far back, so why keep it around?


How does SQL Server know what index to use? I have this non clustered index

CREATE NONCLUSTERED INDEX [nci_wi_StoreVariantInventory_F09B4BE817BC953FAC6861B7E46F6F51] ON [dbo].[StoreVariantInventory]
(
    [ProductId] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)
INCLUDE (   [LocalInventory]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

And this clustered index

ALTER TABLE [dbo].[StoreVariantInventory] ADD  CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC,
    [ChainId] ASC,
    [CountryCode] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Generated for this table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StoreVariantInventory](
    [ProductId] [bigint] NOT NULL,
    [ChainId] [nvarchar](5) NOT NULL,
    [CountryCode] [nvarchar](2) NOT NULL,
    [StoreId] [bigint] NOT NULL,
    [VariantId] [int] NOT NULL,
    [Size] [int] NOT NULL,
    [SizeRow] [nvarchar](1) NOT NULL,
    [KoncernNumber] [nvarchar](4) NOT NULL,
    [LocalInventory] [int] NOT NULL,
    [OrderedInventory] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC,
    [ChainId] ASC,
    [CountryCode] ASC,
    [StoreId] ASC,
    [VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StoreVariantInventory]  WITH CHECK ADD  CONSTRAINT [FK_StoreVariantInventory_Stores] FOREIGN KEY([StoreId])
REFERENCES [dbo].[Stores] ([StoreId])
GO

ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Stores]
GO

ALTER TABLE [dbo].[StoreVariantInventory]  WITH CHECK ADD  CONSTRAINT [FK_StoreVariantInventory_Variants] FOREIGN KEY([ProductId], [VariantId])
REFERENCES [dbo].[Variants] ([ProductId], [VariantId])
GO

ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Variants]
GO

If I do a simple select

select LocalInventory
from StoreVariantInventory
where ProductId=1219100110
and VariantId = 49
and StoreId = 9050
and ChainId = 'F'

It looks through the non clustered index

But why does it pick the non clustered index? I am doing a WHERE that doesn't fit the non clustered index 100% so how can it use it? Does SQL Server just picks what it seems a best fit, when all columns in the WHERE clause doesn't fit an index 100%? Is it bad practice not having an index like here not fitting 100% with my where clause?

Answer originally left as a comment by i-one:

Your query references ProductId, VariantId, StoreId, ChainId and LocalInventory columns.

All of them except ChainId are declared in the non-clustered index definition.

ChainId is included in the non-clustered index implicitly as part of the clustered index key.

So, the non-clustered index is covering for this query.

The nonclustered index contains the clustered index keys because SQL Server must be able to locate the base table row from the index entry.

From the SQL Server Index Architecture and Design Guide:

  If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.


Does it ever make sense to create an index with additional columns after the primary key? I am looking through our MySQL 5.7.16 database and I noticed that there are some indexes that use the primary key as the first column in the index, followed by additional columns.  Intuitively, this doesn't seem to make sense to me since the index would always narrow it down to one row by using the first column.  However, I am not sure if there are any special circumstances in which it makes sense to have this kind of index.  Is there any situation where this would improve performance?

Indexes have more than one use. The primary use (in most cases) is to quickly identify the rows that meet a query's conditions. When doing this, the leftmost columns of the index are considered.

Say you have a table, users, with 100 columns, a mix of dates, strings, and numbers. Five of the columns in the table are: id, username, country, acct_expir_date, and access_level (an int). id is the primary key, both it and username are unique.

Let's say there's a (non-clustered) index on country and username. The query:

SELECT `username`, `acct_expir_date` FROM `users` WHERE country = 'UNITED STATES';

could use this index to identify all users from that country, locate their records in the actual table, and return the requested data.

However, the query:

SELECT `username`, `country` FROM `users` WHERE `username` LIKE 'g%';

would not be able to use the index - the leftmost column in the index, country, is not a part of the WHERE clause.

That said, indexes do have a second use. If all the columns in a query exist in an index, then the query can ignore the actual table, and use the index as if it were the table itself. this is called a covering index.

Let's assume we now have another non-clustered index on our table, on the columns acct_expir_date, username, id, and access_level, in that order. And, we have the query:

SELECT `username`, `access_level`, `acct_expir_date`
  FROM `users`
 WHERE `acct_expir_date` >= '2017-07-01'
   AND `acct_expir_date` < '2017-08-01'
;

This query would probably use our index even if the SELECT column list was * (all columns); however, since our index contains all the columns in the query, the users table itself will not be touched - the query will simply use the information in the index to provide the requested data.

So, in your case, the columns in the index up to the primary key are there to be used for the index's first use - locating records. The remaining columns may be there for the second use; providing a covering index.

NOTE: as stated by Balazs Papp in the comments, columns to the right of the primary key in the index can be used to help identify specific rows in a query where a range of primary key values are of interest; however, each individual row in the index would have to be checked, since the rows cannot be sorted on anything to the right of the primary key (since it's unique). Since the index is already in memory, narrowing down the actual selection of rows further via the index would indeed probably be faster than loading the full records and then narrowing things down; however, if the extra columns are going to be used in searches regularly, they might be more useful to the left of any unique columns, where they can narrow things down more quickly.

UPDATE: tables that can be loaded into memory in their entirety don't generally need covering indexes. If your table is not all that large, then any performance gain from the indexes may be lost in maintaining them and storing them. Consider the size of the table overall, the size of the rows of the table vs. the rows of the index, and how often queries are performed that might use the index, vs. how often the data in those columns is added or updated. If the indexes seem like they might be causing more harm than good, you may want to consider dropping them. However, do so with great care; if there is some critical query they're boosting performance on, removing them could cause significant issues. I would remove no more than one per month or so; over the course of a month of normal business, whatever use the index might have should be encountered. And, only do so with the knowledge of key people in your department; not just your boss, but someone who deals with reports of performance issues, so any problems that may arise can be resolved. Also, keep in mind how long it would take to recreate the index; there may actually be cases where creating the index, using it for one monthly report, then dropping it would still give a performance benefit, without the maintenance and (long-term) storage costs.

UPDATE 2: For the sake of (improved) completeness, as joanolo mentions in the comments, indexes also have a third general use - they do sort the values in indexes columns by those columns. If that sort matches your ORDER BY clause, the engine can avoid having to sort the records by retrieving them in the necessary order to begin with. This isn't particularly relevant to the OP's question, since a unique column means that any values to the right in the index aren't being sorted (because the position of the rows is fixed once the index reaches a unique column), but it is true.


mysql starts and stops servers automatically non stop I am working on macOS High Sierra.

Basically, the problem is that for some reason the mysql server is starting and stopping automatically, and constantly. It does not stop. If I run:

# ps -ax | grep mysql

to see the processes even when I (think) I have uninstalled mysql, it shows:

54948 ttys002    0:00.00 grep mysql

I run the same thing 5 seconds later, and it has a different ID:

54950 ttys002    0:00.00 grep mysql

I cannot sudo kill -9 54950 because by the time I try to kill it, it has already disappeared.

I have tried removing everything related to mysql. I then reinstall it, and the same thing happens. I have tried doing this with homebrew as well.

Please help.

Of course you will see your terminal running the grep command .. that's pretty much to be expected. Please learn some console basics (either mac os or free bsd related  as that is the underlaying system that mac os builds on )

If ps -> grep really only shows its own instance then there is nothing else with "mysql" in its process name.
Which pretty much explains that you cant kill the process afterwards - because the console-command did its work and then the process ended regularly. Hence your second attempt "generated" a new process ID (new process) ...

At the time of your ps -> grep NO "other" mysql process is running.


Postgres and Windows AD using windows authentication I am working with SQL SERVER 2012 and postgres 9.4.x version on windows OS. The service-x on this machine is running under user-1 and user-1 is member of AD group ADG1.

during the installation of our product one of the step is to create the required login ADG1 on sql server and postgres server. this is happening fine.

one of the operation this service does is create new database in both SQL SERVER and corresponding in Postgres with same name upon user's single request.

During database creation, service is making connection to both data bases with windows authentication. So the AD Group login ADG1 is present in SQL SERVER and user-1 under which account this service is running is member of this AD group. So it has appropriate permission. Database creation is happening fine on SQL SERVER. though I do not know how the SQL SERVER resolves AD Group authentication.

My main issue is, service is not able to connect to postgres using windows authentication. I have set the SSPI authentication method in pg_hba.config file and hence the service is trying find login "user-1" which doe snot exist on postgres server.

So my question is how Do I tell postgres in pg_hba config file to search this user name in AD groups and use that GroupName instead the username.

please let me know if any specific information you may need to understand the situation.

Thanks

Update:1

I find out this post which is very old and not sure if since then postgre has not changed this behavior but basically under this post point number (4) is indicating that even thought postgres support windows authentication using SSPI authentication method, it does not support AD Groups Directly. We still have to create those individual user in that AD Group in postgres as login.

BIGGEST OBSTACLE TO POSTGRESQL ADOPTION

First you have to note that PostgreSQL doesn't really have a concept of anonymous usage.  So you do not have a way to log in without the user being added.

You have two options. The first is you can add appropriate ad users and groups manually.  Or you can add them using an AD trigger (see https://superuser.com/questions/909554/trigger-an-event-when-computer-is-added-to-active-directory for a similar question, and full documentation at https://technet.microsoft.com/en-us/library/dd277403.aspx).  Please note, the latter is a bit of a security concern and you need to make sure you aren't giving out access beyond what you want to give).

If you go the latter direction, you need to make sure you are restricting access to the public role.


Can't stop MongoDB process on CentOS My OS is CentOS release 6.4 (Final) and my MongoDB version is 2.4.1.

I tried:

use admin;
db.shutdownServer()

and I got in output:

assert failed : unexpected error: "shutdownServer failed: exception: dbexit timed out getting lock"
Error: Printing Stack Trace
    at printStackTrace (src/mongo/shell/utils.js:37:7)
    at doassert (src/mongo/shell/assert.js:6:1)
    at assert (src/mongo/shell/assert.js:14:1)
    at DB.shutdownServer (src/mongo/shell/db.js:342:1)
    at (shell):1:4
Thu Apr  7 11:42:52.577 JavaScript execution failed: assert failed : unexpected error: "shutdownServer failed: exception: dbexit timed out getting lock" at src/mongo/shell/assert.js:L7

Then I tried to force shutdown, but the output is the same. Can anyone help me?

do a command like this

ps -ef | grep "MONGO"

You will see output like

502  8066     1   0 12:23pm ??        12:25.00  /Applications/PyCharm.app/Contents/MONGO/pycharm

Now before you do the next command understand that killing a process when it may or may not be doing something is a VERY VERY DANGEROUS habit.

So if this corrupts your MongoDB Database - it is because you did this command.....

kill -9 8066

8066 is the 2nd number you can see from the ps command.

You can tell if the process is consuming cpu/disk using other systems tools such as

top
sar
instat

If these processes look like the MongoDB Process is doing some work - then the chances of something bad usually increases.

Good Luck !!

Thought: Ask the server to restart ??


Comparing Query Performance Across DBMSs I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.
I have been looking into command line tools for each system to get the query execution time.

For SQLite I used the .timer ON command to show the real/user/sys time after each query
For MariaDB I set the profiling and called "show profiling" to get the duration of each query
For PostgreSQL I executed the query with "analyze" to show the full query plan including execution time.

My problem is that I wasn't able to find documentation for these tools that specified what the time actually is: CPU vs actual time etc, and some of the values are very different. For example, a join query that I tried with MariaDB took 33 mins but with postgreSQL it only took a few seconds.
Does anyone have insight into how these metrics are actually measuring query time, and if they can accurately be compared with each other? Alternatively, is there a better tool I should be using for this task? Any help is appreciated.

I think that you shouldn't rely on any of that. The only real measure is how long the query actually took, when measured from the client side. That also avoids overhead during query execution that you get if you for example use EXPLAIN (ANALYZE) in PostgreSQL.
So I would do it like this:

measure the current time on the client

send a query to the server

wait until execution is complete and the client has got the result

measure the time again and calculate the difference


Two Oracle ORDS on one web server accessing one database Oracle ORDS 3.0
Oracle 12c database
Red Hat Linux 6.8
Tomcat 8.0.22

Can two ORDS be deployed to one web server (Tomcat) and access the same database?
If so, how is this accomplished?

That is, I would be deploying to Tomcat an ords1.war file and an ords2.war file that both connect to the same Oracle database.

Better yet, I need two URLs that point to two different applications running on the same database.  Perhaps another ORDS is not needed.

The context root (normally just 'ords') has to be different for each application.
For example, ords1 and ords2.  Hence, the thought that there needs to be a ords1.war and ords2.war file deployed.

This can be done.

Make a copy of the existing ords.war (call it, say, ords2.war) and just follow the same process that you would when creating your initial ORDS .war file.
Just be sure to use different configuration directories for each ORDS .war file in order to avoid possible collisions.

You name the two ORDS .war file differently in order to change the 'root'.  For example:

https://mywebsite.com/ords/...
https://mywebsite.com/ords2/...

has the 'root' of ords and ords2, respectively


Applying single quotes across multiple lines using ssms code snippet further to Applying Quotes Across Multiple Lines

Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could:
select the list of values > right click > surround with > select the name of my code snippet in order to turn this:

SELECT * FROM tbl WHERE someValue
ABC1
ABC2
ABC3
ABC4

into this:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)

Yes. You can write your own snippets code in a .snippet file and then import it into SSMS.
To import the folder containing the .snippet file:

Open SSMS
Click on Tools ->Code Snippets Manager
Click on Add and select the folder where your custom snippets are written.

I believe this answers a part of your question. You can further follow the below links to get more details.

Adding Custom Snippets: http://www.sqlservercentral.com/articles/SSMS/138994/

About Snippets: https://www.mssqltips.com/sqlservertip/2411/getting-started-with-code-snippets-feature-of-sql-server-2012/


Should Service Broker use SQL or Windows Authentication? I was curious what the best practice would be, for properly setting up Service Broker in SQL Server 2008. Should I use a SQL authenticated user, or should I use a Windows NT Authenticated user based off AD or LDAP server?

Service Broker relies on certificates which is shared between databases. No other info for the user is shared.
Service Broker provides 2 distinct types of security -

Dialog security — Encrypts messages in an individual dialog conversation and verifies the identities of participants in the dialog. Dialog security also provides remote authorization and message integrity checking. Dialog security establishes authenticated and encrypted communication between two services.
Transport security — Prevents unauthorized databases from sending Service Broker messages to databases in the local instance. Transport security establishes an authenticated network connection between two databases.

Its imperative to understand difference between authentication and encryption. Authentication will let the service identify the identity of the user trying to connect to the service whereas encryption will ensure that the messages sent over network are encrypted during transmission.
Service broker supports :

Windows Authentication : Depending on how your network is configured, it will use Kerberos or NTLM protocols to authenticate Service Broker Endpoints when endpoints are in the same windows domain or between trusted domains.

Certificate Based Authentication : This authentication is used when the servers involved are members of unrelated domains and the default windows authentication is not possible.
Refer to : How does Certificate based Authentication work ? and Remus's answer here.

So, depending on how your servers are configured, you can choose Windows Authentication (default) or Cert based authentication.
BOL has a good article on Identity and Access Control (Service Broker)


MS SQL Server cycle calculation Dears, I have an problems with a calculation of this formula:

Column "A" has always the same value for a group of SubIDs.
In the "Result" column I need formula (function) like in the example columns "Result Formula Description".
I tried many approaches but each of them help me with a half of the problem.
Please, help me!


SQL Server Reporting Services: How to check who accessed certain report? I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created.  Is there a way to find out who has accessed certain reports during past month?

My goal here is to remove people who hasn't accessed the report for awhile.

Suggest you not to remove people but disable their login or just revoke access.

Then after a full business cycle, if no one complains then remove those users.

  Is there a way to find out who has accessed certain reports during past month?

Below is the query that will give the report you need. There are more report queries available at this link :

Note: Adjust the query as per your need.

 – Execution Log:

– For debugging (remove next 4 lines after pasting to dataset) –

DECLARE @DateFrom Date

DECLARE @DateTo Date

SET @DateFrom = ’2008-01-01′

SET @DateTo = ’2008-06-30′

– End debugging script –

SELECT

      DATEPART(Hour, TimeStart) AS ReportYear

    , DATEPART(Month, TimeStart) AS ReportMonth

    , DATEPART(Day, TimeStart) AS ReportDay

    , DATEPART(Hour, TimeStart) AS ReportHour

    , Type

    , COUNT(Name) AS ExecutionCount

    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum

    , SUM(TimeProcessing) AS TimeProcessingSum

    , SUM(TimeRendering) AS TimeRenderingSum

    , SUM(ByteCount) AS ByteCountSum

    , SUM([RowCount]) AS RowCountSum

FROM

(

    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,

  TimeProcessing, TimeRendering, ByteCount, [RowCount]

    FROM

    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =

       ExecutionLog.ReportID LEFT OUTER JOIN

    Users ON Catalog.CreatedByID = Users.UserID

    WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo

) AS RE

GROUP BY

      DATEPART(Hour, TimeStart)

    , DATEPART(Month, TimeStart)

    , DATEPART(Day, TimeStart)

    , DATEPART(Hour, TimeStart)

    , Type

ORDER BY

      ReportYear

    , ReportMonth

    , ReportDay

    , ReportHour

    , Type


How Does Table Partitioning Help? I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will hold 180-260 million records. As it will be properly indexed table, so I am thinking of limiting the table records to 20 million this way I would have to create 9-13 tables.

But I am not quite sure about how it will improve the performance because they will be sitting on same machine (32GB RAM)?

I am using MySQL and tables would be MyISAM and big table would have index on id field and there are not further complexities like full text search etc.

Please also shed light on table partitioning vs database partitioning.

The following is just insane ranting and raving...
If you leave all data in one table (no partitioning), you will have O(log n) search times using a key. Let's take the worst index in the world, the binary tree. Each tree node has exactly one key. A perfectly balanced binary tree with 268,435,455 (2^28 - 1) tree nodes would be a height of 28. If you split up this binary tree into 16 separate trees, you get 16 binary trees each with 16,777,215 (2^24 - 1) tree nodes for a height of 24. The search path is reduced by 4 nodes, a 14.2857 % height reduction. If the search time is in microseconds, a 14.2857 % reduction in search time is nil-to-negligible.
Now in the real world, a BTREE index would have treenodes with multiple keys. Each BTREE search would perform binary searching within the page with a possible descent into another page. For example, if each BTREE page contained 1024 keys, a tree height of 3 or 4 would be the norm, a short tree height indeed.
Notice that a partitioning of a table does not reduce the height of the BTREE which is already small. Given a partitioning of 260 milliion rows, there is even the strong likelihood of having multiple BTREEs with the same height. Searching for a key may pass through all root BTREE pages every time. Only one will fulfill the path of the needed search range.
Now expand on this. All the partitions exist on the same machine. If you do not have separate disks for each partition, you will have disk I/O and spindle rotations as an automatic bottleneck outside of partition search performance.
In this case, partitioning by database does not buy you anything either if id is the only search key being utitlized.
Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.
The overall impact should be to have at least two partitions: One partition for frequently accessed ids, and the other partition for the rest of the ids. If the number of frequently accessed ids is fairly large, you could optionally partition that.


Transaction not rolling back in MySQL Consider the following setup, which you can tinker with at dbfiddle.uk:
In the first batch we setup three simple tables, and add a row to each table:
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Next, we'll create three stored procedures:
DELIMITER //

CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
///

Next we'll start a transaction, run the three procs, then rollback the transaction.  Note we're disabling autocommit explicitly even though that is not strictly required since we have an explicit transaction:
SET autocommit = 0;
START TRANSACTION
CALL proc1();
CALL proc2();
CALL proc3();
ROLLBACK

Now, if we inspect the content of the three tables, we note that the rollback appears to have not taken place.  In fact, the rows are exactly as if there was no transaction, i.e. it looks like autocommit was on.
SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The results:
╔═══╗
║ x ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝
What is happening here, and how can I rollback the actions of the first two procs when the third clearly fails?
FYI, this has been tested on MySQL 5.7.2, and via DBFiddle.uk on MySQL 8.0.22.

It appears MySQL is silently failing at the call to proc3(), and therefore never running the rollback statement.
This is how you should construct the code to actually perform the rollback, passing some details back to the caller.  You can test this code at dbfiddle.uk:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Creating the original three procs:
DROP PROCEDURE IF EXISTS proc1;
DROP PROCEDURE IF EXISTS proc2;
DROP PROCEDURE IF EXISTS proc3;

DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
//
DELIMITER ;

Create a new proc to call the three procs above:
DELIMITER //

DROP PROCEDURE IF EXISTS call_procs;

CREATE PROCEDURE call_procs()
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        ROLLBACK;
        SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
        SELECT @full_error;
    END;
    SET autocommit = 0;
    START TRANSACTION;
    CALL proc1();
    CALL proc2();
    CALL proc3();
END
//
DELIMITER ;

Call the new proc, and check the results:
CALL call_procs();

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The output now appears to be what you'd expect:
╔═══╗
║ x ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝
And a nice little bonus, you get the following output alerting you to an issue:
╔═══════════════════════════════════╗
║            @full_error            ║
╠═══════════════════════════════════╣
║ ERROR 1365 (22012): Division by 0 ║
╚═══════════════════════════════════╝


I lost my database in SQL server 2008 R2 My database suddenly stopped working and from checking the error logs it says that the master database was corrupted.

So I tried rebuilding the system database with the command:

setup /ACTION=REBUILDDATABASE   /QUIET
      /INSTANCENAME=<instance name>  /SQLSYSADMINACCOUNTS=<accounts>

After running the command, the database server has started working again, but all my database are nowhere to be found. I really need some ideas as to I have no backup databases.

I have the idea that the database files (mdf, ldf, ndf) are still on my local drive so is it possible to relink those files to a database?

update

I tried creating a database with the same name on the instance and got the error

Create failed for Database 'db'.  (Microsoft.SqlServer.Smo)

Cannot create file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.db\MSSQL\DATA\db.mdf' because it already exists. Change the file path or the file name, and retry the operation.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170)

Which proves my idea that the files are still there. So how do I rebuild/recover this to my database?

You can try attaching the missing databases using the files. You can right click on the databases folder in Management Studio and select "Attach".

Another option, and the one I would generally go for, would be to use any backups you have. You will experience some data loss though.  If you are using the full recovery model for your databases, you should be able to restore to the last transaction log backup. Otherwise if you are using the simple recovery model you can only restore from the latest full backup.


SQL Server using Parallel Plan for Cost less than 5 I have a pivot query that uses a parallel plan even though the cost is less than 5.

The Cost Threshold for Parallelism is set to default of 5, and MAXDOP is 0.

SELECT Column1, Column2 Column3, Column4 AS Column5, Column6 Column7, Column8 Column9, Column10 Column11
        FROM (
              SELECT Column12,
                        Column13,
                        Column1
                FROM Database1.Schema1.Object1
             ) Object2 PIVOT( SUM(Column13) FOR Column12 IN (Column2, Column4, Column6, Column8, Column10) ) AS pvtScore;

Following screenshot shows the estimated subtree cost as 1.39.

I am wondering why the parallel plan was used when the cost threshold is at 5 for parallelism to kick in.

Version: SQL Server 2008 R2

This can be confusing when you're first looking at parallel plans. The thing to keep in mind is that the cost based optimizer picks what it estimates will be the cheaper query to execute. It's not always right, but hey, it's not rocket science.

As a simple example, here are two count queries. One is allowed to go parallel, the other is forced to run serially.

SELECT COUNT(*)
FROM dbo.Posts AS p;

SELECT COUNT(*)
FROM dbo.Posts AS p
OPTION(MAXDOP 1);

Their query plans have the information we need:

The serial plan costs 121 query bucks, and the parallel plan costs 69.


How to recover a database from a failed Availability Group? We are currently testing a SQL Server 2012 AlwaysOn HA implementation where we have 2 out of the 3 nodes are synchronous.  The third, asynchronous node, does not have a vote in the quorum.  There is a scenario in which the first two nodes experience complete failure.  When this happens, the third node is up and running but it has it's database in the AG marked as "Recovery Pending".  How can I get this database out of this state and make it available?

The first attempt would be to try to get either the primary replica back online, or to get the synchronized secondary replica online to ensure that there is no data loss.  But provided this is a true disaster recovery scenario (as your original question implies), then it would seem as though your actual cluster (WSFC) has failed.  With the database entering the recovery_pending state, you can't unjoin the database to recover it.  Likewise, you would need the cluster in order to do a failover.  So the following assumes that you can't force quorum on either of the sync replicas, and you need to failover to the async.

The next step at this point to recover to the async secondary that is currently online, would be to force quorum (WSFC) and then perform a forced failover (with the potential for data loss).

For more information, I highly recommend you read up on BOL regarding the topic Perform a Forced Manual Failover of an Availability Group


In a multi-database AG, what decides which databases will have more than 1 redo thread? We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet.
When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases with those threads to be the busy ones.  During a recent migration, we decided to be careful of the order we restored the dbs in, as it was our understanding that this was decided based on databases creation date.  However, checking post migration, this isn't true.
SELECT databases.database_id,
       databases.create_date,
       dm_hadr_db_threads.name,
       dm_hadr_db_threads.num_redo_threads,
       dm_hadr_db_threads.num_parallel_redo_threads
  FROM sys.dm_hadr_db_threads
 INNER JOIN sys.databases ON dm_hadr_db_threads.name = databases.name
 ORDER BY
       dm_hadr_db_threads.num_redo_threads DESC
OPTION (RECOMPILE)

It isn't create date or database id.  It's not alphabetical.  It isn't order added to the ag. Does anyone know what decides this?
This is SQL server 2019; the query is from the secondary.
Here is where we found it was based on database_id: https://www.brentozar.com/archive/2018/06/first-responder-kit-release-just-when-you-think-theres-nothing-new-left-to-do/

Parallel redo threads are assigned in database recovery order, which does follow the sys.databases creation date.
That said, the separate parallel database recovery feature means each database recovery task can be assigned to a different SOS scheduler (when there are a large enough number of schedulers available).
Say you had 8 databases and 32 processors. The 8 separate recovery tasks would likely be assigned to 8 different schedulers (the SOS abstraction of a CPU). Creation of the 8 tasks (in create date order) and scheduler assignments can happen very quickly.
How soon (and in which order) each scheduler starts executing its assigned recovery task depends on what other work each scheduler has at the time (its runnable queue), and how far through the current quantum any other currently-active task is.
Parallel redo threads are assigned (up to the global limit) soon after each independent recovery task starts executing on its assigned scheduler. This is non-deterministic due to the issue described just above.
Microsoft support have some undocumented trace flags that can help promote good distribution of parallel redo threads in complex scenarios. You should contact them about your situation.


How to schedule function in Oracle? I want to schedule function in Oracle.

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'aud_clear_fun',
                            start_date      => sysdate,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            end_date        => null,
                            enabled         => true,
                            comments        => 'Created By: MK; Truncates aud$ table');
end;

I have tried to indicate several other values for job_action, for example:

declare
my_var number;
begin
select aud_clear_fun into my_var from dual;
end;

But doesn't work. Can you provide me with the right syntax of scheduling function?

Thank you.

--aud_clear_fun

create or replace function aud_clear_fun
return number is
begin
   delete from a;
   return 0;
end;

--Job

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'begin
                                                 mari_dba.aud_clear_fun();
                                                end;',
                            start_date      => sysdate,
                            end_date        => NULL,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            enabled         => true);
end;

--Execution

begin
  dbms_scheduler.run_job('aud_clear');
end;

--Error

ORA-06550: line 2, column 50:
PLS-00221: 'AUD_CLEAR_FUN' is not a procedure or is undefined
ORA-06550: line 2, column 50:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

View program sources of error stack?

The error message is very clear, you need to specify a procedure or a valid pl/sql block.
If you really want to truncate the aud$ table from a piece of pl/sql, you will need to use dynamic SQL.

begin
execute immediate 'truncate table sys.aud$';
end;
/

put the code in the action or in the procedure definition of aud_clear_fun, that really needs to be a procedure to work with Oracle Scheduler. The Scheduler is not going to read the returned value ...


Using coalesce on an index column Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way?
I think that it should but in this case how should I convert my where clause?

If you afraid that the condition will keep the optimizer from using an index on the columns, you are right, the use of functions in most DBMS has this effect. There are some exceptions to the rule but not in MySQL.

If you want indexes to be considered (and possibly used), you have to conevrt the condition to one without functions applied to columns.

Your condition, (if table2.other_date_column is not nullable), is equivalent to:

 (  table1.indexed_column > table2.other_date_column
 OR table1.indexed_column IS NULL )


How to design a Table in which its fields varies on its categories? I am trying to design a database to post free ads. Eg http://www.gumtree.com.au/p-post-ad.html

Scenario: Every ad will have different categories and some categories will have further sub categories. SO the table AD will have different fields depending on its category. For example, an ad for used car will have MAKE, MANUFACTURER etc but an ad for books will have Book title field, Author field etc.

SO how do I design the table for ads?

I can possibly think of two solutions. One, to create subtypes for categories in table ad. Two, to create individual table for each categories of ad (this does not sound like a good design).

I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows.

The usual technique for this sort of problem is "table inheritance" (so search that term for more examples, whereby you have one core table that contains all the common properties (in this case things like date, caption, description and owner), a column to indicate the type of advert and an extra table for each type. So for a car the ad_type column would contain "car" and the details would be found ad_detail_car table. Something like:

ad_common          ad_detail_car
===========        =============
advert_id   -----> advert_id
advert_type   |    make
caption       |    manufacturer
description   |
owner         |    ad_detail_book
price         |    ==============
              |--> advert_id
              |    isbn
              |    binding
              |
              |    ad_detail_kitten
              |    ================
              `--> advert_id
                   breed
                   age

advert_id would be the PK for the common table and both the PK and an FK to the common table in the detail tables.

As the number of advert types grows having a separate table for each might become a mess so you might instead consider a property bag like so:

ad_common          ad_extra_properties
===========        ===================
advert_id   -----> advert_id
advert_type        property_name
caption            property_type
description        property_value
owner
price

Here the PK for the property bag is advert_id,property_name. Be warned though, this is a pattern (called the EAV or "Entity Attribute Value" pattern) that has its own set of problems, particularly for reporting, so read around that before considering if it is suitable for your needs.

Another option if going for the EAV model is to do away with the detail or property bag tables completely and just store the extra properties in a format useful for the business logic (JSON, XML) in a column of the main table. This is simpler but means (unless your chosen DB natively supports such documents) you can't report on or filter/sort by the extra properties at all directly in the database.


How to:: Master-Master replication I have setup Master-Master replication on two servers. Now, my question is that if I need to have the second master as only read-only, what should I do? I tried doing

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

But since this creates a read-lock in my 2nd master (Slave 1). The changes that I make in Master 1 is not propagated to Master 2 (Read-only).

What should I do such that - changes in Master 1 are getting updated to Master 2 but Master 2 should not be in write mode, Master-master(Active/Passive).
Is it possible to do this?

Regds

1 Stop the slave on the first master

mysql> STOP SLAVE;

If the second master becomes a read only then the first master should no longer replicate from it.

2 Change the 2nd master's configuration to read only.

mysql> SET GLOBAL READ_ONLY=1;

Add read_only=1 to my.cnf for the next restart

Read Only variable

3 Unlock the tables on the second master

UNLOCK TABLES;

You don't need to lock tables on the second master (now slave)

4 Secure privileges

Redefine privileges on the 2nd master, follow this answer here : Should a MySQL replication slave be set to read only?

5 Verify slave status on 2nd Master, now slave.

mysql> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52

If the second master is not replicating then you may have to reinitialize the replication to the first master.

A. On first master, identify current log and position

SHOW MASTER STATUS;

+------------------+----------+--------------------------+------------------+
| File             | Position | Binlog_Do_DB             | Binlog_Ignore_DB |
+------------------+----------+--------------------------+------------------+
| mysql-bin.000001 |      107 | base1, base2             |                  |
+------------------+----------+--------------------------+------------------+
1 row in set (0.00 sec)

B. On second master now slave, reinitalize replication

Stop SLAVE ;
CHANGE MASTER TO MASTER_HOST = '10.11.128.52',
MASTER_USER = 'repli', MASTER_PASSWORD = 'repli',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
Start SLAVE;

C. Show status on second master(now slave)

mysql> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52


Oracle Benchmarking (SQL_NO_CACHE ...) I want to benchmark some queries on an oracle System from a JDBC application.

However, the database seems to cache at some point in time. However, this is not intended in this case.

Is there anything like the MySQL query hint SQL_NO_CACHE.

There are Oracle query hintes /*+ FULL(test) */ and there is even the CACHE hint, but they concern whether the query results goes to the cache at all ...

What can I do on an Oracle System to get rid of any caching?

Using an account that has the ALTER SYSTEM privilege:

alter system flush shared_pool;

followed by:

alter system flush buffer_cache;


How to store chains of information? I am currently trying to design a table structure that can capture report data (from various sources) in an ordered fashion.

Let me give you an example:

Each day I receive three separate reports from various companies (one report per company). These reports are a dump of their database tables and include PK, FK to previous report row (of another company), Amount, CustomerID, Timestamp and some other unrelated fields.

The order of the reports is very important. The three reports create a chain that I am interested in (query later). Each record only knows about its immediate predecessor (FK to previous report row) and is NULL if it is the root record.

The problem I am running into is the fact that for certain chains, the length of the chain can be 5 reports, others are 3. I'm wondering if it is possible to design a Chain(?) definition table somehow and reference a chain id somewhere in the record so that the record knows which part of the chain it belongs to.

The previous developer created a table per report and had humongous amounts of business logic to tie report chains together. I would like to abstract this design so that the tables are agnostic to the reports and have one to four tables instead of fourty-seven.

Once everything is stored, I would like to query a specific master record and see all children records and their amounts.

I read about self referencing tables, but I am not quite sure if this is the right way to go.

SQL: SQL Server 2008R2

From what you've described a self referencing table is the way to go.  This only works if you can get all the data into a single schema (which you probably can).  It'll make the management a piece of cake and you can have changes on infinite length at this point.


Optimizing query with type casting I had a query that was taking way too much time despite having all the indexes I could think of.

Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to another data type because the column from table A is of type varchar while the matching column of table B is of type integer.

I changed my code to use a temporary table in which I insert the rows I need from table A and cast the column from varchar to integer.

It improved the query speed by about a 1000x!

Table zone_site:

CREATE TABLE traitements.zones_sites (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL,
    origine_id varchar(255) NOT NULL,  -- can't be converted to int
    catégorie varchar(255) NOT NULL,
    horodatage timestamp NOT NULL,
    geom geometry(MultiPolygon,2154),
    précision_contour varchar(100),
    statut varchar(100),
    détails_jsonb text,
    CONSTRAINT enforce_dims_geomloc CHECK (ST_NDims(geom) = 2)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_géométrie ON traitements.zones_sites USING GIST (geom);
CREATE INDEX zones_sites_précision_contour ON traitements.zones_sites (précision_contour);
CREATE INDEX zones_sites_idx_catégorie ON traitements.zones_sites (catégorie);
CREATE INDEX zones_sites_idx_origine ON traitements.zones_sites (origine);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut);
ALTER TABLE  traitements.zones_sites
    ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site)
    REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

Related question on dba.SE:

Join on different types

Unfortunately, the column traitements.zones_sites.origine_id cannot be of type integer because it holds identifier from several origins, some of them not being integers.

Original query:

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM traitements.zones_sites AS emprises
JOIN parcelles ON parcelles.idpk::varchar = emprises.origine_id
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

Modified code:

CREATE TEMPORARY TABLE temp_emprises (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL,
    idpk_parcelle integer NOT NULL
);

INSERT INTO temp_emprises (pkid, pkid_site, origine, idpk_parcelle)
SELECT pkid, pkid_site, origine, origine_id::integer
FROM traitements.zones_sites AS emprises
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

CREATE INDEX ON temp_emprises(idpk_parcelle);

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM temp_emprises AS emprises
JOIN parcelles ON parcelles.idpk = emprises.idpk_parcelle;

Is there a better solution?

Proper solution

Your comment (which I added to your question) discloses the root of the problem:

  Unfortunately, the column traitements.zones_sites.origine_id cannot be
  of type integer because it holds identifier from several origins, some
  of them not being integers.

A roughly normalized DB design would avoid to mix data of different kind in the same column. Then your true integer values could be in a column of type integer and the rest in (a) text column(s). And there wouldn't be a problem to begin with.

While stuck with your current design

Since, as you asserted, the column origine_id can be cast to integer for the selection of rows in your query, I suggest this much faster alternative: create a partial expression index:

CREATE INDEX zones_sites_idx_origine_part_int ON traitements.zones_sites ((origine_id::int))
WHERE catégorie = 'emprise_site'
AND   précision_contour = 'contour_inconnu'
AND   origine = 'xxxxxxxxx';

If the cast in your INSERT into the temp table is guaranteed to work, then so is this index.

Slightly modified query:

SELECT z.pkid, z.pkid_site, z.origine, p.idpar
FROM   traitements.zones_sites z
JOIN   parcelles               p ON p.idpk = z.origine_id::int
WHERE  z.catégorie = 'emprise_site'
AND    z.précision_contour = 'contour_inconnu'
AND    z.origine = 'xxxxxxxxx';

Since the partial index also supports your query perfectly, this should make your current solutions look like slow motion in comparison. And it's simpler and more reliable, too.

The only other index you need is one on parcelles(idpk).
Since the only other column you need from parcelles is idpar, if that table is big and write-load is low so you can get index-only scans out of it, an (additional) multicolumn index on parcelles(idpk, idpar) would make it even faster.

And faster, yet, in the upcoming Postgres 11 with true covering indexes with an INCLUDE column:

Related answer with code example:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?


Need help making a sane database schema I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each type of content.

I would like to introduce a moderation system into my app. To do this, I will allow users to report/flag a piece of content. In the database, each report should link back to the original piece of content, so that I can retrieve information about the reported content as needed. However, this requires yet 4 more tables: message report, post report, thread report, and reply report. Each of these tables will be exactly the same except for a foreign key column to reference the correct piece of content (a message, post, thread, or reply).

Using this system, reports will be saved in a many-to-one fashion, meaning that 1 single piece of content can have multiple reports associated with it. To further complicate things, I also need to be able to collect data on a single piece of content. For instance, say thread with ID 23 got many reports, but a moderator comes along and marks the content as safe. I need to be able to somehow mark all the reports for thread ID 23 as 'safe'. This seems difficult to wrap my head around, and perhaps I need another table to track this kind of information.

Does anyone have any recommendations for how to improve/design a sane database schema for this scenario? I feel as though I am doing something wrong and it is making this far too complicated.

Most entities need a thread_id.

If you want "in reply to", you need a link into the same message/comment/post table as parent_id; use NULL or 0 to indicate start of chain.

Since I don't know your distinction between message and comment and post, I can't judge whether to put them in 1, or 2, or 3 tables; it depends on whether they have enough differences.  But Threads should be a separate table that discusses common info about the set of messages/comments/posts.

safe (and questioned), as you described it, belong in Threads.  But you might want a flag(s) on the other table(s) for censoring specific items, too.

Code that displays a thread (or one item in the thread) would need to check safe and the user_id to decide whether this user can see it.  (Presumably, user_id='admin' can see everything.)


Are InnoDB files binary compatible between Windows and Linux? I have seen many posts about recovering InnoDB files.

The versions I have are:

Windows: 5.6.17
Linux 5.0.96 (I cannot upgrade this at this time)

If I create a new InnoDB file on the Windows machine, can it be copied to the Linux machine? Are they binary compatible?

It's all in the documentation here. It should be OK but see my final point.

Two important points to note:

Copying Data Files (Cold Backup Method)

You can move an InnoDB database simply by copying all the relevant files listed under "Cold Backups" in Section 14.15, “InnoDB Backup and Recovery”.
Like MyISAM data files, InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format.

(Should be OK unless you are running Linux on Sparc or ARM! :-) )

and

Using Lowercase Names for Cross-Platform Moving or Copying

On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating any databases or tables:

[mysqld]
lower_case_table_names=1

Finally, I think tat this is also of interest, particularly for you.

A lot of water has passed under the bridge between 5.0 and 5.6.

If at all possible I strongly recommend that you do a mysqldump and copy over your data that way.

Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.

One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.


Label data according to percentile I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within 10%-20%, 3 if lies within 20%-60%, 4 if lies within 60-100%. How can I do this?

Some sample data is included at sqlfiddle

select customer_id,sum,case
when pct_row<=0.10 then 1
when pct_row>0.10 and pct_row<=0.20 then 2
when pct_row>0.20 and pct_row<=0.60 then 3
when pct_row>0.60 then 4
end as customer_label from (
select customer_id,sum,(@curRow := @curRow+1)/c as pct_row
from temp t
jOIN (SELECT @curRow := 0) r
JOIN (SELECT @curRow2 := 0) r2
join (select count(*) c from temp) s
order by sum desc) p;

sqlfiddle :- here

I don't know whether this is efficient method or not but work fine for small data set.


Finding reason for transaction log 'tempdb' full due to 'ACTIVE_TRANSACTION' Our application stopped working suddenly due to The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.. Now, it start working again. But how can I know the exact reason for this error came?

You can use this query to get a list of the actively running queries throughout the server including their process ID, how long they've been running for, which database they're running in, who ran them, wait stats info, blocking query IDs, etc. I recommend putting it inside of a stored procedure so that you can easily call it as needed (even with it's default parameters) and you can routinely schedule it with a SQL Agent job to log the results to a table.
DECLARE @Database VARCHAR(100) = NULL
DECLARE @EntityName VARCHAR(100) = NULL
DECLARE @ExcludeMyQueries BIT = 0
DECLARE @RunningThreadsOnly BIT = 0
DECLARE @LocksOnly BIT = 0
DECLARE @MinDuration INT = 0
DECLARE @OutputMode INT = 0 -- TODO: Setup formally documented modes, and clean up usage below, can even have different kinds of modes like Verbose, vs EmergenciesOnly, etc

DECLARE @ExecutionDate DATETIME = GETDATE();

SELECT
    SP.HostName,
    SP.SPID,
    ER.request_id AS RequestId,
    ER.percent_complete AS PercentComplete,
    DATEDIFF(s, start_time, @ExecutionDate) AS Duration,
    CAST(((DATEDIFF(s, start_time, @ExecutionDate)) / 3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 3600) / 60 AS VARCHAR) + 'min, '
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 60) AS VARCHAR) + ' sec' AS RunningTime,
    CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), '
        + CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + 'min, '
        + CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS EstimatedTimeRemaining,
    DATEADD(SECOND, estimated_completion_time/1000, @ExecutionDate) AS EstimatedCompletionDate,
    ER.Command,
    ER.blocking_session_id AS BlockingSessionId,
    LastWaitType,
    SP.[DBID],
    DB_NAME(SP.[DBID]) AS DbName,
    --[TEXT] AS EntityText,
    CPU,
    ER.plan_handle AS PlanHandle,
    ER.query_plan_hash AS QueryPlanHash,
    LOGIN_TIME AS LoginTime,
    LOGINAME AS LoginName,
    SP.[Status],
    [PROGRAM_NAME] AS ProgramName,
    NT_DOMAIN AS NT_Domain,
    NT_USERNAME AS NT_Username,
    @@SERVERNAME AS ServerName,
    @ExecutionDate AS ExecutionDate
INTO #ExecutingQueries
FROM sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
    ON sp.spid = ER.session_id
WHERE --TEXT NOT LIKE N'%spGetRunningQueries%'
    --AND
    DB_NAME(SP.dbid) NOT IN ('msdb','master','Distribution')
    AND
    (
        @Database IS NULL
        OR (@Database IS NOT NULL AND @Database = DB_NAME(SP.[DBID]))
    )
    AND
    (
        @ExcludeMyQueries = 0
        OR (@ExcludeMyQueries = 1 AND hostname <> HOST_NAME())
    )
    AND
    (
        @RunningThreadsOnly = 0
        OR (@RunningThreadsOnly = 1 AND SP.[Status] = 'RUNNABLE')
    )
    AND
    (
        @LocksOnly = 0
        OR (@LocksOnly = 1 AND ER.blocking_session_id <> 0) -- TODO: Show the source running query that IS the blocking session ID (will need to join / union this in somehow?)
    )
    AND DATEDIFF(s, start_time, @ExecutionDate) >= @MinDuration

-- TODO: Clean this up and DON'T USE SELECT *
IF (@OutputMode = 0) -- Everything mode
BEGIN
    SELECT *
    FROM #ExecutingQueries
END
ELSE IF (@OutputMode = 1) -- Lightweight mode
BEGIN
    SELECT
        HostName,
        SPID,
        RequestId,
        RunningTime,
        BlockingSessionId,
        LastWaitType,
        DbName,
        LEFT(EntityText, 100) AS EntityText,
        PlanHandle,
        [Status]
    FROM #ExecutingQueries
END

Using this, you can look at the longest running queries (the Duration or RunningTime columns) and also see why they're running for so long by looking at the LastWaitType and/or BlockingSessionId columns.
This is a work-in-progress that I've created off of some queries I've found in the past (don't recall exactly where, but if someone recognizes it and knows the source, I'd love to cite it), that I regularly use to debug issues on SQL Server.


LEFT JOIN DEFAULT NULL VALUE My query can be simplified as this:

SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id

Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes 10 columns depend on filtering.

What I want is something like this:

SELECT a.id, a.name, IFNULL(b.*,0) FROM a LEFT JOIN b ON a.id=b.id

I already looked here and here and the answer is always repeating per column. But I can't apply ifnull per column because I don't know what the columns are. Is there any other approach for replacing null values from left join?

All columns are of the integer type.

If I understand correctly, you could have any number of columns from table B? If so, then you will need to dynamically create the SQL statement (by querying the information_schema table) and just wrap each column from B in your ifnull(field, ifnullvalue).


Create a log file for check which time import has been done and to split the import files How to create a log table for when does the last import has been done
and also for
Query for split the records in to 1000 while importing. to avoid the bulk insert
For example :
If the data contains 10000 records means i want to import 1000,.... records one by one
and finally 10000 should be updated after the import fully completed

Create a log table for your use such as:

CREATE TABLE dbo.ImportLogging
(ImportID INT IDENTITY,
 ImportFileName NVARCHAR(200),
 ImportBeginTime DATETIME,
 ImportEndTime DATETIME);

That assumes that you want to save the file name, begin time, and end time. That is up to you, of course. I would log the ImportBeginTime at the start of the import, then add the ImportEndTime at the end.  That way you will have some tracking even if the import fails.

And, assuming that you are importing from a file, I would actually recommend using BULK INSERT because you can set the batch size and let BULK INSERT handle the issue for you. Much better than writing it yourself.  E.g.

BULK INSERT dbname.schemaname.tablename
FROM 'G:\Import\Importfile.txt'
WITH (BATCHSIZE = 1000);

This will cause the BULK INSERT to process 1000 rows per transaction, commit the transaction, then start the next transaction until the file is processed.  Of course, you can change the 1000 to any number you choose.

If you are importing data from another database (either on the same server or across a linked server) then you may need to write some looping code to insert 1000 rows (or 1 row, if that is what you want) at a time.


How can I calculate the loan balance based on following data set There are two loans 12345, 54321.
I want to arrive at 'New Column' which is calculated column based on Balance and Transaction_amount.
Also, need to calculate till maturity date.

CREATE TABLE #Test (
    Account_Number int,
    Maturity_Date Date,
    Interest_Charged_Date Date,
    Transaction_Amount float,
    Balance Float
);

INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-05-12',0,100);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-06-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-07-12',-28.83,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-08-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-09-12',28.87,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-06-28',0,250);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-07-28',-17.25,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-08-28',17.18,0);

You can use SUM() function  with OVER clause (see here for more details)

Key point here is the window frame:
ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW
This is saying to include into calculation , the rows from start (UNBOUNDED PRECEDING) till the current row.

  need to calculate till maturity date
  for this you can add a Case expression

;with cteSource AS
(
     SELECT 12345 AS Account_Number ,'12/09/2016' as Maturity_Date, '12/05/2016' as Interest_Charged_Date,0 as  Transaction_Amount , 100 As Balance UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/06/2016' ,28.77  , 0 UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/07/2016' ,-28.83  , 0  UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/08/2016' ,28.77  , 0  UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/09/2016' ,28.77  , 0 UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/06/2016' ,0  , 250  UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/07/2016' ,-17.25  , 0  UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/08/2016' ,17.18  , 0
)

SELECT
    *
    ,SUM(case when Interest_Charged_Date < = Maturity_Date then Balance + Transaction_Amount else 0 end) OVER (PARTITION BY Account_Number
                       ORDER BY Interest_Charged_Date ASC
                       ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS NEW_Column
    ,SUM(Balance + Transaction_Amount) OVER (PARTITION BY Account_Number
                           ORDER BY Interest_Charged_Date ASC
                           ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS NEW_Column2
FROM
    cteSource;

output :

 Account_Number Maturity_Date Interest_Charged_Date Transaction_Amount Balance     NEW_Column   NEW_Column2
12345          12/09/2016    12/05/2016            0.00               100         100.00       100.00
12345          12/09/2016    12/06/2016            28.77              0           128.77       128.77
12345          12/09/2016    12/07/2016            -28.83             0           99.94        99.94
12345          12/09/2016    12/08/2016            28.77              0           128.71       128.71
12345          12/09/2016    12/09/2016            28.77              0           157.48       157.48
54321          28/08/2016    28/06/2016            0.00               250         250.00       250.00
54321          28/08/2016    28/07/2016            -17.25             0           232.75       232.75
54321          28/08/2016    28/08/2016            17.18              0           249.93       249.93

dbfiddle here


How to compact the current MS Access database from VBA function I want to be able to run the 'compact and repair' process from within a VBA module in the database.

I have a batch process that I run occasionally, it drops a few old tables, re-imports them from other databases, renames a couple of fields, does a few updates and makes a few other minor changes. The process isn't rocket science, but there are several steps so it really does needs to be automated.

The problem is that a couple of the steps (the UPDATEs) temporarily increase the size of the database which can cause problems with subsequent imports.

If I do the process manually (including compacting) then everything works fine and I end up with a 800MByte database. If I use my automated VBA script (without compacting) then it crashes halfway through when the database busts the 2Gbyte limit.

I've found several threads on this subject, but they're all three of four years old (or more) and the methods they describe don't seem to work anymore.

Are they're any solutions that work with Office 365 (version 1720)?

The 'auto compact' causes the database to compact on closing, it does NOT allow the compaction of the database to be added between steps.

I've tried this:

Public Sub CompactDb2()
  Dim control As Office.CommandBarControl
  Set control = CommandBars.FindControl(Id:=2071)
 control.accDoDefaultAction
End Sub

And this:

Public Sub CompactDb1()
    CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _
    Controls("Compact and repair database...").accDoDefaultAction
End Sub

And this....

Public Sub CompactDb3()
    Application.SetOption "Auto compact", True
End Sub

Amongst other

This is simply not possible. Compacting and repairing a database requires the database to be closed. As such, you can't compact and repair a database between steps in a sub or procedure, since the database is open when running the procedure.

You might notice the Compact and repair button on the ribbon requires an exclusive lock, closes the database, then compacts and repairs, and then reopens it.

My advice: either run the process from an external database, a VBScript file or PowerShell. Run the first part of your batch, close the file, compact and repair, reopen, run second part

Sample code

Dim fileLocation As String
DBEngine.CompactDatabase fileLocation, fileLocation & "_1"
Kill fileLocation
Name fileLocation & "_1" As fileLocation

You might also notice the Access compact and repair button doing something similar. If you run compact & repair, it moves the data to a database called Database.accdb in your current folder (name might vary based on existing names/database type), then deletes your current database, and then renames the new one.

Well, but nothing is impossible, right?

Well, some things are, but this isn't one of them, if you're willing to do some weird trickery. As I just said, the main problem is that the current database has to be closed. So, the workaround does the following:

Programmatically create a VBScript file
Add code to that file so we can compact & repair our database without having it open
Open and run that file asynchronously
Close our database before the compact & repair happens
Compact and repair the database (creating a copy), deleting the old one, renaming the copy
Reopen our database, continue the batch
Delete the newly created file

Luckily, I had some time to spare, so I came up with the following solution:

Public Sub CompactRepairViaExternalScript()
    Dim vbscrPath As String
    vbscrPath = CurrentProject.Path & "\CRHelper.vbs"
    If Dir(CurrentProject.Path & "\CRHelper.vbs") <> "" Then
        Kill CurrentProject.Path & "\CRHelper.vbs"
    End If
    Dim vbStr As String
    vbStr = "dbName = """ & CurrentProject.FullName & """" & vbCrLf & _
    "resumeFunction = ""ResumeBatch""" & vbCrLf & _
    "Set app = CreateObject(""Access.Application"")" & vbCrLf & _
    "Set dbe = app.DBEngine" & vbCrLf & _
    "Set objFSO = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
    "On Error Resume Next" & vbCrLf & _
    "Do" & vbCrLf & _
    "If Err.Number <> 0 Then Err.Clear" & vbCrLf & _
    "WScript.Sleep 500" & vbCrLf & _
    "dbe.CompactDatabase dbName, dbName & ""_1""" & vbCrLf & _
    "errCount = errCount + 1" & vbCrLf & _
    "Loop While err.Number <> 0 And errCount < 100" & vbCrLf & _
    "If errCount < 100 Then" & vbCrLf & _
    "objFSO.DeleteFile dbName" & vbCrLf & _
    "objFSO.MoveFile dbName & ""_1"", dbName" & vbCrLf & _
    "app.OpenCurrentDatabase dbName" & vbCrLf & _
    "app.UserControl = True" & vbCrLf & _
    "app.Run resumeFunction" & vbCrLf & _
    "End If" & vbCrLf & _
    "objFSO.DeleteFile Wscript.ScriptFullName" & vbCrLf
    Dim fileHandle As Long
    fileHandle = FreeFile
    Open vbscrPath For Output As #fileHandle
    Print #fileHandle, vbStr
    Close #fileHandle
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    wsh.Run """" & vbscrPath & """"
    Set wsh = Nothing
    Application.Quit
End Sub

This does all the steps outlined above, and resumes the batch by calling the ResumeBatch function on the database that called this function (without any parameters). Note that things like click-to-run protection and antivirus/policy not liking vbscript files can ruin this approach.


RDS: permission denied for relation spatial_ref_sys when copying I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,

ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS

This error is a result of you not having access on that view. What you need to run to fix the permissions is

ALTER TABLE public.spatial_ref_sys OWNER TO rds_superuser;
GRANT SELECT, INSERT ON TABLE public.spatial_ref_sys TO public;

Found here on the AWS forums


How to Create Table Name That Contains Last Month in Suffix I want to create a table with the last month's name as part of the table name.
If the current month is February then I would want the table name to look like this: table_name_jan18

The statement I have so far looks like this:

create table abc_'||'trunc(SYSDATE,'mm')-1';

declare
  l_cmd clob;
begin
  l_cmd := 'create table abc_'|| to_char(add_months(SYSDATE,-1), 'MONDD') || ' (c1 number)';
  execute immediate l_cmd;
end;
/

SQL> select table_name from user_tables where table_name like 'ABC%';

TABLE_NAME
----------
ABC_JAN13

SQL> desc abc_jan13
 Name  Null?    Type
 ----- -------- -------
 C1             NUMBER


BUILTIN\Administrators accidently removed Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. Also I do not have the SA account password to log in as that user to change/ add any permission.

Can anyone help me add the BUILTIN\Administrators login back into sysadmin or find a way to alter the database using my windows account?

Start the SQL Server service in single-user mode (by using the -m startup parameter).  When you do this, all members of the local administrators group will have sysadmin rights.  Provided you are a member of that group, you should be able to do whatever sysadmin actions you need.

Take a look at Database Engine Service Startup Options for documentation.

Edit:  To start SQL Server in Single-User Mode, please see this documentation.


Postgres: Merge database backup with development database My database is quite large 50GB+ what I've always had to do when I want to sync my development database with production is download a backup of production, drop my development database then restore the production database on my development machine.

Is there any way of doing a (Sync/merge) where only new data is added?

What I do is keep two copies of production on my test machine.  At 50GB, that should be pretty doable.  When I want a fresh copy of production, all I have to do is:

pg_ctl stop -D $WHERE/dev -mi
rm $WHERE/dev/* -r
mv $WHERE/pre_stage/* $WHERE/dev
cp $WHERE/recovery.sample $WHERE/dev/recovery.conf
pg_ctl start -D $WHERE/dev
tar xf /mnt/something/latest/base.tgz -C $WHERE/pre_stage &

It takes anywhere from a few seconds to a few minutes, depending on how much WAL log has been generated since the last base backup was taken.  The last step of replenishing the pre_stage takes longer, but it doesn't matter since I don't have to sit around and wait for it to finish.

Of course this requires you to be using log archiving on the production machine.  I would be doing that anyway, though.


ORDER BY text doesn't use my index?! currently this query doesn't use a index even after I created one:

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.307..11.311 rows=20 loops=1)
   ->  Sort  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.305..11.306 rows=20 loops=1)
         Sort Key: c.name
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=0.28..148.85 rows=15 width=1156) (actual time=0.022..6.403 rows=2977 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..96.24 rows=15 width=674) (actual time=0.009..1.184 rows=2977 loops=1)
                     Filter: (rating = 3)
                     Rows Removed by Filter: 2
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.001..0.001 rows=1 loops=2977)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.246 ms
 Execution time: 11.353 ms
(13 rows)

When turning seqscan off via set enable_seqscan = false; it will run correctly (ordered by name and is way faster):

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.028..0.103 rows=20 loops=1)
   ->  Nested Loop  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.027..0.101 rows=20 loops=1)
         ->  Index Scan using crm_customer_name on crm_customer c  (cost=0.28..360.41 rows=15 width=674) (actual time=0.018..0.040 rows=20 loops=1)
               Filter: (rating = 3)
               Rows Removed by Filter: 2
         ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.002..0.002 rows=1 loops=20)
               Index Cond: (id = c.address_id)
               Heap Fetches: 0
 Planning time: 0.190 ms
 Execution time: 0.133 ms
(10 rows)

My actual index is created with:
CREATE INDEX crm_customer_name_search_index ON crm_customer (name text_pattern_ops ASC NULLS FIRST );

currently I had a bigger index and thought that this way the problem, but however it's not. (indexing rating, too doesn't help)

You can find the answer in the EXPLAIN ANALYZE outputs you've posted.

In the first one, the estimated cost of the sequential scan is 96.24, while the index scan has a cost of 360.41 in the second one.  This is why the planner decided not to use the index.

However, we see that the execution time is much better in the second case.  The main reason might be that the row estimate of the sequential scan is quite bad - 15 rows are expected instead of ~3k.  Do an ANALYZE crm_customer; and see if it changes - it is already possible that the index will be chosen.


Rank using one column. If it is the same value in 2 rows rank using another column i have a table as table1

 id| hits | time
-------+--------+------
 1 | 9    |    40.89
 2 | 5    |    33.21
 3 | 5    |    78.31
 4 | 1    |    71.93
 5 | 4    |    23.10
 6 | 4    |    99.99
 7 | 8    |    12.32
 8 | 7    |    32.64

I want to rank the values using hits and if 2 rows has the same number of hits i want to rank them using time
 That is one with lowest time has a higher rank
 This is the expected result

  id| hits | time     | rank
-------+--------+--------------
 1 | 9    |    40.89  |  1
 2 | 5    |    33.21  |  4
 3 | 5    |    78.31  |  5
 4 | 1    |    71.93  |  8
 5 | 4    |    23.10  |  6
 6 | 4    |    99.99  |  7
 7 | 8    |    12.32  |  2
 8 | 7    |    32.64  |  3

Any help is appreciated

SELECT *, RANK() OVER (ORDER BY hits DESC, `time` ASC) AS `rank`
FROM table1
ORDER BY id


How to change the datatype of a column from integer to money? I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:

  cannot cast type MyColumn to money

I have tried these two statements, but just haven't found any real example on how to really do it.

ALTER TABLE products
    ALTER COLUMN price TYPE money

and:

ALTER TABLE products
    ALTER COLUMN price TYPE money USING to_char(price, '999.99')

How can you change the datatype of a PostgreSQL column from integer to money?

Just try the following command.

ALTER TABLE products ALTER COLUMN price TYPE money using price::text::money

Non-quoted numeric values can be converted to money by casting the numeric value to text and then money