1

I have a website with admin panel, which people can upload files and data. My website is on 2 servers and use nlb, and also two servers are synced together with dfs, also I have 2 SQL Server on both servers and they are synced too.

The problem is syncing files on both servers take sometimes, Now just imagine I have a table with these fields:

Name, price, fileName

File address is point to somewhere of physical disk which is synced in 2 servers.

Now imagine the website run this query:

Select * from myTable

How can I tell SQL that just show that records which the fileName field exists in physical disk?

Note: I want it to do it with SQL, not in my application.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sepehr Estaki
  • 331
  • 5
  • 19
  • See this question http://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server – Michał Komorowski Feb 10 '16 at 07:09
  • 1
    "I want it to do it with sql not in my application" - yes, forget about layering and separation of concerns. Your application *naturally* cares about various different types of resources, whereas your databases should only care about the data. The *natural* and logical place to perform such checks *is* in the application. – Damien_The_Unbeliever Feb 10 '16 at 07:11
  • @Damien_The_Unbeliever I have to do it with sql because i want to paging records – Sepehr Estaki Feb 10 '16 at 07:20
  • So, assuming a page size of 10 and you want page 2. Files for rows 11-15 are currently absent so you return rows 16-25. Then, when you want page 3, it turns out that those files have now appeared, and page 3 will contain rows 21-30 (repeating 5 of the rows). Whatever you do is going to be *messy* here and I'd still suggest it's better solved in the application. – Damien_The_Unbeliever Feb 10 '16 at 07:42

1 Answers1

2

There are two options:

  1. You already mentioned it. It is a good idea to add a column (FileExists bit (Y/N)) which tells you directly the status of physical copy.

  2. Second option is tedious as you need to create custom logic which identifies the physical copy at runtime as per the first commented link given by @MichałKomorowski.

Ideally a database is used to store data and communicate with the application. Outer world interfaces within a database will be tedious work as well as decrease performance.

For example, if you implement a function to check physical file and in select query as above you want, you used that function, so extra headache to checking the status will definitely increase the time and performance decrease. Just think again.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58